cfquery replacing single quotes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rschoen - 25 Apr 2007 23:23 GMT

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')

Reply to this Message

Dan Bracuk - 26 Apr 2007 00:05 GMT

cfqueryparam list ="yes" works.

Another way is to quote all your quotes with the replace function.

Reply to this Message

The ScareCrow - 26 Apr 2007 00:28 GMT

You could also use

PreserveSingleQuotes(string)

Ken

Reply to this Message

Ian Skinner - 26 Apr 2007 15:05 GMT

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.

Reply to this Message

insuractive - 26 Apr 2007 16:08 GMT

though, if it works in the context of your code, Dan Bracuk's first suggestion
is probably the best:


<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.

Reply to this Message

swangs - 26 Jul 2007 07:08 GMT

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?

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / July 2007

cfquery replacing single quotes