cfsqltype, CF_SQL_NUMBER not supported in CF2018. It worked up to CF2016
Migrating to CF2018 from CF2016, we’ve encountered that CF_SQL_NUMBER is no longer a valid cfsqltype. Statements such as
<cfqueryparam value=”#request.projectid#” cfsqltype = “CF_SQL_NUMBER”>
need to be changed to
<cfqueryparam value=”#request.projectid#” cfsqltype = “CF_SQL_NUMERIC”>
This did not show up on our compatibility scan of the code base. It’s funny too, since I can not find any references to CF_SQL_NUMBER ever being a valid cfsqltype, but it did work in CF2016, and I suspect earlier.
Newbie
5 posts
Hey Austin,
To follow up on Charlie’s response, starting with 2018, it became a little stricter where invalid params error out and as of 11 you no longer need to pre-pend with “cf_sql_”. Here is a gist of mine that I used with a client to clean up all the possible bad entries in their code. Use it in VS Code or Sublime and make sure to set your search to regex and it will find all possible bad entries.
https://gist.github.com/GiancarloGomez/7c0bb39a7e100378253ca422769211d3
Thanks for the regex/gist, Giancarlo.
That said, when you say “as of 11 you no longer need to pre-pend with “cf_sql_“, it’s not quite clear what your “11” refers to. Might you be saying that CF2018 update “11” removes that prefix requirement again? I hadn’t noticed but also haven’t yet tested.
Or were you merely saying that it was that as of CF11 you noticed that one no longer “needed” to pre-pend? (I never noticed when that was allowed, if in CF10 or before.)
Either way, again your gist is valuable. Thanks. 🙂
Yep, this is indeed something that was noticed back at the launch of CF2018. FWIW, I mentioned it in my “hidden gems in CF2018 talk” back then, but I don’t see that I ever created a blog post about, nor am I readily finding any others. So good for you in posting this here, to help others. (If an Adobe person sees this, it should be clarified in the docs page for cfqueryparam, how this changed in 2018.)
These changes are important for security, in that because they are not valid values, I’m pretty sure it was that in CF2016 and before it was simply IGNORING the types and simply validating as if it was the default of cf_sql_char. And that’s not correct, of course. (At least there was still SOME benefit of using cfqueryparam for other aspects of how it helps with both its primary role of parameterizing queries to the DB and its secondary role of trying to help prevent sql injection.)
And to be clear, it’s not just cf_sql_int for cf_sql_integer but I tracked then how also cf_sql_num should be cf_sql_numeric, and even that CF_SQL_DateTime should be CF_SQL_date, _time, or _timestamp.
I also noted then how just “integer” worked, not requiring “cf_sql_integer”, which was a surprise to me.
I will add that I have not tested things further since back then. If anyone may do more, and would want to offer clarifications/revisions here, I’m sure many would welcome that.
You must be logged in to post a comment.
23 Nov
10AM Pacific
Online
