Dealing with single quote marks in input boxes

How do I deal with single quotes in input boxes?  When the form is submitted, it causes errors in the SQL statement.  What do I need to do to avoid that?
LVL 2
David WilliamsonIT DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James RodgersWeb Applications DeveloperCommented:
either replace them prior to submission using js, or during the submission process using rereplace

in js

in your form validation

document.form.textbox.value.replace(/\'/g,'"');


or in the coldfusion processing

#rereplace(#form.textbox#,"'","#chr(34)#","all")#

the above will replace single quote with double quote
0
PE_CF_DEVCommented:
ColdFusion normally does this for you unless you are using a function already..so if you have

insert stuff
('#coldfusionfunction(form.textbox)#',...)

 it won't make it SQL safe you can do something like Jester suggested, I would recomend doing
#rereplace(#form.textbox#,"'","''","all")# (2 single quotes that escaped the ' in SQL like ## output # in coldfusion), or you can do this:
<cfset variablename=coldfusionfunctions(form.textbox)>
insert stuff
('#variablename#',...)

0
MauseCommented:
You can use #preservesinglequotes(yourvar)# to escape a single quote mark

But I think its better to use
<cfqueryPARAM value = "#yourvar#"  CFSQLType = "CF_SQL_CHAR">
for escaping your single quote mark and other trobleshooting characters

for more information see:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm

hope this helps
Mause
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

David WilliamsonIT DirectorAuthor Commented:
wow, thanks.  Some of the tags/functions you all have mentioned are unfamiliar to me: rereplace, coldfusionfunction, preservesinglequotes

What are your (all) opinions about the answers that the others have given?  I've read several articles that stress the use of CFQUERYPARAM, would that be the best approach since it seems to be the 'proper' way of coding INSERTs?  Will that tag escape other special characters in addition to the single quote?
0
David WilliamsonIT DirectorAuthor Commented:
One more thing, do double quotes cause problems in some situations?  What if my SQL statements use double quotes instead of single quotes around CHAR data?
0
PE_CF_DEVCommented:
preservesinglequotes(yourvar) will not work...it is used to KEEP the single quotes and to keep Coldfusion from doubling up the quotes...
Like if you have a string state_list : 'OH','FL','TX'

If you do
where state IN (#state_list#)

Coldfusion will try to make it :
where state in (''OH'',''FL'',''TX'')
 
You use preservesinglequotes()
where state IN (#preservesinglequotes(state_list)#)

ColdFusion does not try to double up the ' which will allow it to be
where state in ('OH','FL','TX')
Like it should be.
0
PE_CF_DEVCommented:
coldfusionfunction is not a function it was just my way of saying any function you use. Sorry for the confusion :)
0
David WilliamsonIT DirectorAuthor Commented:
It looks like using CFQUERYPARAM did the trick, as well as take care of ANY special characters I entered.  Thanks for the input everyone!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.