Improper handling of query using IN ( ... ) matching with PreserveSingleQuotes #19
Description
<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