Skip to main content
A
ACS LLC
Inspiring
March 27, 2021
Question

Coldfusion 2016 Update from 16 to 17 crashing queries with CFQUERYPARAM

  • March 27, 2021
  • 1 reply
  • 788 views

I just noticed a ticket with a similar issue although the fix did not work for me, but I will repost here

https://community.adobe.com/t5/coldfusion/coldfusion-2016-update-17-getting-500-error-after-installing-when-using-elvis-operator/m-p/11921655

 

 

I just updated CF2016 from update 16 to update 17 and it's crashing my queries with a 500 error. I performed the same update on my developer edition and it works fine. I've checked all of the settings including the version of JVM and everything appears to be identical.

 

The problem appears to be in CFQUERYPARAM, but not all of them. This WHERE for example will cause the 500 error

WHERE enduser_uid = <cfqueryparam value="#GetEnduser.enduser_uid#" cfsqltype="CF_SQL_INT">

 

I tried to replace the #GetEnduser.enduser_uid# with a hardcoded value, it still crashed, but when I removed the cfqueryparam and used WHERE enduser_uid = 123456 the query works.

 

These all worked fine before the update, plus with having two systems the dev and prod and only one doing this, I'm at a bit of a loss.

 

Any ideas anybody?

 

This topic has been closed for replies.

1 reply

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
March 27, 2021

I saw and replied to your comment in that other thread, but to help readers of this one, I'll repeat the key points here (and look forward to your reply to either). 

 

You say you're using sql_int, and the issue is that that is now invalid: it should be sql_integer. The docs will show the latter has always been supported, but the former is not listed.

 

And one of the changes implemented in the update for cf2016 is that it no longer ignores invalid cfsqltype values. (That change has been in CF2018 since its release). This is discussed in the technote for the update, and even more in the tracker ticket it points to, which also clarifies that this is a security concern (that cf had ignored invalid types and therefore did not do expected validation), which people complained there should be fixed.  Again, the change WAS rolled into cf2018 when it came out 3 years ago. 

 

As for them rolling that change into this update (which is the last one ever for cf2016) with such a breaking change, it is indeed unfortunate. But again it was done for security reasons. Perhaps Adobe will soon offer a jvm arg to let folks switch it off, for those whose amount of affected code is too great. 

 

Finally, as for you're not getting the same error for the same code on the same cf2016 update on the other machine, it would seem that can't be. I'd think this means you have not successfully updated the "working" one. Check the hf-updates folder there, and its update 17 folder and its install log, to confirm it shows "0 fatalerrors". Let us know what you see.

/Charlie (troubleshooter, carehart. org)
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
March 28, 2021

I would second Charlie's point. Cfsqltype="CF_SQL_INT" is incorrect. Correct is either cfsqltype="CF_SQL_INTEGER" or cfsqltype="INTEGER". 

Prior to recent versions, ColdFusion was more forgiving. If you used an incorrect cfsqltype, such as cfsqltype="CF_SQL_INT", or even cfsqltype="CF_SQL_ABRACADABRA", ColdFusion would interpret it as the default cfsqltype="CF_SQL_CHAR".

For example, the following code worked without any errors on CF2016 Update 16 (but it will fail on recent CF versions):

 

<cfscript>
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
    [ 
       {id=1,name="One",amount=15}, 
       {id=2,name="Two",amount=18}, 
       {id=3,name="Three",amount=32} 
    ]); 
</cfscript>

<cfquery dbtype="query" name="q">
    select * from myQuery
    where amount = <cfqueryparam cfsqltype="cf_sql_abracadabra" value="18">
</cfquery>

<cfdump var="#q#">

 

 

 

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices