Prepared statement execution filter - MySQL

The prepared statement execution filter is called when an already-prepared statement is being invoked, with values for the parameters defined in the prepared statement.

The execution of a prepared statement normally follows the following flow:

1. client sends a StmtPrepare packet containing the SQL for the statement

2. server responds with a StmtPrepareOK packet containing information about the statement: the ID of the prepared statement, how many parameters it contains, what columns to expect when executing it, etc...

3. client sends a StmtExecute packet providing values for the parameters in the prepared statement

4. server responds with a StmtExecuteResponse packet starting the result set

5. client can then send a StatementReset packet to reset the prepared statement's parameters

6. client eventually sends a StatementClose packet to tell the server to dispose of the prepared statement

Keep in mind that the same prepared statement can be executed many times

Parameters

• Query patterns : optional. If specified, a comma-separated (or line break separated) list of strings or regular expressions (prefixed with regex:) specifying for which prepared statements this filter should be invoked. If not specified, this filter will be invoked for all executions of all prepared statements.

Examples:

select * from customers

regex:select.*from \w+\.customers where.*cust_id=\?.*

• Parameter patterns: optional. If specified, a comma-separated (or line break separated) list of strings or regular expressions (prefixed with regex:) specifying for which parameter values this filter should be invoked. If not specified, this filter will be invoked for all executions.

Each value must be specified as an zero-based index, followed by the equal sign, followed by the value as a string.

Examples:

0=23

3=Jones

17=regex:(CA|MI|NY|TX)

• Client IPs: optional. If specified, a comma-separated (or line break separated) list of IP4 or IP6 addresses or regular expressions for IP addresses.

Examples:

12.34.56.78, 98.76.54.32

99.88.77.66, 2200:1602:6170:69f0:baa3:8227:c139:d9fc

regex:178\.45\.\d+\.\d+, regex:202\.45\.223\.\d+

• Users: optional. If specified, a comma-separated (or line break separated) list of user names or regular expressions for user names.

Examples:

jdoe,arodriguez,mkhan

regex:fin_\w+,regex:hr_\w+

Context

The following variables will always be defined when your JavaScript executes:

• log : the log object

• context : contains all the variables you'll commonly use:

  • packet : the packet being filtered -- this will always be a StmtExecute packet.

  • result : the result object, used to cancel the current packet if desired

  • filterContext: an object containing variables attached to this filter. Any changes to this object will be visible to all invocations of this filter.

  • connectionContext: an object containing variables attached to the connection. Any changes to this object will be visible to all filters defined on this connection.