Coldfusion 2016 Update from 16 to 17 crashing queries with CFQUERYPARAM

[ACS LLC]

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-installi...

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?

[Charlie Arehart]

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.

[BKBK]

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#">

[ACS LLC]

Thanks to both of you for the reply. Ouch, I'll have used a LOT of these. I'm going to have to do a mass search and replace. Hopefully it's just INT that is the problem, I may have used others such as VARCHAR. Any idea where I can find a list of valid ones? I'm going to have to fix this as I would like to upgrade in the future, so now I know for sure the code is not going to work above my current version and build.

I took a look at the development box log and it does show success

2793 Successes
0 Warnings
0 NonFatalErrors
0 FatalErrors

[Charlie Arehart]

See the table of values for cfqltype at  https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-p-q/cfqueryparam.html

[ACS LLC]

Thanks Charlie,

Btw In my administator is also says I have version 17.

Rather odd that it's working locally but not in production. I'll have to do a big search of all code on all sites and fix it all so I don't have to worry about it int he future. The kicker is that my development version won't through the error, so it's challenging to test until it's in prod 😞

[Charlie Arehart]

That would indeed be odd (that you would NOT experience the error on a CF2016 update 17 implementation), because again the technote for the update clearly indicates this change ("Invalid cfsqltypes are ignored").

So there simply MUST be some explanation for why you are not seeing the error. I know you said you already confirmed there were 0 fatalerrors in the update log. Next, are you sure you don't have more than one instance of CF in your local setup? That could either be another instance alongside cfusion (if you used the CF Admin Intsance Manager to create one), or perhaps you are using MORE than one installed CF instance.

For example, while most people install CF with the cf installer, some people also implement ColdFusion Builder and in recent releases it has offered to install a CF instance within the CFBuilder folder. That one has its own admin, and would need its own updates.

Here is what you could do to at least confirm that the version you feel is "working fine" (doesn't get this new error) is indeed running CF2016 update 17: do a cfoutput or cfdump of #server.coldfusion.productversion#. Is it really reporting update 17, on the page that "works"? If not, consider one of the possibilities above.

Let us know how it goes.

[BKBK]

@ACS LLC :  Hopefully it's just INT that is the problem

Yes, just INT. Note that the cf_sql_ prefix is optional. Hence:

• cfsqltype="CF_SQL_INTEGER" or cfsqltype="INTEGER"
• cfsqltype="CF_SQL_VARCHAR" or cfsqltype="VARCHAR"

See, for example, https://www.petefreitag.com/item/855.cfm