Single Quote Escaping in ColdFusion 8

swg_mcherry

unread,

Mar 3, 2008, 4:47:24 PM3/3/08

to

Recently we've begun migrating some sites to ColdFusion 8 from earlier versions
of ColdFusion. We've found the following quirk when inserting data into an SQL
database.

<cfset value = "Two single quotes '' and one single quote ' in the same line
does not insert as expected." />
<cfquery datasource="#datasource#">
UPDATE Record
SET field = '#value#'
</cfquery>

Running the code above updated the record in the database to be "Two single
quotes ' and one single quote ' in the same line does not insert as expected."
In this case it did not escape the two single quotes together so only one sigle
quote was entered there, but where the single quote was by itself it was
properly escaped and entered into the database record. I would have expected
the code above to be entered into the database as "Two single quotes '' and one
single quote ' in the same line does not insert as expected." where all single
quotes in the string were escaped.

Has anyone else encountered this problem, and know of a way to correct it?
The exact same query works as exprected in previous versions of ColdFusion.
Manually changing all of the existing queries on the site to manually escape
the single quotes would be an incredibly time consuming process, so I'm hoping
that there is another way around the problem.

paross1

unread,

Mar 3, 2008, 5:00:21 PM3/3/08

to

Does it make any difference if you use a cfqueryparam tag?

<cfquery datasource="#datasource#">
UPDATE Record

SET field = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#value#">
</cfquery>

Phil

swg_mcherry

unread,

Mar 3, 2008, 5:29:04 PM3/3/08

to

Interestingly enough, the same problem does occur when the CFQUERYPARAM tag is
used as well. I even double checked how the data was entered on two separate
servers to ensure that there was not some fluke with the installation on the
server I had been using for development.

Even if changing the query to use the CFQUERYPARAM had corrected the problem I
would like to avoid having to go through all of the existing queries that are
inserting data into the database and having to edit them if possible. The
number of man hours of work that would be involved in editing all of the
existing queries makes that approach to correcting the problem impractical, and
at the moment would only be considered as a last resort.

JRock

unread,

Mar 4, 2008, 9:54:29 PM3/4/08

to

Have you tried #PreserveSingleQuotes(mystring)# ?
What about writing a stored procedure?

swg_mcherry

unread,

Mar 5, 2008, 4:41:44 PM3/5/08

to

Using PreserveSingleQuotes throws an error because when it's used no quotes are
escaped which causes the data being inserted to be invalidly formatted.

While using stored procedures is definitely a possibility I'd rather not have
to change the thousands of existing queries into stored procedures to correct
this problem. That would be a lot of work to do to correct something that had
been working correctly in previous versions of ColdFusion.

After a bit more testing I was able to get the cfqueryparam tag to insert the
data correctly. While I don't like the idea of having to update all of the
existing queries, it doesn't look like I'm going to have much of a choice in
the matter.

neo#1

unread,

Mar 5, 2008, 5:58:06 PM3/5/08

to

I agree with JRock, try writing a stored procedure.

NeoTech
http://www.techprodigy.net

neo#1

unread,

Apr 6, 2008, 12:54:29 AM4/6/08

to

How did it turn out?Problem resolved?

Thanks!

<a href ="www.techprodigy.net">Coldfusion </a>

swg_mcherry

unread,

Apr 7, 2008, 12:23:27 PM4/7/08

to

The problem itself still exists. We're currently in the process of rewriting
all of the existing queries since there apparently is nothing else that can be
done that will make these queries that worked in previous versions of
ColdFusion work correctly in ColdFusion 8 without rewriting them. While this
problem doesn't apply to every query on the site we still need to evaluate each
query to determine if any changes need to be made. This has made the process
up upgrading to ColdFusion 8 quite time consuming.

Azadi

unread,

Apr 7, 2008, 1:05:26 PM4/7/08

to

which dbms are you using? the problem must be with db connector drivers,
not cf...

check which db connector driver is used and look up built-in escape
characters in that driver version and how to disable/change those (i.e.
maybe possible though a connection string argument for dsn or jdbc url
param)

alternatively, see if you can use drivers that do not use built-in
escape characters, if such exist...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

swg_mcherry

unread,

Apr 8, 2008, 1:42:52 PM4/8/08

to

We're using a connection defined in ColdFusion administrator that's using the
Microsoft SQL Server driver. I don't believe it's possible to change how
Microsoft SQL Server handles escaping single quotes in field value used in
Update and Select statements.

I'll create a system ODBC connection and try the ODBC Socket driver when I get
some time and see if that has an effect on the problem. Overall I don't like
the general idea of creating a system ODBC data source for each database since
it adds an extra layer of overhead to the queries, but if this works then it
will save a lot of effort in updating existing queries.

vanessalewis

unread,

Jul 23, 2008, 7:53:04 AM7/23/08

to

Does this work with Vista ?

Anthony O'Hare

http://www.advanceddatarecovery.co.uk http://www.inkcartridgesuk.co.uk http://www.datarecovery24.co.uk

Dan Bracuk

unread,

Jul 23, 2008, 11:17:37 AM7/23/08

to

[q][i]Originally posted by: [b][b]swg_mcherry[/b][/b][/i]

that there is another way around the problem.[/q]
With that specific example, let's say value = O'Hara. This is what I have
noticed over time.

set field = '#value#' would crash because your database would see 3 single
quotes.

set field = '#replace(value, "'", "''", "all")#' would work, but your database
record would be O'Hara, not O''Hara as you said you would expect.