DEV Community

James Moberg
James Moberg

Posted on • Edited on

ColdFusion Query-of-Query Reserved Words or Bug?

I encountered an Adobe ColdFusion error where a query would throw an error if certain column names were accessed. A third-party client uploaded an Excel file using "first" and "last" column names. When I attempted to access those columns independently using ColdFusion 2016.0.14.318307 with a query-of-queries CFQuery, an error "Query Of Queries syntax error. Encountered FIRST. Incorrect Select List, Incorrect select column," was thrown. I checked the official about and user guide support pages and there's no indication that any column names are reserved. (NOTE: I'm also using the Microsoft JDBC Driver for SQL Server instead of the native DataDirect drivers when performing non-in-memory SQL queries.)

"first" and "last" aren't reserved keywords in MSSQL. I can create queries with these column names and access them if the asterisk is used to "select all" columns, but sometimes I only need to access specific columns, wish to create an alias, concat values or change the column order.

If I perform the same QofQ query using either Lucee or Railo, it works.

I checked against Pete Freitag's SQL Reserved Words Checker and do see that "first" and "last" are reserved when using ODBC, DB2, PostgreSQL 8 and ISO/ANSI,SQL99. So which SQL standard is used by Adobe versus Lucee/Railo?

As a result, I'm wondering what other undocumented keywords may be reserved. Since this works in Railo (from 2014) & Lucee, I'm going to consider this a bug. Adobe will probably respond with "not a bug. works as expected" and then not follow up to identify all undocumented reserved column names.

Workarounds

Create a struct of all reserved keywords and the safe alternative that you wish to replace it with and use java SetColumnNames() to rename the columns.

myQuery.SetColumnNames(["firstName", "lastName"]);
Enter fullscreen mode Exit fullscreen mode

Zac Spitzer recommended using brackets, so I used the following (which may not be the most elegant, but it works if using a comma-delimited list of column names):

SELECT [#replace(Test.columnList, ",", "],[", "all")#]
Enter fullscreen mode Exit fullscreen mode

Bug Reported

I reported bug CF-4207962 to Adobe. I tried to search to see if it had already been reported, but didn't know exactly know what to search for as the results were too numerous.

Demos

Source Code

<!--- 20200408 Query-of-Queries Column Name Test
Column names like "first" and "last" throw errors in Adobe ColdFusion 10, 11, 2016 & 2018, but work without
any errors in Railo or Lucee.
NOTE: Message on CFFiddle.org states "CFFiddle currently doesn't support some of the tags used in the cfm file like cfquery."
Blog: https://dev.to/gamesover/coldfusion-query-of-query-reserved-words-or-bug-4ppf
TryCF 10: https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf
TryCF 11: https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf11
TryCF 2016: https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf2016
TryCF 2018: https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=acf2018
TryCF Railo: https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=railo
TryCF Lucee: https://trycf.com/editor/gist/4fbd1a8336c71f5f21e982ea2b10d448?engine=lucee
renameColumn UDF: http://www.neiland.net/blog/article/using-java-to-rename-columns-in-a-coldfusion-query-object/
SOLUTION: Rename known columns using java SetColumnNames() or bracketify all column names.
--->
<cfset test = queryNew("first,last", "varchar,varchar")>
<cfset queryAddRow(test)>
<cfset querySetCell(test,"first","Mickey")>
<cfset querySetCell(test,"last","Mickey")>
<h2>Dump of manually created query</h2>
<cfdump var="#test#">
<cfquery name="testAll" dbtype="query">SELECT *
FROM test</cfquery>
<h2>Dump of SELECT *</h2>
<cfdump var="#testAll#">
<h2>Dump of SELECT First, Last</h2>
<cftry>
<cfquery name="TestFirst" dbtype="query">SELECT #Test.columnList#
FROM test</cfquery>
<cfdump var="#TestFirst#">
<cfcatch type="any">
<div style="color:red;">Error!</div>
<cfdump var="#Cfcatch#" expand="false" label="Error - click to view">
</cfcatch>
</cftry>
<h2>Sanitize "Known" Reserved Column Names</h2>
<cfset testSanitized = duplicate(test)>
<cfset ColumnData = {
"Names" = test.GetColumnNames(),
"Alternatives" = {"last"="LastName", "First"="FirstName"},
c = 0
}>
<cfloop array="#ColumnData.Names#" index="thisCol">
<cfset ColumnData.c = ColumnData.c + 1>
<cfif StructKeyExists(ColumnData.Alternatives, thisCol)>
<cfset ColumnData.Names[ColumnData.c] = ColumnData.alternatives[thisCol]>
</cfif>
</cfloop>
<cfset testSanitized.SetColumnNames(ColumnData.Names)>
<cfdump var="#testSanitized#">
<cfquery name="TestFirst" dbtype="query">SELECT FirstName, LastName
FROM testSanitized</cfquery>
<cfdump var="#TestFirst#">
<h2>Add Brackets to All Column Names</h2>
<cfquery name="TestBrackets" dbtype="query">SELECT [#replace(Test.columnList, ",", "],[", "all")#]
FROM test</cfquery>
<cfdump var="#TestBrackets#">

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (1)

Collapse
 
gamesover profile image
James Moberg

I've encountered issues when continuing to work with a query after using SetColumnNames(). When retrieving the column names using columnlist, getMetaData().getColumnLabels() and getColumnNames(), the getMetaData() function would only return the initial columns names instead of the modified column name.

To work around this, perform a QofQ using an alias. Here's a user-defined function (UDF).
gist.github.com/JamoCA/3bb02cdbdea...

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay