Single Quote Escaping in ColdFusion 8

[swg_mcherry]

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]

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]

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]

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

[swg_mcherry]

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]

I agree with JRock, try writing a stored procedure.




NeoTech
Web Hosting ColdFusion Domain Registration

[neo_1]

How did it turn out?Problem resolved?



Thanks!

[swg_mcherry]

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.

[Newsgroup_User]

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]

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.

[Dan_Bracuk]

quote:

---

Originally posted by: swg_mcherry
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.

---

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.