Skip navigation
Currently Being Moderated

Single Quote Escaping in ColdFusion 8

Mar 3, 2008 1:47 PM

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.
 
Replies
  • Currently Being Moderated
    Mar 3, 2008 2:00 PM   in reply to swg_mcherry
    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
     
    |
    Mark as:
  • Currently Being Moderated
    Mar 4, 2008 6:54 PM   in reply to swg_mcherry
    Have you tried #PreserveSingleQuotes(mystring)# ?
    What about writing a stored procedure?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 5, 2008 2:58 PM   in reply to JRock
    I agree with JRock, try writing a stored procedure.




    NeoTech
    Web Hosting ColdFusion Domain Registration
     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2008 9:54 PM   in reply to neo#1
    How did it turn out?Problem resolved?



    Thanks!

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 7, 2008 10:06 AM   in reply to neo#1
    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/
     
    |
    Mark as:
  • Currently Being Moderated
    Jul 23, 2008 8:17 AM   in reply to swg_mcherry
    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.
     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points