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_INTEGER you are using the java sql type INTEGER which "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 unsigned INT. For values larger than 2147483647 you would need to use CF_SQL_BIGINT instead.

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.

Short link: https://bennadel.com/4157