Prepared statement filter - MSSQL

Parameters

None of the parameters are required.  If no parameter is specified, this filter will be invoked for every call to a prepared statement, which would be unusual but not unheard of.

The parameters that can take multiple values can be separated by commas or by newlines.

SQL patterns

The SQL(s) or regular expression(s) determining for which prepared statement calls to invoke this filter. They can be separated by commas or newlines.

Examples:

select id, name from customers where name like ?

regex:select id\, name from \w+\.customers where name like \?

Parameter patterns

Zero or more name=regular expression entries specifying for which values of which parameters this filter should get invoked. In addition to all the usual regular expression syntax, if a regular expression contains equal signs, they must be escaped with a backslash (e.g. \=).

Client IPs

A list of IP addresses (IP4 and/or IP6) and/or regular expressions for IP addresses. If specified, only requests from matching IP addresses will cause execution of this filter.

Example: 

12.34.56.78
1234:5678:90ab::01
regex:98\.76\..*
regex:9876:5432:.*

Users

A list of user names and/or regular expressions for user names. If specified, only calls from these users will cause execution of the filter.

Note

This filter can be invoked for either the Sp_PrepExec or Sp_Execute stored procedure, and the order of parameters is different between the two.

For Sp_PrepExec, the first three parameters are the statement number (output only), the SQL, and the parameter definition.  The values for the prepared statement parameter start after that.

For Sp_Execute, the first parameter is the statement number, and the values for the prepared statement parameter start after that.

This difference is handled automatically when specifying the parameters (see above), i.e. the parameter patterns are normalized to start at 1. But if you want to change the value of a parameter, you will need to take this difference into account (see the example).

Example

We can change the value of a parameter to the prepared statement:

select id, name from gallium_demo.customers where id = ? or name = ?

with parameter pattern:

2=Jane

with:

let pkt = context.packet;

if (pkt.procName.toLowerCase() === "sp_prepexec") {

    pkt.parameters[4].value = "Eric";

}

else {

    pkt.parameters[2].value = "Eric";

}