Result set filter - MSSQL

A result set filter is invoked (potentially) for every row in a result set. The filter can modify the row, add more rows, drop the row, or return an error. This filter works for all result sets, whether they result from SQL statements or from the execution of a stored procedure.

Parameters

All parameters are optional. If no parameter is specified, then the filter will be invoked for every row of every result set.

Query pattern

A series of strings and/or regular expressions, separated by commas or newlines. If the current SQL does not match any of these, the filter will not be invoked. The current SQL is the last SQL command executed, either directly or via the sp_executesql, sp_prepexec or sp_prepexecrpc stored procedures.

Column patterns

A series of name=value pairs, separated by commas or newlines. The value is a regular expression, which means special characters like period must be escaped.

And/or

If more than one column pattern is specified, this determines whether they must all be true (and) or any one of them (or).

Action

Can be either code or hide. If it's code, then the code is executed. If it's hide, then the row is dropped from the result set, and the code is not executed.

Client IPs

A comma-separated (or line break separated) list of IP4 or IP6 addresses or regular expressions for IP addresses. If specified, only responses to requests from matching IP addresses will cause execution of this filter.

Users

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

Examples

Assuming that the filter is set for a query that returns a column named first_name, we can change its value to its initial with:

context.packet.first_name = context.packet.first_name.substring(0,1) + ".";

If we want to hide certain rows (this could also be done without code with parameter Action set to hide):

if (context.packet.country === 'DK') {
    context.packet.remove();
}

We can add new rows to the result set by cloning the current row and modifying it:

if (context.packet.id === 2) {
    for (var i = 0; i < 1000; i++) {
        let newRow = context.packet.clone();
        newRow.id = -1 - i;
        newRow.first_name = 'Synthetic ' + i;
        context.packets.addPacket(newRow);
    }
}

Example with data classification

Assuming that the database contains some data classification:

if (context.packet.columnHasSensitivity("first_name", "Confidential")) {
    context.packet.first_name = "<?>";
}

This will replace the contents of the first_name column with <?> if that column has a data classification sensitivity of Confidential.