cfquery replacing single quotes

[Guest]

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

[Dan_Bracuk]

cfqueryparam list ="yes" works.

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

[The_ScareCrow]

You could also use

PreserveSingleQuotes(string)

Ken

[Newsgroup_User]

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.

[insuractive]

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.

[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?