Is this a ColdFusion Bug in cfscript?

Question

This is a valid query to return emails surrounded by single quotes from the users table.

SELECT  '''' +email + '''' as email
FROM    users
where fname = @fname

Yet when I try to do this in a cfscript (cf9) query like this :

var q = new Query(datasource="warewithal");
q.setSQL("SELECT  '''' +email + '''' as email
                FROM users where firstName= :firstName ");
q.addParam(name="firstName", value=trim(firstName), cfsqltype="cf_sql_varchar");

what I end up with is

Email
+email+

when I expected (and get by running the query in an analyzer)

Email
'[email protected]'

the cfscript is doing a perserveSingleQuote and not letting me add the single quote to the output.

Is this a bug or what am I doing wrong?

Answer 1

To answer the question in your headline: no, this is not a bug in CFScript. What you are demonstrating has nothing to do with CFScript at all.

However to answer the question you mean to be asking: yes, you have found a bug in Query.cfc.

Here's some code that demonstrates the bug (and demonstrates that it has nothing to do with CFScript, and everything to do with Query.cfc).

This code works fine:

<cfset query = new Query(datasource="scratch_mssql")>
<cfset query.setSql("
    SELECT  '''' + email + '''' as email
    FROM    users
    WHERE   firstName = '#firstName#'   
")>
<cfset emailAddresses = query.execute().getResult()> 
<cfdump var="#emailAddresses#">

Note that I have hard-coded the filter value into the SQL string. Yuck.

This code errors:

<cfset query = new Query(datasource="scratch_mssql")>
<cfset query.setSql("
    SELECT  '''' + email + '''' as email
    FROM    users
    WHERE   firstName = :firstname  
")>
<cfset query.addParam(name="firstname", value=firstName, cfsqltype="CF_SQL_VARCHAR")>
<cfset emailAddresses = query.execute().getResult()> 
<cfdump var="#emailAddresses#">

The error for me is:

[Macromedia][SQLServer JDBC Driver][SQLServer]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

FYI: The SQL that CF had passed to the DB was:

SELECT  '' '' + email + '' '' as email FROM users WHERE firstName = (param 1)

To prove that it's caused by ColdFusion mishandling your single quotes, this works:

<cfset query = new Query(datasource="scratch_mssql")>
<cfset query.setSql("
    SELECT  email as email
    FROM    users
    WHERE   firstName = :firstname  
")>
<cfset query.addParam(name="firstname", value=firstName, cfsqltype="CF_SQL_VARCHAR")>
<cfset emailAddresses = query.execute().getResult()> 
<cfdump var="#emailAddresses#">

So I'd raise a bug for this if I was you. Note: it's the same on CF9.0.2 and CF10.0.7

But, equally... if I was you I'd not be putting those quotes in there anyhow. Unless there's a very good reason, stick 'em in when you're doing the display, not when you're doing the data processing. I presume they're there for display purposes?