Query Filter modules allow the SQL Relay server programs to filter out queries, and not pass them along to the database.
The filters section of the sqlrelay.conf file indicates which filter modules to load and what parameters to use when executing them.
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<filters>
<filter module="regex" pattern=" [0-9]*=[0-9]*" errornumbrer="100" error="regex filter violation"/>
</filters>
...
</instance>
...
</instances>
The module attribute specifies which module to load.
Module configurations may have attributes and/or nested tags. How these elements are interpreted is module-specific.
Currently, all filter modules have an enabled parameter, allowing the module to be temporarily disabled. If enabled="no" is configured, then the module is disabled. If set to any other value, or omitted, then the module is enabled.
Filter modules can be "stacked". Multiple modules may be loaded and multiple instances of the same type of module, with different configurations, may also be loaded.
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<filters>
<filter module="regex" pattern=" [0-9]*=[0-9]*"/>
<filter module="regex" pattern="^(create)"/>
<filter module="regex" pattern="^(drop)"/>
<filter module="string" pattern="hugetable" ignorecase="yes"/>
<filter module="string" pattern="badschema" ignorecase="yes"/>
</filters>
...
</instance>
...
</instances>
At startup, the SQL Relay server creates instances of the specified filter modules and initializes them. When a query is run, the server passes the query to each module, in the order that they were specified in the config file. If a module filters out the query, then it isn't passed along to the next module, nor is it sent to the database, and the client program is told that the query failed.
When using query filters, it is helpful to use the normalize query translation too:
@sqlrelay-filter-regex-normalize.conf.html@
See Query Translations for more information.
Currently, the following filter modules are available:
The patterns module matches the query against a specified set of patterns. Each pattern may be a string, case-insensitive string, or regular expression. Each pattern may also be matched against the entire query, only the parts of the query that are outside of quotes, or only the parts of the query that are contained within quotes. If the query matches, then it is filtered out.
The list of patterns is given by a set of pattern child tags. Each pattern tag may contain the following attributes.
For example, with the following configuration...
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<filters>
<filter module="patterns">
<pattern pattern="^(drop|create)" type="regex"/>
<pattern pattern="hugetable" type="cistring" scope="outsidequotes"/>
<pattern pattern="badstring" scope="insidequotes" errornumbrer="100" error="pattern filter violation"/>
</filter>
</filters>
...
</instance>
...
</instances>
These queries would be filtered out:
drop table mytable
create table mytable (col1 int)
select * from HugeTable
select * from badstringtable where col1='badstring'
But these queries would not be:
insert into mytable values (1)
select * from goodtable
select * from badstringtable where col1='goodstring'
The regex module matches the query against a specified regular expression pattern. If the query matches, then it is filtered out. This module is useful if you need to do a quick match, without the complexity of the patterns module.
In addition to the module attribute, each filter tag may contain the following attributes.
For example, with the following configuration:
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<filters>
<filter module="regex" pattern=" [0-9]*=[0-9]*" errornumbrer="100" error="regex filter violation"/>
</filters>
...
</instance>
...
</instances>
This query would be filtered out:
select * from mytable where column1=1 and 1=1
But this query would not be:
select * from mytable where column1=1
The string module matches the query against a specified string pattern. If the query matches, then it is filtered out. This module is useful if you need to do a quick match without the complexity of regular expressions or of the patterns module.
In addition to the module attribute, each filter tag may contain the following attributes.
For example, with the following configuration:
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<filters>
<filter module="string" pattern="hugetable" ignorecase="yes" errornumbrer="100" error="string filter violation"/>
</filters>
...
</instance>
...
</instances>
This query would be filtered out:
select * from hugetable
But this query would not be:
select * from goodtable where column1=1
Custom modules may also be developed.
For more information, please contact David Muse at david.muse@firstworks.com.