DB2 prepared statement filter

A prepared statement filter is invoked when a prepared statement is executed. The filter has full access to the parameters (if any) and can change them, or reject the execution.

The parameters are available to the JavaScript code as an array in the variable context.packet.values.

Parameters

SQL patterns

The SQL(s) for which this filter should be invoked. This can be one or more strings or regular expressions.

If this is left blank, then all prepared statement executions will cause execution of the filter (unless other parameters prevent it).

Parameter patterns

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

For instance:

2=Federated States of Micronesia

4=99.A

would restrict this filter to execute only when the second parameter has a value of "Federated States of Micronesia", and the fourth parameter has a value of "99", followed by any one character, followed by "A". The values are converted to strings for comparison, so this would work whether the fourth parameter is a string or a number.

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 queries executed by these users will cause execution of the filter.

Example:

jdoe

jdoe,asmith,wanderson

regex:us_.+

Examples

You could intercept the execution of a specific prepared statement execution only when certain parameters have certain values, and modify these values:

SQL patterns: INSERT INTO CUST.CUSTOMERS (ID, NAME, ADDRESS, COUNTRY) VALUES (?,?,?,?)

Parameter patterns: 4=(DE)|(IT)|(AT)

and the code:

log.info("Changing customer insert");

switch(context.packet.values[3]) {

    case 'DE': context.packet.values[3] = "Germany"; break;

    case 'IT': context.packet.values[3] = "Italy"; break;

    case 'AT': context.packet.values[3] = "Austria"; break;

}

You could reject a prepared statement execution with:

Query patterns: SELECT ID, NAME, ADDRESS FROM CUST.CUSTOMER WHERE COUNTRY=?

Parameter patterns: 1=(DE)|(IT)|(AT)

and the code:

log.info("Rejecting prep stmt execution: " + context.packet.sql);
context.result.errorCode = -438;

context.result.sqlStatus = 99999;

context.result.addErrorParameter("You cannot get the address for these customers");

For more details, see Rejecting a request.