tracker issue : CF-4203307

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

Single quotes inside of MSSQL comments cause issues with cfparam

| View in Tracker

Status/Resolution/Reason: To Track//PRNeedInfo

Reporter/Name(from Bugbase): Benjamin Melikant / ()

Created: 08/30/2018

Components: Database, General

Versions: 11.0

Failure Type: Crash

Found In Build/Fixed In Build: 307976 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Windows 7 64-bit

Vote Count: 0

Problem Description:

A coworker discovered that when a query object is created and given a SQL string containing a multi-line comment and named parameters (and potentially positional parameters) are used, the parameter parsing engine cannot properly add parameters to the query due to the parser's inability to handle single quotes within comments. The issue was traced back to the file WEB-INF/cfusion/CustomTags/com/adobe/coldfusion/query.cfc, function named replaceDelimsWithMarkers. 

Steps to Reproduce:

Create a function that builds a query object. Within the SQL for the query, place a multi-line SQL comment that contains at least one single quote. Additionally, be sure to pass at least one named parameter into the function and use addParam() to add the value for the parameter. Call execute().getResult() on the query object after everything has been set up.

Example code:

public query function getUserInformation(required string username) {
        var userQuery = new query();
        var sql = "SELECT
                        -- select some random columns, doesn't matter
                        user.FirstName,
                        user.LastName,
                        emails.Email
                   FROM UserTable user
                   /* pretend we are joining to another table for this comment's sake */
                   INNER JOIN UserEmails emails
                        ON user.ID = email.USER_ID
                   WHERE user.Username = :user";
        userQuery.setSQL(sql);
        userQuery.addParam(name="user",value=arguments.username,cfsqltype="CF_SQL_VARCHAR");
        return userQuery.execute().getResult();
    }

Actual Result:

Error code 102:  [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ':'.

Expected Result:

Query should execute normally

Any Workarounds:

To solve this issue, I updated the file in question (query.cfc) with the following code:

/**
     * replace named and positional delimiters inside single quotes with corresponding markers to allow parsing SQL on delimiters
     * @output false
     */
    private string function replaceDelimsWithMarkers(string sql, string sqlDelimtersList, string sqlDelimtersMarkersList)
    {
        // sql comments should be stripped out first in case there is a single qoute inside
        var tempSql = stripSqlComments(sql);
        var sqlArray = listtoarray(tempSql,SINGLEQUOTE,true);
		var newSql = "";
        var i = 0;
        for(i=1; i lte arraylen(sqlArray); i++)
        {
            //even numbered array indices contain value inside the single quotes
			//replace occurence of named or positional delimiters in the value with corresponding markers
            if( (i%2) eq 0 )
            {
        		sqlArray[i] = ReplaceList(sqlArray[i],sqlDelimtersList,sqlDelimtersMarkersList);
                newSql = newSql & SINGLEQUOTE & sqlArray[i] & SINGLEQUOTE & " ";
            }
            else
            {
                newSql = newSql & sqlArray[i];
            }
        }
        return newSql;
    }

    private string function stripSqlComments(required string sql) {
        // go over SQL and remove any comments!
        var MSSQL_COMMENT_SINGLE_LINE = "--.+(\r\n|\n)?";
        var MSSQL_COMMENT_MULTILINE = "/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/";

        var returnSQL = reReplace(sql,MSSQL_COMMENT_SINGLE_LINE,'');
        returnSQL = reReplace(sql,MSSQL_COMMENT_MULTILINE,'');

        return returnSQL;
    }

Basically the idea is to remove comments from the SQL before it gets processed for named arguments so that single quotes inside it will no longer break the named parameter functionality. This may need to be a more robust solution, but in our case the error went away once the comments were parsed out of the incoming SQL.

Attachments:

Comments:

Hi Benjamin, Thanks for reporting this issue.  We are not actively maintaining Query.cfc file. I have introduced a QueryExecute function in ColdFusion 11. I would recommend you to try this scenario with QueryExecute function. [https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryexecute.html] Please do let us know if it works for you. -Nimit ColdFusion Team
Comment by Nimit Sharma
29712 | September 18, 2018 07:05:10 AM GMT
So the QueryExecute function did not cause this issue to occur under the same circumstances. It seems to only be in the query object. Our development team prefers to use the query object over the QueryExecute function, so it is likely we will maintain our own fix for this.
29748 | September 28, 2018 02:09:44 PM GMT
Hi Benjamin,   Is there any specific reason your team would prefer using query object over QueryExecute? We would be interested to know before we close this ticket.    Thanks, Hari
29757 | October 05, 2018 05:02:30 AM GMT
Mostly because the code we have in place is already using the query object, but I think most of us prefer the syntax of treating a query like an object rather than a function call. Speaking for myself on this, I find it much easier to add parameters to the object rather than either cramming a struct into the function call or creating a separate parameters struct to send in. To be honest it's probably a 'we have always done it this way' type of thing for the most part.
29782 | October 11, 2018 04:05:00 PM GMT