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

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

From OWASP
Jump to: navigation, search
Line 132: Line 132:
 
Sometimes this method does not work. In the case the server returns two defferent 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 template in order to decide the result of the test.
 
Sometimes this method does not work. In the case the server returns two defferent 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 template in order to decide the result of the test.
 
In the previous tests, we are supposed to know in what way it is possible to understand when we have ended the inference beacause we have obtained the value.  
 
In the previous tests, we are supposed to know in what way it is possible to understand when we have ended the inference beacause we have obtained the value.  
In order to understand when we have ended, we will use one characteristic of the SUBSTRING function and the CHAR_LENGTH function.
+
In order to understand when we have ended, we will use one characteristic of the SUBSTRING function and the LENGTH function.
 
When our test will return a true value and we would have used an ASCII code equals to 0 (that is the value null), then that mean that we have ended to make inference, or that the value we have analyzed effectively contains the value null.
 
When our test will return a true value and we would have used an ASCII code equals to 0 (that is the value null), then that mean that we have ended to make inference, or that the value we have analyzed effectively contains the value null.
  

Revision as of 23:02, 15 November 2006

[Up]
OWASP Testing Guide v2 Table of Contents

Brief Summary

An SQL injection attack consists of insertion or "injection" of an SQL query via the input data from the client to the application.
A successful sql injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such shutdown the DBMS) or recover the content of a given file present on the DBMS filesystem.

Description of the Issue

Three classes of SQL Injection exist: inband, out-of-band and inferential. The inband type uses the same channel of the attack in order to receive the information, the out-of-band type uses an external channel. The channel used for the attack to receive the information (for example taking advantage of other protocols of communication, e.g. smtp protocol) and the inferential uses inferential methods for obtaining the required value. We can find a sql injection vulnerability in general by observing particular error messages returned by the application. However in some cases the application is written in such way that returns one customized error page. In this case we have a Blind Sql Injection.

Black Box testing and example

Standard Sql Injection Testing

Consider the following sql query:

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

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

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

The query will be:

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

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

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

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

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

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

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

In this way we'll get the following query:

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

The url request will be:

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

Which return a number of values. Sometimes, the authentication code verifies that the number of returned tuple is exactly equal to 1. In the previous examples, this situation would be difficult (in the database there is only one value per user). In order to go around to this problem, it is enough to insert a sql command, that imposes the condition that the number of the returned tuple must be one. (One record returned) In order to reach this goal, we use the command "LIMIT <num>", where <num> is the number of the tuples that we expect to be returned. The value of the fields Username and Password regarding the previous example will be modified according the following:

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

In this way we create a request like the follow:

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

Union Query Sql Injection Testing

Another test to carry out, involves the use of the UNION operation. Through such operation it is possible, in case of Sql Injection, to join a query, purposely forged from the tester, to the original query. The result of the forged query will be joined to the result of the original query, allowing the tester to obtain the values of fields of other tables. We suppose for our examples that the query executed from the server is the following:

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

We will set the following Id value:

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

We will have the following query:

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

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

Blind Sql Injection Testing

We have pointed out that exists another category of sql injection, called Blind Sql Injection, in which nothing is known on the outcome of an operation. This behavior happens in cases where the programmer has created a customed error page that does not reveal anything on the structure of the query or on the database. (Does not return a SQL error, it may just return a HTTP 500).
Thanks to the inference methods it is possible to avoid this obstacle and thus to succeed to recover the values of some desired fields. The method consists in carrying out a series of booloean queries to the server, observing the answers and finally deducing the meaning of such answers. We consider, as always, the www.example.com domain and we suppose that it contains a parameter vulnerable to sql injection of name id. This means that carrying out the following request:

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

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

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

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

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

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

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

Through such functions we will execute our tests on the first character and, when we will 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 time (selecting a single character means to impose the length parameter to 1) and 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 ASCII table, until finding the desired value. As an example we will insert the following value for Id:

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

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

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

The previous returns a result if and only if the first character of field username is equal to the ASCII value 97. If we get a false value then we increase the index of 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 table and we pass to analyze the next character, modifying the parameters of SUBSTRING function. The problem is to understand in that way we distinguish the test that has carried a true value, from the one that has carried a false value. In order to make this we create a query that we are sure returns a false value. This is possible by the following value as field Id:

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

by which will create the following query:

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

The answer of the server obtained (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 exposed before. Sometimes this method does not work. In the case the server returns two defferent 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 template in order to decide the result of the test. In the previous tests, we are supposed to know in what way it is possible to understand when we have ended the inference beacause we have obtained the value. In order to understand when we have ended, we will use one characteristic of the SUBSTRING function and the LENGTH function. When our test will return a true value and we would have used an ASCII code equals to 0 (that is the value null), then that mean that we have ended to make inference, or that the value we have analyzed effectively contains the value null.

We will insert the following value for the field Id:

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

Where N is the number of characters that we have analyzed with now (excluded the null value). The query will be:

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

that gives back a true or false value. If we have a true value, then we have ended to make inference and therefore we have gained the value of the parameter. If we obtain a false value, this means that the null character is present on the value of the parameter, then we must continue to analyze the next parameter until we will 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. A simple tool which performs this task, via GET requests on MySql DB is SqlDumper, is shown below.

Sqldumper.jpg

References

Whitepapers

Tools

  • Francois Larouche: Multiple DBMS Sql Injection tool - [SQL Power Injector]
  • ilo--: MySql Blind Injection Bruteforcing, Reversing.org - [sqlbftools]
  • Daniele Bellucci: MySql Injection Inference tool - [SqlMap]
  • Antonio Parata: Dump Files by sql inference on Mysql - [SqlDumper]

OWASP Testing Guide v2

Here is the OWASP Testing Guide v2 Table of Contents