Collecting values of cfqueryparams before executing cfquery?

Is there a way to collect the values of all cfqueryparams in a query before executing the query?

Background info
We want to use MS SQL Server Audit to log queries, but it only logs the query text itself and not the values of the parameters passed via cfqueryparam.
So we’re trying to log the values of the query parameter in our Lucee application and see if we can match them with the queries logged by SQL-server.

I’m looking for alternatives.

What I found so far

  • One can fill the params attribute of the cfquery tag with an array or a struct with values, thus one would know all the values before execution.
    This wouldn’t work for me, because it require me to manually rewrite thousands of queries, replacing all the cfqueryparam tags in our huge Lucee application.

  • One can specify a variable for the result attribute of the cfquery tag, in which the parameter values are returned after executing the query.
    This also isn’t very pleasant for me. Would also require manually rewriting thousands of queries. I would also need to find a way to retroactively match the captured parameter values with the query.

  • We can also log queries with MS SQL Extended Events. This does log the values of the query parameter as well, but this form of logging apparently has more overhead than SQL Server Audit.

Any thoughts, anyone?

try out the query listeners

3 Likes

Wow! This looks really promising.

Thank you.