Single Quote Troubles

Posted on 2000-04-17
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)

Any help would be greatly appreciated.

:) dapperry
Question by:dapperry
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

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.


Expert Comment

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


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


Author Comment

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

:) dapperry

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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