Is this a ColdFusion bug in cfscript?
This is a valid request to return emails surrounded by single quotes from the users table.
SELECT '''' +email + '''' as email
FROM users
where fname = @fname
However, when I try to do it in a cfscript (cf9) request like:
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");
I ended up
Email
+email+
when i expected (and get by running query in parser)
Email
'bozo@clowns.com'
cfscript does perserveSingleQuote and does not let me add a single quote to the output.
Is this a bug or what am I doing wrong?
source to share
To answer the question in the title: no, this is not a bug in CFScript. What you are demonstrating has nothing to do with CFScript.
However, to answer the question you want to ask: yes, you found a bug in Query.cfc.
Here is some code that demonstrates the error (and shows that it has nothing to do with CFScript and everything related to 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 coded the filter value in the SQL string. Ugh.
These 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 passed to the DB:
SELECT '' '' + email + '' '' as email FROM users WHERE firstName = (param 1)
To prove that ColdFusion caused your single quotes to be misused, 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 would raise a bug for this if I were you. Note: the same on CF9.0.2 and CF10.0.7
But, equally ... if I were you, I wouldn't put these quotes like that. Unless you have a compelling reason, insert them when you are doing the display, not when you are doing data processing. I assume they are there for display?
source to share
For what it's worth, I just got this problem. What seems to work is a simple fix for your Query.cfc object (/ [YOUR-CF-INSTALL] /CustomTags/com/adobe/coldfusion/Query.cfc). In replaceDelimsWithMarkers () function update this line (LN 341 for me)
newSql = newSql & SINGLEQUOTE & sqlArray[i] & SINGLEQUOTE & " ";
removing the quoted space at the end, this is:
newSql = newSql & SINGLEQUOTE & sqlArray[i] & SINGLEQUOTE & "";
Then it works great. I was looking for users and I needed to find the one with the last name "O'Neill". No matter what I did, I was getting "O", "Nile" which you claim is failing.
Can you think of any cases where this change / fix would have a negative impact? I can't get off my head, but for now we're going to copy the query.cfc file, rename it, and use it as a custom tag ONLY when we need to run queries where we suspect an extra apostrophe might be used. The only difference is one small change.
source to share