Logo

github Download
"patterns" filters

patterns filters #

patterns filters match the queries by a template consisting of literal query text with variable parts represented as patterns.

AcraCensor parses the incoming query and compares its structure to the provided template. That is, the query must have matching type (SELECT, INSERT, UPDATE, etc.), the internal structure must match too (WHERE, JOIN, ORDER BY, UNION clauses, etc.), as well as individual expressions, literal values, column names and tables used in the query. If the query matches all the patterns, the handles applies the decision: whether to allow the query, or deny it.

Supported patterns #

AcraCensor is still work in progress, the list of supported patterns here might be incomplete. See example configuration files used in integration tests and unit tests for the latest supported features.

If the query type you need is not supported yet, consider using queries filters. Please file an issue if you believe patterns filters are missing an important query type.

VALUE pattern #

%%VALUE%% represents a literal value (string, binary, number, boolean). It is supported in many places where literal values can be used:

  • WHERE, ORDER BY, GROUP BY clauses
  • IN, BETWEEN expressions

WHERE ... = VALUE clause #

patternmatching queriesnon-matching queries
SELECT a FROM t WHERE ID = %%VALUE%%SELECT a FROM t WHERE ID = '123';SELECT a, b FROM t WHERE ID = '123';
SELECT a FROM t WHERE ID = 35SELECT a FROM anothertable WHERE ID = '123;
SELECT a FROM t WHERE ID > '123

BETWEEN VALUE AND VALUE expression #

patternmatching queriesnon-matching queries
SELECT a FROM t WHERE param BETWEEN %%VALUE%% and %%VALUE%%SELECT a FROM t WHERE param BETWEEN 1 and 3;SELECT a, b FROM t WHERE param BETWEEN 1 and 3;
SELECT a FROM t WHERE param BETWEEN "qwerty" and NULLSELECT a FROM anothertable WHERE param BETWEEN 1 and 3

IN (VALUE, VALUE, ...) expression #

patternmatching queriesnon-matching queries
SELECT 1 FROM t WHERE b='qwe' and v IN (%%VALUE%%, %%VALUE%%, %%VALUE%%)SELECT 1 FROM t WHERE b='qwe' and v IN (1, 2, 3)SELECT 1 FROM t WHERE b='qwe' and v IN (1, 2);
SELECT 1 FROM t WHERE b='qwe' and v IN (1, 2, 3, 4, 5)

LIST_OF_VALUES pattern #

%%LIST_OF_VALUES%% matches a list of %%VALUE%% patterns of arbitrary length, for example in IN expressions.

patternmatching queriesnon-matching queries
SELECT 1 FROM t WHERE b='qwe' and v IN (%%LIST_OF_VALUES%%)SELECT 1 FROM t WHERE b='qwe' and v IN (1, 2, 3);SELECT 1 FROM anothertable WHERE b='qwe' and v IN (1, 2)
SELECT 1 FROM t WHERE b='qwe' and v IN (1, 'qwe', True, NULL, FALSE)

SUBQUERY pattern #

%%SUBQUERY%% matches a subquery expression (SELECT ...) which is used as a value in the parent query.

patternmatching queriesnon-matching queries
SELECT 1 FROM t WHERE a=%%SUBQUERY%%SELECT 1 FROM t WHERE a=(SELECT column1, column2 FROM table1 WHERE a=1);SELECT 1 FROM t WHERE a=2;
SELECT 1 FROM t WHERE a=(SELECT column1, column2 FROM table1 WHERE a=1 union SELECT column1, column2 FROM table2)SELECT 1 FROM anothertable WHERE a=(SELECT column1, column2 FROM table1 WHERE a=1)

WHERE pattern #

%%WHERE%% matches a WHERE clause with one or more expressions. This pattern is supported for SELECT, UPDATE, and DELETE queries.

patternmatching queriesnon-matching queries
SELECT users FROM company %%WHERE%%SELECT users FROM company WHERE title = 'engineer';SELECT users, cats FROM company WHERE a = 'someValue';
SELECT users FROM company WHERE AGE IN ( 25, 27 );SELECT age FROM company4 WHERE age IS NULL;
SELECT users FROM company WHERE NAME LIKE 'Pa%';SELECT users FROM company4 INNER JOIN (SELECT age FROM company WHERE id = 1) AS t ON t.id=another_table.id WHERE AGE NOT IN (25, 27)
SELECT users FROM company WHERE A=(SELECT age FROM company WHERE salary > 65000 limit 1) and B=(SELECT age FROM company123 WHERE salary > 1000 limit 1)

COLUMN pattern #

%%COLUMN%% matches a column name expression, such as in SELECT and ORDER BY clauses.

  • SELECT COLUMN

    patternmatching queriesnon-matching queries
    SELECT %%COLUMN%%, %%COLUMN%% FROM companySELECT users, cats FROM company;SELECT users FROM company
    SELECT a, b FROM t WHERE ID = '123'SELECT users, cats, chameleons FROM company
    SELECT users, cats FROM zoo
  • ORDER BY COLUMN

    patternmatching queriesnon-matching queries
    SELECT 1 FROM t ORDER BY %%COLUMN%%SELECT 1 FROM t ORDER BY age;SELECT 1 FROM anothertable ORDER BY age;
    SELECT 1 FROM t ORDER BY (case when f1 then 1 when f1 is null then 2 else 3 end)SELECT 1 FROM anothertable ORDER BY age DESC

SELECT pattern #

%%SELECT%% matches any SELECT query.

patternmatching queriesnon-matching queries
%%SELECT%%SELECT users, cats FROM company;any non-SELECT query: UPDATE users SET name='new_name'
SELECT SUM(Salary) FROM Employee WHERE Emp_Age < 30
SELECT dogs, chameleons FROM company;

INSERT pattern #

%%INSERT%% matches any INSERT query.

patternmatching queriesnon-matching queries
%%INSERT%%INSERT INTO Customers (CustomerName, ContactName) VALUES ('Cardinal', 'Tom B. Erichsen');any non-INSERT query: SELECT email FROM users;
INSERT INTO dbo.Points (PointValue) VALUES ('1,99');

UPDATE pattern #

%%UPDATE%% matches any UPDATE query.

patternmatching queriesnon-matching queries
%%UPDATE%%UPDATE t SET a=1 WHERE ID = 1any non-UPDATE query: SELECT email FROM users;
UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1

DELETE pattern #

%%DELETE%% matches any DELETE query.

patternmatching queriesnon-matching queries
%%DELETE%%DELETE FROM t WHERE removed = TRUEany non-DELETE query: SELECT email FROM users;
DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste'

BEGIN pattern #

%%BEGIN%% matches any BEGIN query.

patternmatching queriesnon-matching queries
%%BEGIN%%BEGINany non-BEGIN query: SELECT email FROM users;

COMMIT pattern #

%%COMMIT%% matches any COMMIT query.

patternmatching queriesnon-matching queries
%%COMMIT%%COMMITany non-COMMIT query: SELECT email FROM users;

ROLLBACK pattern #

%%ROLLBACK%% matches any ROLLBACK query.

patternmatching queriesnon-matching queries
%%ROLLBACK%%ROLLBACKany non-ROLLBACK query: SELECT email FROM users;

This is a constantly updating list of useful configuration examples. See the examples we are using for integration testing in tests/acra-censor_configs and AcraCensor’s unit tests. We will also appreciate your questions and Pull Requests.