|
|
[q][i]Originally posted by: [b][b]swg_mcherry[/b][/b][/i]
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.[/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.
|
|