CFQueryParam "Fails Silently" With Empty Lists And IN Clauses In Lucee 5.2.9.40
About a year ago, we converted our production app from Adobe ColdFusion to Lucee CFML. The conversion itself took several months (depending on how hard you squint); and, since the conversion, we've occasionally stumbled over a few inconsistencies between Adobe ColdFusion and Lucee CFML. Yesterday, I fixed an inconsistency in the way that the cfqueryparam tag was handling empty lists when used with an IN() clause in MySQL. Furthermore, even within Lucee CFML, it appears that an empty list is handled differently between the cfquery tag an the queryExecute() function.
To understand what I mean, let's look at a simplified demo query. In the following CFML and SQL code, I'm attempting to use an empty list of IDs in a NOT IN() clause:
<cffunction name="test">
<cfquery name="local.results">
SELECT
p.id,
p.name
FROM
project p
WHERE
p.id = <cfqueryparam value="3" sqltype="integer" />
AND
-- Trying to use an empty collection with IN() clause.
p.id NOT IN ( <cfqueryparam value="" sqltype="integer" list="true" /> )
;
</cfquery>
<cfreturn results />
</cffunction>
<cfdump var="#test()#" />
As you can see, the above cfquery is attempting to include a condition that looks like this:
p.id NOT IN( ? )
... where the given query parameter is an empty list.
Now, personally, I believe the SQL statement itself is buggy. Meaning, that the WHERE condition should have been omitted, by the developer, with a <cfif> block if the value was empty. And, in fact, that's how I fixed the bug:
<cffunction name="test">
<cfquery name="local.results">
SELECT
p.id,
p.name
FROM
project p
WHERE
p.id = <cfqueryparam value="3" sqltype="integer" />
<!--- Only include condition if we have a value-list (which we don't). --->
<cfif "".len()>
AND
-- Trying to use an empty collection with IN() clause.
p.id NOT IN ( <cfqueryparam value="" sqltype="integer" list="true" /> )
</cfif>
;
</cfquery>
<cfreturn results />
</cffunction>
<cfdump var="#test()#" />
But, the curious thing is that the original query doesn't throw an error - it just returns zero records:
I am guessing (although I haven't tested this) that Adobe ColdFusion essentially nullifies this condition, returning the intended record set as if the IN() clause wasn't executed.
What's even stranger about this whole thing is that the queryExecute() function does throw an error when attempting to use an empty IN() condition:
<cfscript>
results = queryExecute(
sql = "
SELECT
p.id,
p.name
FROM
project p
WHERE
p.id = :id
AND
p.id NOT IN ( :notIDs )
;
",
params = {
id: {
value: 3,
type: "integer"
},
notIDs: {
value: "", // Trying to use an empty collection with IN() clause.
type: "integer",
list: true
}
}
);
dump( results );
</cfscript>
As you can see, we're trying to use the same NOT IN() clause. Only this time, instead of returning an empty recordset, Lucee CFML throws an error:
Though, to be fair, it looks to be a CFML error, not a SQL error.
After some Googling, it appears that this inconsistency is a known issue and is slated to be fixed in future releases of Lucee CFML:
ASIDE: Changing this would be a "breaking change" to Lucee CFML code. So, it will be curious to see if this is handled as a "major" bump, with regard to semantic versioning; or, if this is a "minor" bump for a bug fix. Semantic versioning is fun :D
Anyway, just documenting my Lucee CFML journey. I better go through the code and sanity-check all the existing list="true" query parameters and make sure there's aren't any other subtle failures that none of the users have complained about yet.
Want to use code from this post? Check out the license.
Reader Comments
This shouldn't be hard to fix. I'm a bit swamped ATM but will look into it soon.
Igal
At the very least we should throw a better error message, but we will review the compatibility issue and try to find the best way to resolve this.
And yes, Semantic Versioning is both Fun and Important ;)
@Igal,
To be fair, there are already tickets open, so don't feel undo pressure simply because I ran into an issue. I only write this stuff to document my experience.
That said, I am surprised that this works in either Adobe ColdFusion or Lucee CFML (albeit with different outcomes). My gut tells me that this should have thrown a syntax error for SQL... essentially trying to pass nothing into an
IN()clause.But, in one of the tickets, Micha talks about this being a MySQL Driver concern and not a Lucee concern. It's a bit of a blackbox to me, so.....
Unless you were specifically talking about the
queryExecute():D Then, yeah, maybe just a better error message :P@Ben,
No pressure at all. We prioritize and work on tickets on a regular basis. Some of the factors we take into account include:
Sometimes it's a matter of bringing a specific ticket to our attention.
I will definitely check Micha's comments first.
Igal
I've looked into this issue today. The discrepancy between Lucee and ACF is that Lucee passes
NULLfor the empty list's value while ACF passes an empty string.In SQL NULL never matches anything, not even other NULLs, so both
IN (NULL)andNOT IN (NULL)return no results, while in ACFNOT IN ('')returns all of the results that are not an empty string.The discrepancy between
cfqueryandqueryExecute()in Lucee is due to the fact that Lucee handles them differently (obviously). Since passingNULLfor the empty list always yields no results, I want to fail fast the error as soon as the issue is detected. The right place to improve the error message would affect both constructs, meaning that it will break the currently (not) workingcfquerywith an error.We will have to therefore discuss this further before deciding on how to best handle it.
Please feel free to join the discussions in tickets
https://luceeserver.atlassian.net/browse/LDEV-2343
https://luceeserver.atlassian.net/browse/LDEV-1671
https://luceeserver.atlassian.net/browse/LDEV-2298
@Igal,
Excellent follow-up. I don't know how strongly I feel about the discrepancy. Frankly, I feel like using an
IN()clause with no arguments is a bug on behalf of the developer (since you can't write such a clause in raw SQL). That said, I'll take a look at the tickets. Thanks!