[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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
0
dapperry
Asked:
dapperry
  • 2
1 Solution
 
UnifexCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now