tracker issue : CF-4203307

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: