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.

References:

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

Comments:

Hi, i will use for mysql mysqlmymonlite.sh but i dont no working or not working? dont help any body about this.

Posted by Arama Motoru on November 04, 2012 at 01:42 PM MSK #

I am not appropriate person to ask about mysqlmymonlite.sh This looks like cPanel tool, so you should ask on cPanel phorums.

Posted by Sveta Smirnova on November 09, 2012 at 03:30 PM MSK #

Hi Sveta, thank you for information but this is not just for Cpanel, i will install WHM Cpanel but have many OS this is i think for Vbulletin forum software, for good and stabil work i think. But i m not sure. Just i will ask from here to ORACLE, why dont have very good and stable work information.

May like this:
I have 2 Hybird Xenon CPU
64 GB Ram
5.TB HDD
10 GB Cornetion
200 Mbps internet and many options.

This information my server pleqse tell şe zhqt configuration good for me?
for my.conf

Posted by Vizgeliyoo on November 10, 2012 at 01:23 AM MSK #

As I said I don't use mysqlmymonlite.sh and don't know how it works, which configuration it needs, etc.

Regarding to configuration file this depends not only from your hardware, but from your load also. This is most likely support or consulting issue and not good question for the blog post originally written about completely different topic.

Posted by Sveta Smirnova on November 10, 2012 at 02:08 AM MSK #

Post a Comment:
  • HTML Syntax: NOT allowed
About

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

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today