Single Quote Troubles

I create a values list for use in a cfquery insert query.  The trouble is if there is a single quote in the data, the whole thing goes kerplunk!  Heres an example.

<cfset fullname="David Perry">
<cfset comments="I'll be back">

<cfset q="'">
<cfset values=q & fullname & q & "," & q & comments & q>

<cfquery name="test" datasource="test">
     INSERT INTO Table1(Name,Comments)
     VALUES(#preservesinglequotes(values)#)
</cfquery>

Any help would be greatly appreciated.

:) dapperry
LVL 3
dapperryAsked:
Who is Participating?
 
UnifexConnect With a Mentor Commented:
If you double up on the single quotes in the comments string it works fine.  Use this line instead:

<cfset values=q & fullname & q & "," & q & replace(comments,"'","''") & q>

The debugging info will include the doubled up single quote but the table will record the entry as a single quote.

Au.
0
 
UnifexCommented:
Hmm...  That looks bad on my screen.  While cutting and pasting will work what that replace statement says is:

replace(comments,[dq][sq][dq],[dq][sq][sq][dq])

....where [dq] is double quote (") and [sq] is single quote(').

Au.
0
 
dapperryAuthor Commented:
Thanks that worked great.  It is kinda of pain though to have to use the replace.  Oh well...

:) dapperry
0
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.

All Courses

From novice to tech pro — start learning today.