Difference between revisions of "Testing for MySQL"

From OWASP
Jump to: navigation, search
(References)
m (Updated URL for Hackprofing sql, Update sqlmap URL, removed Antonio Parata article since it can not be found)
 
(43 intermediate revisions by 8 users not shown)
Line 1: Line 1:
[[http://www.owasp.org/index.php/Web_Application_Penetration_Testing_AoC Up]]<br>
+
{{Template:OWASP Testing Guide 4}}
{{Template:OWASP Testing Guide v2}}
+
  
== Short Description of the Issue (Topic and Explanation) ==  
+
== Short Description of the Issue ==  
[[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.
+
[[SQL Injection]] vulnerabilities occur whenever input is used in the construction of a 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  
 
''MySQL server'' has a few particularities so that some exploits need to be  
Line 11: Line 10:
  
 
=== How to Test ===
 
=== How to Test ===
When a SQL Injection is found with MySQL as DBMS backend,
+
When an SQL injection vulnerability is found in an application backed by a MySQL database,
there is a number of attacks that could be accomplished depending  
+
there are a number of attacks that could be performed depending  
on MySQL version and user privileges on DBMS.
+
on the MySQL version and user privileges on DBMS.
  
MySQL comes with at least four versions used in production worldwide.
+
MySQL comes with at least four versions which are used in production worldwide.
 
3.23.x, 4.0.x, 4.1.x and 5.0.x.
 
3.23.x, 4.0.x, 4.1.x and 5.0.x.
 
Every version has a set of features proportional to version number.
 
Every version has a set of features proportional to version number.
Line 24: Line 23:
 
* From Version 5.0.2: Triggers  
 
* 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.
+
It should be noted that for MySQL versions before 4.0.x, only Boolean or time-based Blind Injection attacks could be used, since the subquery functionality or UNION statements were not 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]].
+
From now on, we will assume that there is a classic SQL injection vulnerability, which can be triggered by a request similar to the the one described in the Section on [[Testing for SQL Injection  (OWASP-DV-005)|Testing for SQL Injection]].
  
 
  <nowiki>http://www.example.com/page.php?id=2</nowiki>
 
  <nowiki>http://www.example.com/page.php?id=2</nowiki>
  
=== The single Quotes Problem ===
+
=== The Single Quotes Problem ===
 
Before taking advantage of MySQL features,  
 
Before taking advantage of MySQL features,  
 
it has to be taken in consideration how strings could be represented
 
it has to be taken in consideration how strings could be represented
Line 38: Line 37:
 
''' <nowiki>'A string with \'quotes\''</nowiki> '''
 
''' <nowiki>'A string with \'quotes\''</nowiki> '''
  
That is MySQL interprets escaped apostrophes (\') as characters and not as
+
That is, MySQL interprets escaped apostrophes (\') as characters and not as
 
metacharacters.
 
metacharacters.
  
So if the needs of using constant strings occurs,
+
So if the application, to work properly, needs to use constant strings,
 
two cases are to be differentiated:  
 
two cases are to be differentiated:  
 
# Web app escapes single quotes (' => \')
 
# Web app escapes single quotes (' => \')
# Web app does not escapes  single quotes escaped (' => ')
+
# Web app does not escape single quotes (' => ')
  
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.
+
Under MySQL, there is a standard way to bypass the need of single quotes, having a constant string to be declared without the need for single quotes.
  
Let's suppose we want know the value of a field named 'password' in a record
+
Let's suppose we want to know the value of a field named 'password' in a record,
 
with a condition like the following:
 
with a condition like the following:
 
password like 'A%'
 
password like 'A%'
  
# The ascii values in a concatenated hex:<br>
+
# The ASCII values in a concatenated hex:<br>
 
#: password LIKE 0x4125
 
#: password LIKE 0x4125
 
# The char() function:
 
# The char() function:
Line 60: Line 59:
  
 
MySQL library connectors do not support multiple queries separated
 
MySQL library connectors do not support multiple queries separated
by '''<nowiki>';'</nowiki>''' so there's no way to inject multiple non homogeneous SQL commands inside a single SQL injection vulnerability like in Microsoft SQL Server.
+
by '''<nowiki>';'</nowiki>''' 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:
+
For example the following injection will result in an error:
  
 
  1 ; update tablename set code='javascript code' where 1 --
 
  1 ; update tablename set code='javascript code' where 1 --
Line 72: Line 71:
 
Of course, the first thing to know is if there's MySQL DBMS as a backend.
 
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
+
MySQL server has a feature that is used to let other DBMS 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
+
dialect. When a comment block ''('/**/')'' contains an exclamation mark ''('/*! sql here*/')'' it is interpreted by MySQL, and is considered as a normal comment block by other DBMS
as explained in [[http://dev.mysql.com/doc/refman/5.0/en/comments.html MySQL manual]].
+
as explained in [http://dev.mysql.com/doc/refman/5.0/en/comments.html MySQL manual].
  
E.g.:
+
Example:
 
  1 /*! and 1=0 */
 
  1 /*! and 1=0 */
  
 
'''Result Expected:'''<br>
 
'''Result Expected:'''<br>
''If MySQL is present, the clause inside comment block will be interpreted.''
+
''If MySQL is present, the clause inside the comment block will be interpreted.''
  
 
==== Version ====
 
==== Version ====
Line 107: Line 106:
 
==== Login User ====
 
==== Login User ====
  
There are two kinds of users MySQL Server relies.
+
There are two kinds of users MySQL Server relies upon.
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html USER()]]: the user connected to MySQL Server.
+
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html USER()]]: the user connected to the MySQL Server.
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html CURRENT_USER()]]: the internal user is executing the query.
+
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html CURRENT_USER()]]: the internal user who is executing the query.
  
 
There is some difference between 1 and 2.
 
There is some difference between 1 and 2.
  
 
The main one is that an anonymous user could connect (if allowed)
 
The main one is that an anonymous user could connect (if allowed)
with any name but the MySQL internal user is an empty name (<nowiki>''</nowiki>).
+
with any name, but the MySQL internal user is an empty name (<nowiki>''</nowiki>).
  
 
Another difference is that a stored procedure or a stored function
 
Another difference is that a stored procedure or a stored function
 
are executed as the creator user, if not declared elsewhere. This  
 
are executed as the creator user, if not declared elsewhere. This  
could be known by using '''CURRENT_USER'''.
+
can be known by using '''CURRENT_USER'''.
  
 
In band injection:
 
In band injection:
Line 148: Line 147:
 
==== INFORMATION_SCHEMA ====
 
==== INFORMATION_SCHEMA ====
 
From MySQL 5.0 a view named [[http://dev.mysql.com/doc/refman/5.0/en/information-schema.html INFORMATION_SCHEMA]] was created.
 
From MySQL 5.0 a view named [[http://dev.mysql.com/doc/refman/5.0/en/information-schema.html INFORMATION_SCHEMA]] was created.
It allows to get all informations about databases, tables and columns
+
It allows us to get all informations about databases, tables, and columns,
 
as well as procedures and functions.
 
as well as procedures and functions.
  
Here is a summary about some interesting View.
+
Here is a summary of some interesting Views.
 
{| border=1
 
{| border=1
 
  || '''Tables_in_INFORMATION_SCHEMA''' || '''DESCRIPTION'''
 
  || '''Tables_in_INFORMATION_SCHEMA''' || '''DESCRIPTION'''
Line 178: Line 177:
 
|-
 
|-
 
|}
 
|}
All of these informations could be extracted by using known techniques as  
+
All of this information could be extracted by using known techniques as  
described in SQL Injection paragraph.
+
described in SQL Injection section.
  
 
=== Attack vectors ===
 
=== Attack vectors ===
Line 185: Line 184:
 
==== Write in a File ====
 
==== Write in a File ====
  
If connected user has '''FILE''' privileges _and_ single quotes are not escaped,
+
If the 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.
+
the 'into outfile' clause can be used to export query results in a file.
  
 
  Select * from table into outfile '/tmp/file'
 
  Select * from table into outfile '/tmp/file'
  
N.B. there are no ways to bypass single quotes outstanding filename.  
+
Note: there is no way to bypass single quotes surrounding a filename.  
 
So if there's some sanitization on single quotes like escape (\') there will
 
So if there's some sanitization on single quotes like escape (\') there will
be no way to use 'into outfile' clause.
+
be no way to use the 'into outfile' clause.
  
This kind of attack could be used as an out-of-band technique to gain informations
+
This kind of attack could be used as an out-of-band technique to gain information
 
about the results of a query or to write a file which could be executed inside the  
 
about the results of a query or to write a file which could be executed inside the  
 
web server directory.
 
web server directory.
Line 204: Line 203:
 
'''Result Expected:'''<br>
 
'''Result Expected:'''<br>
 
'' Results are stored in a file with rw-rw-rw privileges owned by  
 
'' Results are stored in a file with rw-rw-rw privileges owned by  
mysql user and group.
+
MySQL user and group.
  
 
Where ''/var/www/root/test.jsp'' will contain:
 
Where ''/var/www/root/test.jsp'' will contain:
 
  <nowiki>//field values//
 
  <nowiki>//field values//
 
<%jsp code here%></nowiki>
 
<%jsp code here%></nowiki>
 
  
 
==== Read from a File ====
 
==== Read from a File ====
Line 216: Line 214:
 
filesystem permissions.  
 
filesystem permissions.  
  
If connected user has '''FILE''' privileges, it could be used to get files content.
+
If a connected user has '''FILE''' privileges, it could be used to get the files' content.
  
 
Single quotes escape sanitization can by bypassed by using previously described
 
Single quotes escape sanitization can by bypassed by using previously described
Line 225: Line 223:
 
'''Result Expected:'''<br>
 
'''Result Expected:'''<br>
  
'' the whole file will be available for exporting by using standard techniques.''
+
''The whole file will be available for exporting by using standard techniques.''
 
+
  
 
=== Standard SQL Injection Attack ===
 
=== Standard SQL Injection Attack ===
Line 234: Line 231:
 
By using already mentioned SQL Injection attacks and the already described
 
By using already mentioned SQL Injection attacks and the already described
 
MySQL features, direct SQL injection could be easily accomplished at a level
 
MySQL features, direct SQL injection could be easily accomplished at a level
depth depending primarily on mysql version the pentester is facing.
+
depth depending primarily on the MySQL version the pentester is facing.
  
 
A good attack is to know the results by forcing a function/procedure
 
A good attack is to know the results by forcing a function/procedure
 
or the server itself to throw an error.
 
or the server itself to throw an error.
 
A list of errors thrown by MySQL and in particular native functions could
 
A list of errors thrown by MySQL and in particular native functions could
be found on [[http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html MySQL Manual]].
+
be found on [http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html MySQL Manual].
  
 
=== Out of band SQL Injection ===
 
=== Out of band SQL Injection ===
Line 245: Line 242:
 
Out of band injection could be accomplished by using the [[#Write_in_a_File|'into outfile']] clause.
 
Out of band injection could be accomplished by using the [[#Write_in_a_File|'into outfile']] clause.
 
=== Blind SQL Injection ===
 
=== Blind SQL Injection ===
For blind SQL injection there is a set of useful function natively provided by MySQL server.
+
For blind SQL injection, there is a set of useful function natively provided by MySQL server.
  
 
* String Length:  
 
* String Length:  
Line 252: Line 249:
 
*: ''SUBSTRING(string, offset, #chars_returned)''
 
*: ''SUBSTRING(string, offset, #chars_returned)''
 
* Time based Blind Injection: BENCHMARK and SLEEP  
 
* Time based Blind Injection: BENCHMARK and SLEEP  
*: ''BENCHMARK(#ofcicles,action_to_be_performed )''
+
*: ''BENCHMARK(#ofcycles,action_to_be_performed )''
*: Benchmark function could be used to perform timing attacks when blind injection by boolean values does not yeld any results.
+
*: The benchmark function could be used to perform timing attacks, when blind injection by boolean values does not yield any results.
 
*: See. SLEEP() (MySQL > 5.0.x) for an alternative on benchmark.
 
*: 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
+
For a complete list, refer to MySQL manual - http://dev.mysql.com/doc/refman/5.0/en/functions.html
 
+
  
 
== References ==
 
== References ==
 
'''Whitepapers'''<br>
 
'''Whitepapers'''<br>
* Chris Anley: "Hackproofing MySQL" -http://www.nextgenss.com/papers/HackproofingMySQL.pdf
+
* Chris Anley: "Hackproofing MySQL" - http://www.databasesecurity.com/mysql/HackproofingMySQL.pdf
  
 
'''Case Studies'''<br>
 
'''Case Studies'''<br>
* Time Based SQL Injection Explained - http://www.f-g.it/papers/blind-zk.txt
+
* Zeelock: Blind Injection in MySQL Databases - http://archive.cert.uni-stuttgart.de/bugtraq/2005/02/msg00289.html
  
 
'''Tools'''<br>
 
'''Tools'''<br>
 
* Francois Larouche: Multiple DBMS SQL Injection tool - http://www.sqlpowerinjector.com/index.htm<br>
 
* Francois Larouche: Multiple DBMS SQL Injection tool - http://www.sqlpowerinjector.com/index.htm<br>
* ilo--:  MySQL Blind Injection Bruteforcing, Reversing.org - http://www.reversing.org/node/view/11 sqlbftools<br>
+
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools]
* Daniele Bellucci: MySQL Injection Inference tool - http://sourceforge.net/projects/sqlmap SQLMap<br>
+
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.org/
* Antonio Parata: Dump Files by SQL inference on Mysql - http://www.ictsc.it/site/IT/projects/sqlDumper/sqldumper.src.tar.gz<br>
+
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
 
+
* http://sqlsus.sourceforge.net/
{{Category:OWASP Testing Project AoC}}
+

Latest revision as of 11:54, 6 November 2012

Template:OWASP Testing Guide 4

Contents

Short Description of the Issue

SQL Injection vulnerabilities occur whenever input is used in the construction of a 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 an SQL injection vulnerability is found in an application backed by a MySQL database, there are a number of attacks that could be performed depending on the MySQL version and user privileges on DBMS.

MySQL comes with at least four versions which are 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

It should be noted that for MySQL versions before 4.0.x, only Boolean or time-based Blind Injection attacks could be used, since the subquery functionality or UNION statements were not implemented.

From now on, we will assume that there is a classic SQL injection vulnerability, which can be triggered by a request similar to the the one described in the Section on Testing for SQL Injection.

http://www.example.com/page.php?id=2

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 application, to work properly, needs to use constant strings, two cases are to be differentiated:

  1. Web app escapes single quotes (' => \')
  2. Web app does not escape single quotes (' => ')

Under MySQL, there is a standard way to bypass the need of single quotes, having a constant string to be declared without the need for single quotes.

Let's suppose we want to know the value of a field named 'password' in a record, with a condition like the following: password like 'A%'

  1. The ASCII values in a concatenated hex:
    password LIKE 0x4125
  2. 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.

For example the following injection will result in an error:

1 ; update tablename set code='javascript code' where 1 --

Information gathering

Fingerprinting MySQL

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 ignore a clause in MySQL dialect. When a comment block ('/**/') contains an exclamation mark ('/*! sql here*/') it is interpreted by MySQL, and is considered as a normal comment block by other DBMS as explained in MySQL manual.

Example:

1 /*! and 1=0 */

Result Expected:
If MySQL is present, the clause inside the comment block will be interpreted.

Version

There are three ways to gain this information:

  1. By using the global variable @@version
  2. By using the function [VERSION()]
  3. 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 /*

Inferential injection:

1 AND @@version like '4.0%'

Result Expected:
A string like this: 5.0.22-log

Login User

There are two kinds of users MySQL Server relies upon.

  1. [USER()]: the user connected to the MySQL Server.
  2. [CURRENT_USER()]: the internal user who 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 can be known by using CURRENT_USER.

In band injection:

1 AND 1=0 UNION SELECT USER() 

Inferential injection:

1 AND USER() like 'root%'

Result Expected:
A string like this: user@hostname

Database name in use

There is the native function DATABASE()

In band injection:

1 AND 1=0 UNION SELECT DATABASE() 

Inferential injection:

1 AND DATABASE() like 'db%'

Result Expected:
A string like this: dbname

INFORMATION_SCHEMA

From MySQL 5.0 a view named [INFORMATION_SCHEMA] was created. It allows us to get all informations about databases, tables, and columns, as well as procedures and functions.

Here is a summary of some interesting Views.

Tables_in_INFORMATION_SCHEMA DESCRIPTION
..[skipped].. ..[skipped]..
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 this information could be extracted by using known techniques as described in SQL Injection section.

Attack vectors

Write in a File

If the connected user has FILE privileges and single quotes are not escaped, the 'into outfile' clause can be used to export query results in a file.

Select * from table into outfile '/tmp/file'

Note: there is no way to bypass single quotes surrounding a filename. So if there's some sanitization on single quotes like escape (\') there will be no way to use the 'into outfile' clause.

This kind of attack could be used as an out-of-band technique to gain information about the results of a query or to write a file which could be executed inside the web server directory.

Example:

1 limit 1 into outfile '/var/www/root/test.jsp' FIELDS ENCLOSED BY '//'  LINES TERMINATED BY '\n<%jsp code here%>';

Result Expected:
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 a connected user has FILE privileges, it could be used to get the files' content.

Single quotes escape sanitization can by bypassed by using previously described techniques.

load_file('filename')

Result Expected:

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 the 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:
    LENGTH(str)
  • Extract a substring from a given string:
    SUBSTRING(string, offset, #chars_returned)
  • Time based Blind Injection: BENCHMARK and SLEEP
    BENCHMARK(#ofcycles,action_to_be_performed )
    The benchmark function could be used to perform timing attacks, when blind injection by boolean values does not yield any results.
    See. SLEEP() (MySQL > 5.0.x) for an alternative on benchmark.

For a complete list, refer to MySQL manual - http://dev.mysql.com/doc/refman/5.0/en/functions.html

References

Whitepapers

Case Studies

Tools