Skip to main content
J
jzimmerman2011
Participating Frequently
July 16, 2010
Question

cf replaces ' with '' in query

  • July 16, 2010
  • 3 replies
  • 673 views

i am having a really odd issue trying to execute a database query with a cf script.

basically, i am looping through a csv file, creating an INSERT query as a string, and then running a cfquery using this string variable for the query text.

but, what is weird is that i am doing what i normally do to insert text info, but cf is messing it up (it seems like).

for instance, i start by declaring the first part of the statement:

<CFSET qryVar = "INSERT INTO table (field1, field2, etc...) VALUES ">

then as i loop, i do:

<CFSET qryVar = qryVar & "( '#item1#', '#item2', etc...)">

now, what is happening is that when i use qryVar in my CFQUERY:

<CFQUERY name="blah" datasource="blah">


#qryVar#

</CFQUERY>

it is changing a single quote into two single quotes.

what i saved in the string:

( 'string', 'string', etc...)

what is being used:

( ''string'', ''string'', etc.)

note: '' is two single ', not a ".  so, i am getting an error because it is reading the '' as a null/empty text value, and is then trying to use the text as objects as the syntax would see it.

why would cf be changing my ' to ''?

    This topic has been closed for replies.

    3 replies

    D
    Dan_Bracuk
    Inspiring
    July 16, 2010

    I'm guessing you put quotes into your item variables.  Then you put another set into your qryvar variable.

    J
    JR__Bob__Dobbs
    Inspiring
    July 16, 2010

    What database are you using?  If your db supports a bulk import feature this might be a better (faster) option for importing data from a CSV file.

    ilssac
    ilssac
    Inspiring
    July 16, 2010

    That is not wierd at all!  Because <cfquery...> is designed to escape single quotes by doubling them in any string variable used in them.

    The designers assumed more people would be passing strings like "Ms O'Hare said she'd like to share", which needs to be escaped, rather then SQL syntax like you are doing.

    But to allow one to do just what you want to do, they provided the preserveSingleQuotes() function.

    Just be VERY aware of the sql injection risk you maybe opening your application up to.

    J
    jzimmerman2011Author
    Participating Frequently
    July 16, 2010

    your answer makes sense, until i look back at other queries that i have made.

    recently, i did a similar thing but with xml data.  example:

    INSERT INTO table (fields) VALUES ( '#xmlDoc["element"].XmlText#', etc.)

    same idea, using the '# and #' to get the text data in the final sql syntax.

    using that above though, i never ran into this problem with the quotes...

    i will however, try the function you supplied, and see how everything works.

    thanks.

    ilssac
    ilssac
    Inspiring
    July 16, 2010

    Did xmlDoc["element"].xmlText contain SQL syntax with single quotes in it?

    <cfquery...> does not escape all single quotes, just single quotes inside of string variables used inside the query block.

    I.E.

    No quote escaping, this will work, but note that the quote in "doesn't" has been manually escaped.

    <cfquery...>
      UPDATE ... SET aField = 'Mary Joe doesn''t want to go'
    </cfquery>

    Quotes in the aVar string variable are escaped, this will work as expected.

    <cfset aVar = "Mary Joe doesn't want to go'>
    <cfquery...>
      UPDATE ... SET aField = '#aVar#'>
    </cfquery>

    Quotes in the aVar string variable are escaped, this will throw an error.  NOTE the attempt to manually escape the singe quote in the string as the database needs it.  Thus the singe quote in the word "doesn't" will be doubly escaped.

    <cfset aVar = "SET aField = 'Mary Joe doesn''t want to go'">
    <cfquery...>
      UPDATE ... #aVar#
    </cfquery>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices