Last active
April 8, 2020 17:02
-
-
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.)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!--- 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