tracker issue : CF-4200048

select a category, or use search below
(searches all categories and all time range)
Title:

Bad replacement of single quotes in already escaped values when using named parameters

| View in Tracker

Status/Resolution/Reason: Closed/Won't Fix/BugVerified

Reporter/Name(from Bugbase): Daniel Short / Daniel Short ()

Created: 10/21/2017

Components: Database, CFQuery

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: 0

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:

The change should actually be made in query.cfc, not base.cfc. I copied the wrong file name when creating the bug report.
Comment by Daniel S.
215 | October 27, 2017 04:59:29 PM GMT