CAUTION: Silent Value Truncation In CFQueryParam Tag In Lucee CFML 5.3.7.47
I just ran into a highly-problematic behavior with the CFQueryParam tag in Lucee CFML 5.3.7.47. As it stands now, if you pass a value via the CF_SQL_INTEGER SQL type that is larger than the max signed-value (2147483647), Lucee will silently truncate it, passing the max signed-value to the underlying SQL query. To fix this, you have to use CF_SQL_BIGINT in your query parameter binding.
At first, they may not sound like a problem since an Int is an Int is an Int. But, it's not that simple. In the (MySQL) database, there are two types of 4-byte integers:
SIGNED INT→ Range from -2,147,483,648 to 2,147,483,647.UNSIGNED INT→ Range from 0 to 4,294,967,295.
ASIDE: There are more than two types of integers, but for this conversation, we're talking about 4-byte integers. I am not sure if the silent truncation happens for the other types (but I assume it would).
What this means is that an UNSIGNED INT column in MySQL can legitimately store a value that it outside the range of the values that fit into the CFQueryParam tag's CF_SQL_INTEGER handling. Silently truncating this value means that the wrong value is passed-through to the underlying SQL query.
We can test this with a simple SELECT-only query:
| <cfscript> | |
| // Largest signed-INT value that can fit in a 4-byte Java integer. | |
| maxInt = 2147483647; | |
| // Value that it outside of Java's signed INT space, but could be inside of the | |
| // database's "UNSIGNED INT" column type. | |
| largerInt = ( maxInt + 1 ); | |
| ``` | |
| <cfquery name="test" result="result"> | |
| SELECT | |
| ( <cfqueryparam value="#largerInt#" sqltype="integer" /> ) AS paramInt, | |
| ( <cfqueryparam value="#largerInt#" sqltype="bigint" /> ) AS paramBigInt, | |
| ( #largerInt# ) AS rawValue | |
| ; | |
| </cfquery> | |
| ``` | |
| // Output results of MySQL query and parameter binding. | |
| dump( test ); | |
| echo( "<br />" ); | |
| dump( result ); | |
| </cfscript> |
Note that we are taking a value that doesn't fit in Java's 32-bit signed Integer and we're using it in two CFQueryParam bindings, one with CF_SQL_INTEGER and one with CF_SQL_BIGINT. And, when we run this Lucee CFML code, we get the following:
Notice that with the CF_SQL_INTEGER type, the value went into the parameter binding as 2147483648, but was passed-through to the underlying SQL query as 2147483647 (as a silently-truncated value). The CF_SQL_BIGINT binding, on the other hand, allowed the value through as is (2147483648).
The ramifications of this are very un-good! It means that you run the risk of pulling the wrong record back from the database. Granted, the scope of damage is limited in that every value over the CF_SQL_INTEGER range would be silently truncated to the same, consistent value. But, it's still the wrong record being accessed.
I tried running this test in Adobe ColdFusion 2021 instance:
| <cfscript> | |
| // Largest signed-INT value that can fit in a 4-byte Java integer. | |
| maxInt = 2147483647; | |
| // Value that it outside of Java's signed INT space, but should be inside of the | |
| // database's "UNSIGNED INT" column type. | |
| largerInt = ( maxInt + 1 ); | |
| test = queryExecute( | |
| " | |
| SELECT | |
| ( :param1 ) AS paramInt, | |
| ( :param2 ) AS paramBigInt, | |
| ( #largerInt# ) AS rawValue | |
| ; | |
| ", | |
| { | |
| param1: { | |
| value: largerInt, | |
| cfsqltype: "cf_sql_integer" | |
| }, | |
| param2: { | |
| value: largerInt, | |
| cfsqltype: "cf_sql_bigint" | |
| } | |
| }, | |
| { | |
| result: "result" | |
| } | |
| ); | |
| // Output results of MySQL query and parameter binding. | |
| writeDump( test ); | |
| writeOutput( "<br />" ); | |
| writeDump( result ); | |
| </cfscript> |
We still run into the same issue that a MySQL UNSIGNED INT won't fit inside of the CFQueryParam range using CF_SQL_INTEGER; but, at least this throws an error instead of truncating:
Invalid data 2.147483648E9 for CFSQLTYPE CF_SQL_INTEGER.
Regardless of the problematic silent truncation in Lucee CFML or the thrown error in Adobe ColdFusion, what strikes me as the most problematic is that I didn't know about this; and, that it doesn't appear to be a [well] documented consideration. As I was looking into this, I found a CFSearching post from 2010 that talks about bindings and type-selection:
When you use
CF_SQL_INTEGERyou are using the java sql typeINTEGERwhich "represents a 32-bit signed integer value ranging between -2147483648 and 2147483647". So obviously it does not have the capacity to represent the upper ranges of an unsignedINT. For values larger than 2147483647 you would need to useCF_SQL_BIGINTinstead.
Call me crazy, but I think this should be messaged hard on the official documentation for the <cfqueryparam> tag. This is information the people need to know!
Epilogue on Lucee Jira Ticket
After writing this, I took a look in the Lucee Jira install and I found a ticket that discusses the binding truncation. So, at least it's on their radar!
Want to use code from this post? Check out the license.
Reader Comments