I previously reported about my joy with MySQL-Proxy and a simple SQL-Injection detection based on a simple heuristic.
Today I present the more interesting approach that I promised to publish after my webinar yesterday. This approach is based on the idea that SQL queries issued by an application always have a certain structure. This structure can be learned and remembered by MySQL-Proxy. Any SQL query that has a different structure can then be considered an attack.
Training Mode
The first Lua script learn_sql_queries.lua uses MySQL-Proxy’s read_query hook to catch COM_INIT_DB and COM_QUERY packets. COM_INIT_DB packets are issued when the database is changed and COM_QUERY packets contain normal queries.
When a change of database is detected a CREATE TABLE is injected into the communication to create a table called ‘allowed_queries’ in the newly selected database. This table consist of only on column called ‘query’. Within this column normalized queries are collected.
When a normal query is received it is first tokenized by MySQL-Proxy’s tokenizer. The tokens are then used to recreate a normalized version of the query where all data values are replaced by the ‘?’ placeholder. Additionally IN ( ?, ?, ?, …) statements are compressed to IN ( ? ) to allow arbitrary length IN value lists without having to learn all possibilities. The normalized query is then learned by inserting it into the table.
When all queries have been learned (maybe during development) the blocking mode can be started.
Blocking Mode
The second Lua script block_unknown_queries.lua also uses MySQL-Proxy’s read_query hook to catch COM_INIT_DB and COM_QUERY packets.
When a change of database is detected a SELECT statement is injected into the communication that loads the table ‘allowed_queries’ into a Lua-Table. The queries become the indices so that they can be found fast.
When a normal query is received it is first tokenized and normalized. The normalized Query is then searched in the Lua-Table which is just a key lookup. If the query is found in the table it is one of the known query structures that are allowed. The query is then executed as normally.
If the query is not found in the table it is either a query that was not learned by mistake or it is an SQL-Injection attack. The query is not executed and a database error “Possible SQL Injection” is returned.
Both proof of concept examples are released as GPL. Therefore feel free to modify them for your needs. You might prefer to just log SQL-Injection attempts instead of blocking them.