QueryExecute named parameter gotcha!

August 7, 2019
Newbie 2 posts
Followers: 0 people
3

QueryExecute named parameter gotcha!

Newbie 2 posts
Followers: 0 people
August 7, 2019

I’ve not seen this posted before, apologies if it’s already been discussed!

Our team have identified a bug when using QueryExecute and named query parameters. This doesn’t appear to affect all installations of ColdFusion, as it doesn’t affect my machine, but doesn’t work on our QA installation.

When using named parameters, ColdFusion can map to the shorting named parameter, ie, if you have both “col1” and “col10” as named parameters, when “col10” gets replaced, it will match “col1” instead.

An example of this is shown below. Given the following query:

var SQL = ‘ INSERT INTO _tempTable123 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)

VALUES

( :col1 , :col2 , :col3 , :col4 , :col5 , :col6 , :col7 , :col8 , :col9 , :col10 )’;

var queryParams = {

col1  = {value= ‘a’, cfsqltype=”cf_sql_varchar”},

col2  = {value= ‘b’, cfsqltype=”cf_sql_varchar”},

col3  = {value= ‘c’, cfsqltype=”cf_sql_varchar”},

col4  = {value= ‘d’, cfsqltype=”cf_sql_varchar”},

col5  = {value= ‘e’, cfsqltype=”cf_sql_varchar”},

col6  = {value= ‘f’, cfsqltype=”cf_sql_varchar”},

col7  = {value= ‘g’, cfsqltype=”cf_sql_varchar”},

col8  = {value= ‘h’, cfsqltype=”cf_sql_varchar”},

col9  = {value= ‘i’, cfsqltype=”cf_sql_varchar”},

col10 = {value= ‘j’, cfsqltype=”cf_sql_varchar”}

};

return queryExecute(sql , queryParams , {datasource=dsn.name});

The expected query that would be executed would be:

INSERT INTO _tempTable123 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)

VALUES

(‘a’ , ‘b’ , ‘c’, ‘d’, ‘e’, ‘f’, ‘g’, ‘h’, ‘i’, ‘j’ )

Instead, the following query would be executed:

INSERT INTO _tempTable123 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)

VALUES

(‘a’ , ‘b’ , ‘c’, ‘d’, ‘e’, ‘f’, ‘g’, ‘h’, ‘i’, ‘a’0 )

Which will result in a query error.

The workaround for this is to suffix the named params with additional characters (ie col1_, col10_), or to rename the named query params so that the shortest name doesn’t match.

Comments (3)
2019-08-13 00:48:42
2019-08-13 00:48:42

That’s certainly an odd one, but it would REALLY help if you’d indicate what cf version and update level this did and did not work on.

Like
(1)
(2)
>
Charlie Arehart
's comment
2019-08-15 12:54:46
2019-08-15 12:54:46
>
Charlie Arehart
's comment

Both versions are CF11. This works fine update pack 14 onwards, but didn’t work  on update pack 3. I assume it was fixed in a pack between those two, and updating the latest version would resolve this, but wanted to help anyone else who encountered the same issues.

Like
>
Chris Bossons
's comment
2019-08-15 13:48:40
2019-08-15 13:48:40
>
Chris Bossons
's comment

Great, thanks. I pressed because you said it worked on one but not another machine, yet you left it at proposing the workaround. 🙂

Maybe it would help readers to add a closing sentence above (since some folks don’t read comments) indicating that later updates did fix it, and that the workaround may help for someone who somehow couldn’t update.

As for which update fixed it, I see no mention of it in the bugs fixed for the latest cf11 u19 (https://helpx.adobe.com/coldfusion/kb/bugs-fixed-in-coldfusion-11-update-19.html), so it’s not easy to tell when exactly it was fixed–or indeed if it affected cf2016 or 18, either.

Like
Add your comment