ColdFusion Query-of-Queries Wildcard LIKE condition not working with single quotes?

Question

I have a query-of-queries that performs a LIKE condition on a variable string:

When the variable contains a single word that includes a single quote, some results are returned, but not all:

<cfset _myVar = "Women's" />

<cfquery name="_qData" dbtype="Query">
    SELECT 
        ID
    FROM   MyQoQ
    WHERE  NAME LIKE '%#_myvar#%'
        OR DESCRIPTION LIKE '%#_myvar#%'
</cfquery>

When the variable contains more than one word, and one of those words includes a single quote, no records are returned:

<cfset _myVar = "Women's Initiative" />

<cfquery name="_qData" dbtype="Query">
    SELECT 
        ID
    FROM   MyQoQ
    WHERE  NAME LIKE '%#_myvar#%'
        OR DESCRIPTION LIKE '%#_myvar#%'
</cfquery>

I've tried PreserveSingleQuotes() as well as wrapping the varaibles with CFQUERYPARAM, but, to no avail - I get the same results.

Is there a way to make this work?

Adding in a repro case

<cfset myQuery = queryNew('hello')>
<cfset queryAddRow(myQuery,5)>
<cfset querySetCell(myQuery,"hello","what up",1)>
<cfset querySetCell(myQuery,"hello","what's up",2)>
<cfset querySetCell(myQuery,"hello","what's up friends",3)>
<cfset querySetCell(myQuery,"hello","what u",4)>
<cfset querySetCell(myQuery,"hello","what",5)>
<cfdump var="#myQuery#">

<cfquery name="res" dbtype="query">
SELECT *
FROM myQuery
WHERE hello LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%$what's up%">
</cfquery>

<cfdump var="#res#">

Railo 4.1.1.009 - returns both results (rows 2 and 3) ColdFusion 10,0,13,287689 - returns no results If I change my SQL to

WHERE hello LIKE '%what''s up%'

I still get no results

cfqueryparam should have escaped the apostrophe. Please edit your question to show what you tried, what you expected, and the actual result. Forget PreserveSingleQuotes, it's designed for other situations. — Dan Bracuk, Mar 18 '14 at 01:57
What is the purpose of the dollar sign in the value attribute of cfqueryparam? — Dan Bracuk, Mar 18 '14 at 02:29

score 1 · Answer 1 · answered Mar 18 '14 at 06:23

aarh!! a classic case of royal pain in the rear.

To solve this, you have to add an extra ' to every ' in your search term, there by escaping it.

<cfset myQuery = queryNew('hello')>
<cfset queryAddRow(myQuery,5)>
<cfset querySetCell(myQuery,"hello","what up",1)>
<cfset querySetCell(myQuery,"hello","what's up",2)>
<cfset querySetCell(myQuery,"hello","what's up friends",3)>
<cfset querySetCell(myQuery,"hello","what u",4)>
<cfset querySetCell(myQuery,"hello","what",5)>
<cfdump var="#myQuery#">

<cfset  x = "what's up" />

<cfquery name="res" dbtype="query">
SELECT *
FROM myQuery
WHERE hello LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#replace(x, "'", "''", "all")#%">
</cfquery>

<cfdump var="#res#">

if you do this directly, as you mentioned,

WHERE hello LIKE '%what''s up%'

the parser is going bonkers. But, if you pass the value via a function return value, the run time assignment of values via a variable somehow makes the parser happy.

I remember using such tricks in sending multiple SQL statements delimited with ';' in cfquery. Directly writing

"DECLARE x NUMBER; SELECT 2 INTO x FROM DUAL;"

inside cfquery fails, but assigning them to a string and then sending the string as a return value for any string manipulator function (lcase, ucase, etc) worked perfectly.

Note: the problem is solved, but if my explanation and approach is diff, feel free to correct and comment.

score 1 · Answer 2 · answered Mar 18 '14 at 07:11

I'm running ColdFusion 10 u13.

Modifying your repo code this seemed to work:

<cfset myQuery = queryNew('hello')>
<cfset queryAddRow(myQuery,5)>
<cfset querySetCell(myQuery,"hello","what up",1)>
<cfset querySetCell(myQuery,"hello","what's up",2)>
<cfset querySetCell(myQuery,"hello","what's up friends",3)>
<cfset querySetCell(myQuery,"hello","what u",4)>
<cfset querySetCell(myQuery,"hello","what",5)>
<cfdump var="#myQuery#">

<cfquery name="res" dbtype="query">
SELECT *
FROM [myQuery]
WHERE [hello] LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%what''s up%">
</cfquery>

Note the double single quotes in the <cfqueryparam> tag. Like Dan I would have thought that the <cfqueryparam> tag would have taken care of this for you automatically. Perhaps this is a bug in QoQ?

ColdFusion Query-of-Queries Wildcard LIKE condition not working with single quotes?

2 Answers2