SQL Injection : Looking for Vulnerabilities

Overview

Many Web projects, both large and small, use databases: A database is a convenient tool for data storage. In most cases, databases are accessed using structured query language (SQL). SQL is a universal language suitable for all databases. However, its syntax can be slightly different in different types of database servers.

A vulnerability called SQL source code injection (or, simply, SQL injection) appears when the attacker can embed any data into SQL queries. SQL injection can be crucial for the system, but despite its danger it is one of the most frequent vulnerabilities.

Suppose you need to test a script for the SQL injection vulnerability.

To test the script, write down a list of all the parameters the script can process. To do this, analyze the COOKIE parameters set by this script and, possibly, by other scripts on the server. In addition, list all the GET and POST parameters that the script passes to itself or receives from other scripts.

Then check other fields of the HTTP header such as Referer and User-Agent.

Change the values of these parameters one by one so that they affect the appearance of the displayed page (assuming they are not filtered in SQL queries).

For example, replace integer values with fractions, insert apostrophes and quotation marks, and insert mathematical operations.

When Error Messages are Enabled

Suppose that the output of error messages is enabled in the system. That is, if an error is in an SQL query, the corresponding error message and, possibly, the text of the query are output to the browser.

When the attacker sees the text of the query, he or she can easily specify a destructive value for a vulnerable parameter.

Suppose you had sent the id=123' GET parameter to the http://localhost/3/1.php script (http://localhost/3/1.php?id=123'), and the following message was displayed:

   Error when accessing the database:


   select * from test1 where id='123''

The attacker can draw a few conclusions sufficient to exploit this vulnerability.

First, apostrophes aren't filtered. Second, the value of the id GET parameter is inserted into an SQL query without filtration, so the actual query has the following form:

   select * from test1 where id='$id'

In this case, the attacker obtains enough information to create a value of the id parameter that will allow him or her to bypass the apostrophes. For example, imagine that the attacker wants to send the following query to the SQL server:

   select * from test1 where id='9999'; drop table users; /*'

In this case, the attacker just needs to send the following HTTP GET request:

http://localhost/3/l.php?id=999';+drop+table+users;+/*

Note that not all SQL servers allow the user to concatenate queries with semicolons as in this example. In addition, high privileges are necessary to delete a table.

When the attacker knows, which parameter is vulnerable to SQL injection, he or she also needs to know, nuances of SQL implementation in the target database server to create an HTTP request that performs malicious actions.

Even when the text of an erroneous SQL query isn't displayed, the displayed error message makes it easier for the attacker to achieve his or her malicious goals. It is a common for an error message to disclose the type of database server used in the system. Consider the following example from http://localhost/3/2.php?id=123':

   Warning: mysql_fetch_object(): supplied argument is not a valid


   MySQL result resource in x:\localhost\3\2.php on line 18


   records not found

If the id=123' parameter was sent, this error message unambiguously indicates that an error was in an SQL request and that it was caused by the apostrophe in the parameter value. In addition, the attacker sees that a MySQL database server is used.

The attacker can try to investigate, which error messages are output in response to particular values of a nonfiltered parameter, and to analyze the messages to eventually create an SQL request similar to the request that causes the error message in this example.

When Error Messages are Disabled

Suppose that the output of error messages is disabled in the system.

Nevertheless, the attacker can judge from indirect indications whether an error happened. For example, different responses to illogical data in different queries can indicate that one query caused an error and another returned nothing.

The attacker can find how the system responds to queries that return empty results even when there is no SQL injection vulnerability.

There can be a few types of results of queries that retrieve information from a database. First, a request can return a normal nonempty result. In this case, you can be sure that the script outputs the query result regardless of whether the programmer intended this script to output these data.

Such a variant is the final goal of the attack exploiting the SQL injection vulnerability. The data that the script should have output can be either output or not.

The attacker's main task will be to create a value of the vulnerable parameter so that the query returns data necessary to the attacker rather than data the script should have output.

Second, a request can return an empty result. This means no record in the database would correspond to the parameters sent in the query. In most cases, a page without contents will be displayed. This will indicate that the results of queries aren't checked for emptiness.

Third, the user can receive a message informing him or her that no record was found in the database. This is a normal response of a correct system.

In most cases, the attacker can find how the script responds to an empty result even when there is no SQL injection vulnerability. He or she just needs to send a special value in an HTTP parameter. The value should belong to the same set as the original value of the parameter, but it should be missing from the database.

For example, if an integer is normally sent to the script (e.g., id=23), the attacker will send a large integer (e.g., id=9999999). It is likely that this value is missing from the database, and the syntactically correct SQL query will return an empty result.

For another example, if a parameter is a date, the attacker will send a date that cannot correspond to any record in the database (e.g., a date in the distant past or future).

Alternatively, instead of a meaningful string, the attacker will send a string of random characters.

Finally, an SQL query can result in error messages related to the syntax of the query or its failure (e.g., nonexistent tables or table columns).

A well-designed, invulnerable, and reliable system is free from such errors. An error for certain values of one or more parameters sent using HTTP indicates that SQL injection is possible.

When an error is in an SQL query, the system can respond differently. If an error message is displayed, the presence of the SQL injection vulnerability becomes apparent. A system error message such as 500 - Internal Server Error can be displayed. As I demonstrated earlier, it can be caused by displaying an SQL query message before the Content-Type header is sent to the browser. This is true for Perl scripts.

Sometimes, an empty page is displayed. It is unlikely that in this situation the normal contents of the HTML page will be displayed or that the page will look as if the query returned an empty result.

In any case, the attacker knows what the HTML page looks like when an SQL query returns normal data. This is the normal behavior of the system responding to a correct query. Then the attacker will try to obtain the system's responses to parameter values that don't correspond to any record in the database. This will be an easy task: The attacker needs to send HTTP parameters that are syntactically correct but illogical. Examples were given earlier in this section.

If no error messages are displayed, it will be difficult for the attacker to find what the HTML page looks like in the third state of the system. However, if the attacker notices that with certain values of certain parameters the system behaves differently than in the first and second states, he or she can conclude that these parameters are likely to cause an error in an SQL query.

If there is the SQL injection vulnerability, an SQL query with a parameter, whose value contains an apostrophe or a quotation mark, almost always causes an error message.

In general, there can be four situations: Whether apostrophes and quotation marks are screened with backslashes or not, and whether the parameter is between apostrophes or quotation marks or not.

Consider all four SQL queries created in these cases.

First, apostrophes and quotation marks aren't screened with backslashes, and the affected parameter isn't between apostrophes.

http://localhost/3/3.php?id=1'
You have an error in your SQL syntax near ''' at line 1 Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in x:\localhost\3\3.php on line 19 records not found

Second, apostrophes and quotation marks aren't screened with backslashes, and the affected parameter is between apostrophes.

http://localhost/3/4.php?id=1'
You have an error in your SQL syntax near ''1''' at line 1 Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in x:\localhost\3\4.php on line 19 records not found

Third, apostrophes and quotation marks are screened with backslashes, and the affected parameter isn't between apostrophes.

http://localhost/3/5.php?id=1'
You have an error in your SQL syntax near '\'' at line 1 Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in x:\localhost\3\5.php on line 20 records not found

Finally, apostrophes and quotation marks are screened with backslashes, and the affected parameter is between apostrophes.

http://localhost/3/6.php?id=1'
John Smith

As you can see, the only case, in which no error emerges in the SQL query, is the one, in which apostrophes and quotation marks are screened with backslashes and the affected parameter is between apostrophes. This is because when apostrophes and quotation marks are screened in a value of a variable, the variable won't contain an unscreened apostrophe or quotation mark, and it will be impossible to embed any characters outside this string. There is no SQL injection vulnerability in the fourth example.

Therefore, if there is the SQL injection vulnerability, a syntax error will take place in an SQL query whose vulnerable parameter is a string with an apostrophe or a quotation mark. The attacker just needs to detect these situations. When error message output is enabled, it will be an easy task because the HTML page returned to the client will contain the text of an error message.

The attacker's actions when error message output is disabled were described earlier.

Situations, in which an apostrophe or a quotation mark embedded into an unfiltered parameter causes a query error, are common. However, they aren't the only ones. Embedding such a character may not lead to an error. The query could return an empty result or even a predictable result, but the SQL injection vulnerability would still be present.

Consider a typical situation:

• http://localhost/3/7.php

• http://localhost/3/7.php?id=l

• http://localhost/3/7.php?id=99

• http://localhost/3/7.php?id=l'

• http://localhost/3/7.php?id=1"

• http://localhost/3/7.php?id=labc

All these requests except for the last one display predictable results. An apostrophe or a quotation mark in the parameter value doesn't cause an error in the SQL query.

The last request to the script results in a database error. In this case, the error message is displayed, but even when error messages are disabled the user can judge from indirect indications that an error happened.

Here is the code of this script.

http://localhost/3/7.php

<? if(empty($id)) { echo " <form> Enter ID of the person (integer): <input type=text name=id><input type=submit> </form> "; exit; }; mysql_connect("localhost", "root", ""); mysql_select_db("bcok1"); $id=$_GET["id"]; $id=str_replace("\'", "", $id); $id=str_replace("\"", "", $id); $sq="select * from test1 where id=$id"; $q=mysql_query($sq); echo mysql_error(); if($r=mysql_fetch_object($q)) echo $r->name; else echo "records not found"; ?>

The reason for such behavior of this script is obvious: All apostrophes and quotation marks are deleted from the received parameter. This is done in the $id=str_replace("\'", "", $id) and $id=str_replace ("\'", "", $id) statements.

So, the reason for such behavior is filtration of received parameters. In this example, filtration is insufficient.

What else can the attacker do to test a script for the SQL injection vulnerability in a received parameter? When the value of the received parameter isn't between apostrophes or quotation marks, the attacker can easily detect this by replacing a normal value with a mathematical expression. For example, he or she can replace an integer value with a mathematical expression that returns this value.

Consider an example.

http://localhost/3/8.php

<? if(empty($id)) { echo " <form> Enter the record ID (integer): <input type=text name=id><input type=submit> </form> "; exit; }; mysql_connect("localhost", "root", ""); mysql_select_db("bookl"); $id=$_GET["id"]; $id=str_replace("'", "", $id); $id=str_replace("\"", "", $id); $sq="select * from test2 where xid=$id"; $q=mysql_query($sq); echo mysql_error(); if($r=mysql fetch_object($q)) echo $r->value; else echo "records not found"; ?>

This script makes an SQL query with the id parameter to the test2 table in the bookl database. Here is the structure of this table:

   su-2.05b# mysql -u root


   Welcome to the MySQL monitor. Commands end with ; or \g.


   Your MySQL connection id is 5 to server version: 4.0.18


   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


   mysql> use bookl;


   Reading table information for completion of table and column names


   You can turn off this feature to get a quicker startup with -A


   Database changed


   mysql> describe test2;


   +-------+--------------+------+-----+---------+----------------+


   | Field | Type         | Null | Key | Default | Extra          |


   +-------+--------------+------+-----+---------+----------------+


   | id    | int(11)      |      | PRT | NULL    | auto increment |


   | xid   | varchar(5)   |      |     | 0       |                |


   | value | varchar(255) |      |     | 0       |                |


   +-------+--------------+------+-----+---------+----------------+


   3 rows in set (0.00 sec)


   mysql>

As you can see, the value of the xid parameter used in the query isn't integer. It is a five-character string. As practice shows, an integer or a mathematical expression can return an integer to the right of this value.

The simplest mathematical operations are addition, subtraction, multiplication, and division. They are described in the SQL 92 standard.

Note that the plus character is used to URL-encode a space, so if you want to send a plus inside a parameter value, you should URL-encode it as %2B.

You don't need to URL-encode the minus character.

Suppose that the value of a parameter is replaced with a mathematical expression that returns the original value. If the displayed HTML page entirely or partly coincides with the page returned for the original value, the attacker can be sure that this parameter is subject to the SQL injection vulnerability. The attacker would need to be aware that this coincidence can be a result of certain filtration.

For example, compare the results of the following requests.

http://localhost/3/8.php?id=10000
this is the first value

http://localhost/3/8.php?id=10001
this is the second value

http://localhost/3/8.php?id=10001-1
this is the third value

In the third request, the 10001-1 expression is inserted instead of the original value.

The HTML page output in the third case coincides with the first HTML page.

A supposition that the value of the received parameter could be filtered and then cast to an integer is refuted by the http://localhost/3/8.php?id=10001 request, whose parameter differs from the third request's parameter only in the mathematical operation, because it returns an HTML page different from that returned by the first request.

Consider another example.

http://localhost/3/9.php?id=10001
this is the second value

http://localhost/3/9.php?id=10002
this is the third value

http://localhost/3/9.php?id=10002-1
this is the third value

http://localhost/3/9.php?id=10002anytext
this is the third value

In this example, output HTML pages coincide for the parameter values id=10002-l and id=10002 as if the script truncates the parameter value starting with the minus character.

At the same time, the HTML page returned to the request with the id=10001 value differs from that returned to the request with the id=l0002-l value. If it was the SQL injection vulnerability in the parameter, these two pages would coincide.

The attacker can suppose that the received parameter value is strictly filtered and explicitly cast to this parameter.

The last request confirms this supposition. Therefore, no SQL injection vulnerability is in this parameter.

Consider another example.

http://localhost/3/10.php?id=1123
a few possible values

http://localhost/3/10.php?id=1342
perhaps, a few possible values

http://localhost/3/10.php?id=1342'
perhaps, a few possible values

http://localhost/3/10.php?id=1342-1
perhaps, a few possible values

http://localhost/3/10.php?id=1341
perhaps, a few possible values

http://localhost/3/10.php?id=1343
records not found

http://localhost/3/10.php?id=1343-1
perhaps, a few possible values

What can the attacker conclude from the results of these requests?

The script is likely to accept an integer value of the id parameter. It makes sense to test this parameter for the SQL injection vulnerability.

After the third request, the attacker understands that apostrophes in the parameter value are filtered. Therefore, if the parameter value is between apostrophes in the query, the SQL injection vulnerability is absent because the attacker won't be able to embed malicious characters outside the string.

The page displayed for id=1342-1 coincides with that displayed for id=1341. It also coincides with the page displayed for the id=1342 parameter. Therefore, the attacker cannot be sure that there is the SQL injection vulnerability.

The coincidence of the pages displayed for the id=1342 and id=1341 parameters only means the coincidence of database records corresponding to these parameter values. The attacker cannot know the cause of coincidence of the pages output for the request with the id=1342-1 parameter and for the request with the id=1342 parameter: Whether it is the SQL injection vulnerability or it is casting the id=1342-1 value to the id=1342 value.

To check whether there is the SQL injection vulnerability, the attacker would submit other pairs of the id parameter's values.

In this example, the attacker sees that the page displayed for id=1342 coincides with that displayed for id=1343-1 and differs from the page displayed for id=1343, which tells the user that no records were found. These facts allow the attacker to suppose that there is the SQL injection vulnerability.

These methods allow the attacker to detect the SQL injection vulnerability in most cases.