5 Replies Latest reply on Jul 25, 2007 11:08 PM by swangs

    cfquery replacing single quotes

    rschoen
      cfquery is doubling my single quotes. I can't seem to find any documentation on when or why it does this.

      I am trying to put a list into an "IN" clause in a query.
      In this example my list is three items. each seprated by a single quote, a comma, and another single quote. When the test is complete
      <cfset strlist = "val1','Val2','Val3">
      <cfquery name="myquery" datasource="#Application.ActiveDSN#">
      Select f1,f2
      from mytable
      Where f1 in('#strList#')
      </cfquery>

      The SQL that attemps to run is
      Select f1,f2 from mytable Where f1 in('val1'',''Val2'',''Val3')
        • 1. Re: cfquery replacing single quotes
          Dan Bracuk Level 5
          cfqueryparam list ="yes" works.

          Another way is to quote all your quotes with the replace function.
          • 2. Re: cfquery replacing single quotes
            The ScareCrow Level 1
            You could also use

            PreserveSingleQuotes(string)

            Ken
            • 3. Re: cfquery replacing single quotes
              Level 7
              cfquery is doubling my single quotes. I can't seem to find any
              documentation on when or why it does this.

              It does this to properly handle strings with single quotes|apostrophes
              in it.

              <aString = "Bob's your uncle">

              <cfquery ...>
              INSERT INTO aTable
              (aField)
              VALUES ('#aString#')
              </cfquery>

              If the single quote in the string was not escaped by doubling, this
              query would fail. As mentioned the preserveSingleQuotes() function is
              used when you want to suppress this behavior.
              • 4. Re: cfquery replacing single quotes
                insuractive Level 3
                though, if it works in the context of your code, Dan Bracuk's first suggestion is probably the best:

                <!--- notice no single quotes --->
                <cfset strList="#val1#,#val2#,#val3#">

                Select f1,f2
                from mytable
                Where f1 in(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" list="yes" value="#strList#">)

                CFQueryParam will not only qualify each item with single quotes (if list="Yes"), but it will also help CF optimize your query and it might help speed things up.
                • 5. Re: cfquery replacing single quotes
                  swangs
                  hello all
                  I've read through this discussion
                  but still have problem with the codes below:

                  <cfset sqlstring="'CUS_TYPE_1'">

                  <cfquery name="UPDATE_RCUS_TYPE_1" datasource="#application.unify01_i#">
                  UPDATE UNIFY104.UNI_RULE_SETTING
                  SET RULE_VALUE = 1
                  WHERE RULE_NAME = #PreserveSingleQuotes(sqlstring)#;
                  </cfquery>

                  they simply don't work.
                  am I doing wrong with anything?