Friday Jul 13, 2012

Performance Schema in version 5.6 helps to prevent SQL Injection attacks

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:

mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/error';


| thread_id | sql_text                                                                            |


|        16 | select * from events_statements_history_long wher event_name='statement/sql/error'  |

|        16 | select * from events_statements_history_long here event_name='statement/sql/error'  |

|        16 | select * from events_statements_history_long where event_name='statement/sql/er'ror |


3 rows in set (0.00 sec)

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 -
Mark Leith's blog -
Oracle Database Firewall -

Wednesday Jul 04, 2012

Warning and error information in stored procedures revisited

Originally way to handle warnings and errors in MySQL stored routine was designed as follows:

  • if warning was generated during stored routine execution which has a handler for such a warning/error, MySQL remembered the handler, ignored the warning and continued execution

  • after routine is executed MySQL checked if there is a remembered handler and activated if any

This logic was not ideal and causes several problems, particularly:

  • it was not possible to choose right handler for an instruction which generated several warnings or errors, because only first one was chosen

  • handling conditions in current scope messed with conditions in different

  • there were no generated warning/errors in Diagnostic Area that is against SQL Standard.

First try to fix this was done in version 5.5. Patch left Diagnostic Area intact after stored routine execution, but cleared it in the beginning of each statement which can generate warnings or to work with tables. Diagnostic Area checked after stored routine execution.

This patch solved issue with order of condition handlers, but lead to new issues. Most popular was that outer stored routine could see warnings which should be already handled by handler inside inner stored routine, although latest has handler. I even had to wrote a blog post about it.

And now I am happy to announce this behaviour changed third time.

Since version 5.6 Diagnostic Area cleared after instruction leaves its handler.

This lead to that only one handler will see condition it is supposed to proceed and in proper order. All past problems are solved.

I am happy that my old blog post describing weird behaviour in version 5.5 is not true any more.


Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group


« July 2012 »