sorted by: recent | see : popular
Content Tagged with PROJECTS + Security

MySQL-Proxy learning to block SQL-Injection

Friday, August 15, 2008

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.

MySQL: Planet MySQL

Security PHP MySQL Projects

Mysql-Proxy Heuristic SQL Injection Detection

Thursday, August 07, 2008

?MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.?

The flexibility of MySQL Proxy is based on the fact that every aspect is scriptable with Lua. Because I am new to MySQL Proxy and the Lua language I tried to implement a very simple script that waits for incoming SQL queries, tokenizes them and tries to detect SQL Injection heuristically by searching for certain disallowed SQL functions, databases, tables, statements or comments. When an SQL query is believed to contain an SQL injection is it not executed and a “Possible SQL injection” error is returned.

You can grab the detect_sql_injection.lua script at

http://www.suspekt.org/downloads/detect_sql_injection.lua.gz

If you are interested in this and german speaking you might also be interested in next week’s MySQL webinar “Bau sicherer LAMP Anwendungen” where I will not only discuss this little Lua script but also another one that implements SQL injection detection by query structure learning.

MySQL: Planet MySQL

Security SQL injection PHP MySQL Projects webinar