Performance Schema in version 5.6 helps to prevent SQL Injection attacks
By Sveta Smirnova-Oracle on Jul 13, 2012
There were few prominent SQL Injection attacks lately.
Such breakages could be possible when an attacker finds that user data, sent to a SQL server, not properly sanitized.
Usually successful attack is preceded by queries which cause parse errors.
mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/er'ror;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ror' at line 1
Easiest test to find out if website can be attacked is to send, using web form or address bar, a value which contains special symbol, say "'", like in the word "D'Artagnan", then analyse if it was properly handled.
There are practices which web developer should use to avoid such situations. They include 100% sanitizing of all incoming data, allowing only expected values and rejecting any other or limitation of database accounts which web site uses. In latter case even if SQL injection happens, damage, which it can make, would be minimal.
But all these means work at the design stage. What to do if you are responsible for large legacy project, so you can not be 100% sure everything is handled properly?
In old time we could only suggest to log queries into general query log, then search for those which look suspicious. This hard job can be hardly automated: general query log does not contain information about query return code, therefore you have to guess which query could cause parse error yourself and not much automation can be applied.
In version 5.6 MySQL introduced new tables in Performance Schema database.
Performance schema in 5.6 became really user friendly and I am falling in love with it.
A bunch of them, events_statements_*, can be useful for debugging possible SQL injection attacks.
event_statements_* tables keep queries, which were executed in past, together with information about how they were proceeded. It includes:
- time, spent waiting for table locks
- how many temporary tables were created, splitting disk and memory-based tables
- which optimizer techniques were chosen
- statistics based on them.
However, for SQL injection discussion, we are interested in diagnostic information, such as return SQLSTATE code, warning and error information.
These fields can give the list of queries which were used by attackers to predict their chances to break the website.
Performance schema has two special error-handling instruments: statement/com/Error for statements which were parsed successfully, but rejected thereafter and statement/sql/error for statements which were rejected at parsing state.
In case of SQL Injection we should examine those statements, which were rejected at parsing state: statement/sql/error. They can easily found with help of this query:
In this example I specially made errors in queries I typed, but the idea can be derived. Usually application does not produce such queries at all: they all should be fixed at the development stage, therefore periodically checking for such queries can be good practice for determining possibility of SQL injection attacks.
And to finish this topic I should mention another tool which can help to prevent SQL injection attacks even before they reach real database. This is Oracle Database Firewall. I'll copy part of its white paper here:
Oracle Database Firewall monitors data access, enforces access policies, highlights anomalies and helps protect against network based attacks originating from outside or inside the organization. Attacks based on SQL injection can be blocked by comparing SQL against the approved white list of application SQL. Oracle Database Firewall is unique and offers organizations a first line of defense, protecting databases from threats and helping meet regulatory compliance requirement.
With its unique feature of blocking access by analyzing all incoming SQL statements your database can be really protected. And amazingly it supports MySQL out of the box.
MySQL user manual - http://dev.mysql.com/doc/refman/5.6/en/performance-schema-statements-tables.html
Mark Leith's blog - http://www.markleith.co.uk/ps_helper/#statements_with_errors_or_warnings
Oracle Database Firewall - http://www.oracle.com/technetwork/products/database-firewall/overview/index.html