Title:
Bad replacement of single quotes in already escaped values when using named parameters
| View in TrackerStatus/Resolution/Reason: Closed/Won't Fix/BugVerified
Reporter/Name(from Bugbase): Daniel Short / Daniel Short ()
Created: 10/21/2017
Versions: 2016,11.0
Failure Type: Data Corruption
Found In Build/Fixed In Build: 10,0,18,296330 /
Priority/Frequency: Normal / Most users will encounter
Locale/System: ALL / Win 2012 Server x64
Vote Count: 1
Problem Description:
When running a query that contains a named parameter, any single quotes that have already been escaped have spaces added between them. This does not happen if you're not using any named parameters
Steps to Reproduce:
Run the following query:
<cfscript>
myQuery = New Query( sql: " SELECT 'It''s Time!' WHERE 1 = :Check" );
myQuery.addParam( name = "Check", cfsqltype = "cf_sql_integer", value = "1" );
result = myQuery.Execute();
</cfscript>
Actual Result:
The following query gets run:
qryname42637 (Datasource=FR_LOADSN, Time=4ms, Records=1) in C:\ColdFusion10\cfusion\CustomTags\com\adobe\coldfusion\base.cfc @ 20:09:40.040
SELECT 'It' 's Time!' WHERE 1 = ?
Query Parameter Value(s) -
Parameter #1(cf_sql_integer) = 1
Expected Result:
qryname42637 (Datasource=FR_LOADSN, Time=4ms, Records=1) in C:\ColdFusion10\cfusion\CustomTags\com\adobe\coldfusion\base.cfc @ 20:09:40.040
SELECT 'It''s Time!' WHERE 1 = ?
Query Parameter Value(s) -
Parameter #1(cf_sql_integer) = 1
Any Workarounds:
A change to \ColdFusion10\cfusion\CustomTags\com\adobe\coldfusion\base.cfc fixes this problem:
Change line 343 from this:
newSql = newSql & SINGLEQUOTE & sqlArray[i] & SINGLEQUOTE & " ";
to this:
newSql = newSql & SINGLEQUOTE & sqlArray[i] & SINGLEQUOTE;
Remove the trailing space that gets added, and everything works correctly. When splitting the list to an array on line 333, you're not removing any spaces, so there is no reason to add spaces back once you reconstruct the sql statement.Attachments:
Comments: