Improper handling of query using IN ( ... ) matching with PreserveSingleQuotes

[jbartlett777]

<cfscript>
Apps=QueryNew("AppName","varchar");
for (CurrApp in "AT&T,Verizon,T-Mobile,Sprint,Cingular,Jasper Wireless") {
	QueryAddRow(Apps);
	QuerySetCell(Apps,"AppName",CurrApp);
}
</cfscript>

<CFSET AppList="'AT&T','T-Mobile'">
<CFQUERY name="SelApps" dbtype="Query">
	SELECT AppName
	FROM Apps
	WHERE AppName IN (#PreserveSingleQuotes(AppList)#)
</CFQUERY>

The above code does a standard IN search that you might find where it doesn't use the CFQueryParam and returned two rows.

SelApps (Datasource=, Time=0ms, Records=2) in C:\CommandBox\MyLogins\www\tests\fixinator_testcase.cfm @ 14:45:12.012
SELECT AppName
	FROM Apps
	WHERE AppName IN ('AT&T','T-Mobile')

Fixinator's scan reports:

Variables within a SQL statement must be passed as parameters to the database engine, for example by using cfqueryparam
/fixinator_testcase.cfm:13
   13: WHERE AppName IN (#PreserveSingleQuotes(AppList)#)

Possible Fixes:
        1) cfqueryparam: <cfqueryparam value="#PreserveSingleQuotes(AppList)#">
        2) cfqueryparam integer: <cfqueryparam value="#PreserveSingleQuotes(AppList)#" cfsqltype="cf_sql_integer">
        3) cfqueryparam bigint: <cfqueryparam value="#PreserveSingleQuotes(AppList)#" cfsqltype="cf_sql_bigint">
        4) cfqueryparam varchar: <cfqueryparam value="#PreserveSingleQuotes(AppList)#" cfsqltype="cf_sql_varchar">
        5) cfqueryparam timestamp: <cfqueryparam value="#PreserveSingleQuotes(AppList)#" cfsqltype="cf_sql_timestamp">
        6) int(val()) wrap: #int(val(PreserveSingleQuotes(AppList)))#"
Do you want to fix this? Enter [1-6] or cf_sql_whatever or no:

Accepting the 4th option works, but the query does not return any rows because it's searching for a single string and not a list.

SelApps (Datasource=, Time=5ms, Records=0) in C:\CommandBox\MyLogins\www\tests\fixinator_testcase.cfm @ 15:27:16.016
SELECT AppName
	FROM Apps
	WHERE AppName IN (?)
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 'AT&T','T-Mobile'

In this scenario, the proper fix when PreserveSingleQuotes is being used is to remove it and add the Replace function to remove the single quotes and add the list="true" option.

WHERE AppName IN (<cfqueryparam value="#Replace(AppList,Chr(39),'','ALL')#" cfsqltype="cf_sql_varchar" list="true">)

Results:

SelApps (Datasource=, Time=0ms, Records=2) in C:\CommandBox\MyLogins\www\tests\fixinator_testcase.cfm @ 15:34:34.034
SELECT AppName
	FROM Apps
	WHERE AppName IN (?,?)
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = AT&T
Parameter #2(cf_sql_varchar) = T-Mobile