DB2 query filter

A query filter is invoked when a query is sent to the database server. This can include prepared statements when they are initially sent to the database.

Parameters

Query patterns

The SQLs for which this filter should be invoked. This can be one or more strings or regular expressions.

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

Include prepared statements

If checked, this filter will be invoked when a prepared statement is initially sent to the database.

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_.+

Example

You can catch certain queries and modify them. For instance, let's say we want to change the query:

SELECT NAME, ADDRESS FROM CUST.CUSTOMERS WHERE <condition>

to:

SELECT NAME, '<address withheld>' FROM CUST.CUSTOMERS WHERE <condition>

We can easily do so by creating a query filter with the parameter:

Query patterns:  regex:SELECT NAME\, ADDRESS FROM CUST\.CUSTOMERS WHERE .*

and the code:

const sql = context.packet.sql;

const sqlWhere = sql.substring(47);

context.packet.sql = "SELECT NAME, '<address withheld>' FROM CUST.CUSTOMER WHERE " + sqlWhere;

log.info("Rewriting query from: \n" + sql + "\nto: \n" + context.packet.sql);

Example

You can reject certain queries with a query filter with the parameter:

Query patterns: regex:SELECT \* FROM MYSCHEMA\..*

and the code:

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

context.result.sqlStatus = 99999;

context.result.addErrorParameter("Logic has rejected this request");

For more details, see Rejecting a request.