Skip to content

Instantly share code, notes, and snippets.

@JamoCA
Last active April 8, 2020 17:02
Show Gist options
  • Save JamoCA/4fbd1a8336c71f5f21e982ea2b10d448 to your computer and use it in GitHub Desktop.
Save JamoCA/4fbd1a8336c71f5f21e982ea2b10d448 to your computer and use it in GitHub Desktop.
Adobe ColdFusion 10, 11, 2016 & 2018 throws an error when access column names that use "first" or "last". (Railo & Lucee work.)
<!--- 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#">
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment