Different cfqueryparam behavior between acf and lucee

lucee allows <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="">
acf crashes <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="">

But only in QoQ?

It happens with sql server as well

and in the sql debug, it shows up somewhat confusingly as

where id = [CF_SQL_INTEGER]

or

where id = [CF_SQL_NUMERIC]

when I’m assuming it’s actually passing in null?

and if i change the datatype to VARCHAR,

<cfqueryparam cfsqltype="varchar" value="">

it throws Error converting data type nvarchar to numeric.

Lucee handles null a whole lot better than ACF and, to my mind, Lucee has this right. I’d suggest filing a bug for ACF at https://tracker.adobe.com/

That’s JDBC semantics (to have “typed” nulls) in a parameter replacement, as it usually would get turned into

DECLARE @p0 int=null
SELECT * from table where id=@p0

(Slight oversimplification to remove the sp_prepare and execute and unprepare bits)

Proper way to do this in ACF IMHO would be

<cfqueryparam cfsqltype="cf_sql_integer" value="#Trim(v)#" null="#Not Len(Trim(v))#" />

where v is your incoming variable. That’s what we do all over our codebase for inserts, or updates where you’re adding or changing a value to a null.

With an update we’d wrap that in a <cfif StructKeyExists(arguments,"v")> for conditional updating semantics.

But I think in your case you need to revisit your strategy. SQL would error if you gave it the query SELECT * FROM sometable WHERE id='', that CF does also doesn’t seem to be a CF problem.

And what are you trying to accomplish? WHERE id='' is not valid, nor is WHERE ID=NULL - you’d need to change your query to do IS NULL if that’s truly what you want.

WHERE
   <cfif not Len(Trim(v))> id IS NULL <cfelse><cfqueryparam cfsqltype="cf_sql_integer" value="#Trim(v)#" /></cfif>

IMHO that’s the programmers responsibility, just as it would be to make sure v is actually a number, instead of something like “J”. Realizing that CF believes something like “1e1” is a number, while SQL doesn’t (in MSSQL you’d have to pass it as a float to parse, not an integer or numeric()) may mean regex or Len(spanincluding(trim(v),"0123456789")) EQ len(trim(v)) validation.

My examples are based on MSSQL semantics - for QoQ you’d have to use H2 semantics.

2 Likes

thanks @joe.gooch, I’ve been doing that approach for many years!
@ddspringle adobe aren’t going to change the behaviour.

It was more documenting the different between the two engines.

For Lucee, the sql debug needs updating to show nulls instead of the datatype
and the documentation needs to be updated

Is this the only official differences guide between acf and lucee at the moment?

there is a much more substantial list here

@Zackster I think based on what you’re saying Lucee is the less preferred approach.

I agree that if you’re using a cfqueryparam to populate a value you’re inserting or updating, it might be slightly more convenient for “” to be the same as NULL. But we have the null attribute for that purpose and it’s relatively easy to calculate, or to specify separately.

BUT if you’re using cfqueryparam in a WHERE clause, like you are, and it silently transforms to null, the query results will be invalid and there won’t be a clear indication why. In that case the ACF behavior of throwing an exception is exactly what I would want to happen.

My preference is for the ACF behavior - because it gives you a sane default with the options to do what you need it to do. In Lucee, the default could cause invalid query results, silently, and you’d have no way to tell queryparam to throw an exception instead, so now you NEED to do length checks on all your queryparams before running the query, and as you indicated, the debugging output doesn’t even help.

The appearance of things like IsNull() in CFML reinforces that in todays programming world, Null != “”, and I think this behavior is a step backwards.

Just my $.02

1 Like

I agree , the acf behaviour is better, that said having <cfqueryparam value=“v” type=“integer” null=“auto”>,
which would be the same as null=“#Not Len(Trim(v))#” would be nice

1 Like

Hi all,

I ran into this issue recently and would like to revive this conversation, because in my opinion this behaviour needs to change. I spent a lot of time debugging this issue.

I have never tried running this in ACF as we use Lucee, and I totally didn’t expect that <cfqueryparam> will silently turn empty value into NULL!

I am working with a codebase not written by me, and I agree there are approaches for checking the value before using it more thoroughly, but this behaviour is not documented and is counterintuitive. I would expect the tag to either throw an error or convert “” into a 0 in case of numeric cfsqltype.

<cfquery result="test">
    INSERT INTO AdminUser (active)
    VALUES (<cfqueryparam cfsqltype="cf_sql_bit" value="">);
</cfquery>
 
<cfdump var="#test#">

This code inserts NULL into the DB. I don’t think this is the correct behaviour.
Should I file a bug?

Marianna

I think you should file a bug Marianna. An empty string is not a valid value for a bit, integer so it should throw an exception. You should also note in the bug report that it is incompatible with ACF, even though that doesn’t matter in your case, it should give the issue a higher priority.

3 Likes

Which database are you working with?

I am using Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

Zac, I found a bug that you submitted in 2017 that’s I think is the same issue, which is still in the backlog. I added my comment.
https://luceeserver.atlassian.net/browse/LDEV-1532?jql=text%20~%20"cfqueryparam"%20order%20by%20created%20DESC

We’ve backed out the fix as it caused other problems in 6.0

https://luceeserver.atlassian.net/browse/LDEV-1532

As we are looking at 6.0, I’m open to the idea of matching ACF behaviour, i.e. throwing an error

CF’s behaviour is correct IMO. As @MariannaAtPlay & @pfreitag allude to: an empty string is not a valid value for a numeric type. And an empty string is also not any approximation / equivalent of null.

If you give a string (even an empty one) when a numeric value is required, one should get a runtime error, and enable the dev to fix their code. Not silently second-guess what they actually mean and apply magic special behaviour instead, which has the sole effect of hiding a problem situation.

7 Likes

For what it’s worth, I agree with @AdamCameron, @MariannaAtPlay & @pfreitag.

ok then , I have created a new ticket to throw an exception instead of casting to null

this will be a breaking change (for the better) for Lucee 6.0

https://luceeserver.atlassian.net/browse/LDEV-4410

4 Likes

Cheers for taking the time to ask about this, and then listening, @Zackster.

4 Likes

ok, played around a few approaches, this one seems the best, should actually be faster for everyone as it removes a try catch around most query parameter handling

2 Likes

this change has been implemented in 6.0.0.383