Solved

Single Quote Troubles

Posted on 2000-04-17
3
166 Views
Last Modified: 2013-12-24
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
Comment
Question by:dapperry
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
Unifex earned 100 total points
ID: 2725204
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
 
LVL 2

Expert Comment

by:Unifex
ID: 2725208
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
 
LVL 3

Author Comment

by:dapperry
ID: 2726874
Thanks that worked great.  It is kinda of pain though to have to use the replace.  Oh well...

:) dapperry
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question