Testing for MySQL
- 1 Short Description of the Issue (Topic and Explanation)
- 2 Black Box testing and example
- 2.1 How to Test
- 2.2 The single Quotes Problem
- 2.3 Multiple mixed queries:
- 2.4 Information gathering
- 2.5 Attack vectors
- 2.6 Standard SQL Injection Attack
- 2.7 Out of band SQL Injection
- 2.8 Blind SQL Injection
- 3 References
Short Description of the Issue (Topic and Explanation)
SQL Injection vulnerabilities occur whenever input is used in the construction of an SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers. It allows for the execution of SQL code under the privileges of the user used to connect to the database.
MySQL server has a few particularities so that some exploits need to be specially customized for this application. That's the subject of this section.
Black Box testing and example
How to Test
When a SQL Injection is found with MySQL as DBMS backend, there is a number of attacks that could be accomplished depending on MySQL version and user privileges on DBMS.
MySQL comes with at least four versions used in production worldwide. 3.23.x, 4.0.x, 4.1.x and 5.0.x. Every version has a set of features proportional to version number.
- From Version 4.0: UNION
- From Version 4.1: Subqueries
- From Version 5.0: Stored procedures, Stored functions and the view named INFORMATION_SCHEMA
- From Version 5.0.2: Triggers
To be noted that for MySQL versions before 4.0.x, only Boolean or time-based Blind Injection could be used, as no subqueries or UNION statements are implemented.
From now on, it will be supposed there is a classic SQL injection in a request like the one described in the Section on Testing for SQL Injection.
The single Quotes Problem
Before taking advantage of MySQL features, it has to be taken in consideration how strings could be represented in a statement, as often web applications escape single quotes.
MySQL quote escaping is the following:
'A string with \'quotes\''
That is MySQL interprets escaped apostrophes (\') as characters and not as metacharacters.
So if the needs of using constant strings occurs, two cases are to be differentiated:
- Web app escapes single quotes (' => \')
- Web app does not escapes single quotes escaped (' => ')
Under MySQL there is some standard way to bypass the need of single quotes, anyway there is some trick to have a constant string to be declared without the needs of single quotes.
Let's suppose we want know the value of a field named 'password' in a record with a condition like the following: password like 'A%'
- The ascii values in a concatenated hex:
- password LIKE 0x4125
- The char() function:
- password LIKE CHAR(65,37)
Multiple mixed queries:
MySQL library connectors do not support multiple queries separated by ';' so there's no way to inject multiple non homogeneous SQL commands inside a single SQL injection vulnerability like in Microsoft SQL Server.
As an example the following injection will result in an error:
Of course, the first thing to know is if there's MySQL DBMS as a backend.
MySQL server has a feature that is used to let other DBMS to ignore a clause in MySQL dialect. When a comment block ('/**/') contains an exlamation mark ('/*! sql here*/') it is interpreted by MySQL, and is considered as a normal comment block by other DBMS as explained in [MySQL manual].
1 /*! and 1=0 */
If MySQL is present, the clause inside comment block will be interpreted.
There are three ways to gain this information:
- By using the global variable @@version
- By using the function [VERSION()]
- By using comment fingerprinting with a version number /*!40110 and 1=0*/
- which means
if(version >= 4.1.10) add 'and 1=0' to the query.
These are equivalent as the result is the same.
In band injection:
1 AND 1=0 UNION SELECT @@version /*
1 AND @@version like '4.0%'
A string like this: 5.0.22-log
There are two kinds of users MySQL Server relies.
- [USER()]: the user connected to MySQL Server.
- [CURRENT_USER()]: the internal user is executing the query.
There is some difference between 1 and 2.
The main one is that an anonymous user could connect (if allowed) with any name but the MySQL internal user is an empty name ('').
Another difference is that a stored procedure or a stored function are executed as the creator user, if not declared elsewhere. This could be known by using CURRENT_USER.
In band injection:
1 AND 1=0 UNION SELECT USER()
1 AND USER() like 'root%'
A string like this: [email protected]
Database name in use
There is the native function DATABASE()
In band injection:
1 AND 1=0 UNION SELECT DATABASE()
1 AND DATABASE() like 'db%'
A string like this: dbname
From MySQL 5.0 a view named [INFORMATION_SCHEMA] was created. It allows to get all informations about databases, tables and columns as well as procedures and functions.
Here is a summary about some interesting View.
|SCHEMATA||All databases the user has (at least) SELECT_priv|
|SCHEMA_PRIVILEGES||The privileges the user has for each DB|
|TABLES||All tables the user has (at least) SELECT_priv|
|TABLE_PRIVILEGES||The privileges the user has for each table|
|COLUMNS||All columns the user has (at least) SELECT_priv|
|COLUMN_PRIVILEGES||The privileges the user has for each column|
|VIEWS||All columns the user has (at least) SELECT_priv|
|ROUTINES||Procedures and functions (needs EXECUTE_priv)|
|TRIGGERS||Triggers (needs INSERT_priv)|
|USER_PRIVILEGES||Privileges connected User has|
All of these informations could be extracted by using known techniques as described in SQL Injection paragraph.
Write in a File
If connected user has FILE privileges _and_ single quotes are not escaped, it could be used the 'into outfile' clause to export query results in a file.
Select * from table into outfile '/tmp/file'
N.B. there are no ways to bypass single quotes outstanding filename. So if there's some sanitization on single quotes like escape (\') there will be no way to use 'into outfile' clause.
This kind of attack could be used as an out-of-band technique to gain informations about the results of a query or to write a file which could be executed inside the web server directory.
1 limit 1 into outfile '/var/www/root/test.jsp' FIELDS ENCLOSED BY '//' LINES TERMINATED BY '\n<%jsp code here%>';
Results are stored in a file with rw-rw-rw privileges owned by mysql user and group.
Where /var/www/root/test.jsp will contain:
//field values// <%jsp code here%>
Read from a File
Load_file is a native function that can read a file when allowed by filesystem permissions.
If connected user has FILE privileges, it could be used to get files content.
Single quotes escape sanitization can by bypassed by using previously described techniques.
the whole file will be available for exporting by using standard techniques.
Standard SQL Injection Attack
In a standard SQL injection you can have results displayed directly in a page as normal output or as a MySQL error. By using already mentioned SQL Injection attacks and the already described MySQL features, direct SQL injection could be easily accomplished at a level depth depending primarily on mysql version the pentester is facing.
A good attack is to know the results by forcing a function/procedure or the server itself to throw an error. A list of errors thrown by MySQL and in particular native functions could be found on [MySQL Manual].
Out of band SQL Injection
Out of band injection could be accomplished by using the 'into outfile' clause.
Blind SQL Injection
For blind SQL injection there is a set of useful function natively provided by MySQL server.
- String Length:
- Extract a substring from a given string:
- SUBSTRING(string, offset, #chars_returned)
- Time based Blind Injection: BENCHMARK and SLEEP
- BENCHMARK(#ofcicles,action_to_be_performed )
- Benchmark function could be used to perform timing attacks when blind injection by boolean values does not yeld any results.
- See. SLEEP() (MySQL > 5.0.x) for an alternative on benchmark.
For a complete list the reader could refer to MySQL manual - http://dev.mysql.com/doc/refman/5.0/en/functions.html
- Chris Anley: "Hackproofing MySQL" -http://www.nextgenss.com/papers/HackproofingMySQL.pdf
- Time Based SQL Injection Explained - http://www.f-g.it/papers/blind-zk.txt
- Francois Larouche: Multiple DBMS SQL Injection tool - http://www.sqlpowerinjector.com/index.htm
- ilo--: MySQL Blind Injection Bruteforcing, Reversing.org - http://www.reversing.org/node/view/11 sqlbftools
- Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net
- Antonio Parata: Dump Files by SQL inference on Mysql - http://www.ictsc.it/site/IT/projects/sqlDumper/sqldumper.src.tar.gz
OWASP Testing Guide v2
Here is the OWASP Testing Guide v2 Table of Contents