http://coldfusion.com logo
Join Slack
Powered by
Is it possible to use a full colin ":" in a column...
# cfml-general
g
Is it possible to use a full colin ":" in a column name in a query? We have a piece of data that is known as a "Metric" to our end users. Our end-users give the Metric a name and the possible values that the Metric can be. Internally, to the DB - the column name is a unique / random string. When showing data on screen / in exports - we use:
Copy code
SELECT
    random_string AS customersMetricName
A customer has entered a lot of metric names that contain full colins ":". • Group:attribute1 • Group:attribute2 • ... So the dynamically produced SQL string looks like;
Copy code
SELECT
    random_string AS Group:attribute1
FROM ....
As a consequence using cfquery fails because it goes looking for a query parameter called "attribute1"
Copy code
The named parameter [attribute1] has not been provided
The Error Occurred in
/org/lucee/cfml/Query.cfc: line 188
called from /org/lucee/cfml/Query.cfc: line 103
called from /org/lucee/cfml/Query.cfc: line 34
called from xxxxxx
I tried "back - ticking", "`" and double-quoting and single quoting the column name - but get the same error in the application.
Copy code
SELECT
    random_string AS `Group:attribute1`
FROM ....
When using the SQL directly on the database (MariaDB) - it works without error. (irrespective of the quotes / back-ticks usage) Short of "Don't allows users to have values with ':'s in values that are used as column names"... Any thoughts on how I might get this to work? Thanks!
From this GIST: https://trycf.com/gist/f607c0bf4708ec6074d8e37c08318bb7/lucee5?theme=monokai Lucee allows it to be used as a columnname. The DUMP works if you exclude the QoQ But fails when trying to use ":" in a select statement/ ACF doesn't even allow the queryNew() to work if there is a ":" in a column name.
z
Come on, what's going to be written on my grave? Which version of Lucee?
g
Sorry Zac. ALL versions of lucee available on tryCF.
So - It would seem that Lucee needs to throw an exception in queryNew() (and anywhere else) that creates an SQL object - to dissallow the use of ":" in column names. I am getting around it at the moment by replacing occurrences of ":" with "_" And we'll also disallow the colin to be used in the "Metric" form.
z
queries in lucee are very flexible, you can stick anything in a cell it's throwing an error, when it can't support a silly column name, so i don't quite see the problem?
r
Wrap customer:name in square brackets, like so [customer:name].
@gavinbaumanis wrapping in single quotes will also work: 'customer:name . since ' and [] are standard conventions for escaping special characters in SQL.
💯 2
g
Single-quotes definitely did NOT work, nor double-quotes, nor back-ticks. When I get a minute to spare I will revert the changes I made and try the square brackets.
@zackster I see your point. you can for example use a string with a math operator and get have an exception thrown. So changing nothing would keep query operating the same way. In a sense, it is different to the way that ACF operates and perhaps that is enough to have it changed? Initially the error message just didn't make sense to me - because there were no SQL parameters in the query. It really threw me. Then: when I finally worked out why the SQL was erroring - my opinion was very strongly in the vain of "It is plain stupid to allow me to use something in the first place that is only going to throw an exception. (Thus I think the ACF option of throwing an exception when using the ":" - is a "nicer" choice. Now that I understand it. Now that I have corrected my code to be correct for both CFML engines - I am far less bothered and will leave it to the Lucee team / other community members to decide what if anything needs to be done.
g
Have you ever considered queryListeners?
They allow you to manipulate the SQL before it get’s executed. Perhaps Lucee doesn’t throw the error when executing
You can define one global listener per datasource in the Application.cfc
Copy code
<cfscript>
listener={
	before= function (caller,args) {
        dump(label:"before",var:arguments);
    	args.sql&=" where Tables_in_test<>'test'"
        return arguments;
	},
	after= function (caller,args,result,meta) {
        dump(label:"after",var:arguments);
		var colName="table_name_lower_case";
		queryAddColumn(arguments.result,colName);
		loop query=result {
			result["table_name_lower_case"]=lCase(result["Tables_in_test"]);
		}
		return arguments;
	}
}
</cfscript>

<!--- custom listener --->
<cfquery name="qry" datasource="mysql" listener="#listener#">
	show tables
</cfquery>
<cfdump var="#qry#">
Open in Slack
PreviousNext
Powered by