CODEWITHSUNDEEP
coldfusioncoldfusion-8sql-likecfqueryqoq
Eric Belair
Eric Belair

Reputation: 10692

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

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

Upvotes: 3

Views: 2363

Answers (2)

Scott Jibben
Scott Jibben

Reputation: 2287

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?

Upvotes: 1

Sanjeev
Sanjeev

Reputation: 1866

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.

Upvotes: 1

Related Questions