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#">

Billboard image

Use Playwright to test. Use Playwright to monitor.

Join Vercel, CrowdStrike, and thousands of other teams that run end-to-end monitors on Checkly's programmable monitoring platform.

Get started now!

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...

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay