MySQL and SQL Field Truncation Vulnerability

Currently, many Web developers have not noticed the two problems mentioned by the author.
The first problem is that MySQL has a default configuration parameter max_packet_size, which is used to limit the data packet size between the MySQL client and the MySQL server. The default configuration of MySQL is 1 MB. If the data sent by the client exceeds 1 MB, the MySQL server ignores the request data. I will give two examples of using this defect. The first is to use ultra-long data to invalidate the MySQL Logging program, and the second is in the PHP + MySQL environment, the Session cleanup program of PHP fails to clean up the session because the number of data packets sent by a session cleanup request exceeds the limit of max_packet_size.

In fact, many PHP + MySQL programs run user-uploaded attachments, while the limit for uploading attachments in PHP + MySQL is generally greater than 1 MB, therefore, PHP programmers generally modify the value of max_packet_size to be greater than 1 MB. This brings us some trouble to exploit the vulnerability. After all, in the current network condition, it is tolerable to construct more than 1 MB of data for upload. However, a large amount of data is a test of our patience.

The second problem is serious. MySQL will perform the default string Truncation for data insertion operations that exceed the field length. For example, if the length of a field is defined as 10, if the length of the inserted string exceeds 10, MySQL will automatically remove some strings that exceed 10 and insert them to the data table. By default, MySQL generates a warning, but this warning is not captured by the Web application. Therefore, on the surface, ultra-long data can be successfully inserted into the data table. The example given by the author below is very representative. The first is a scenario hypothesis:

The application is a forum where new users can register
The administrator's name is known e.g. 'admin'
MySQL is used in the default mode
There is no application restriction on the length of new user names
The database column username is limited to 16 characters
If you try to register a user whose username is admin, the registration fails due to the verification of the isAlreadyRegistered function in the Web application.

SELECT * FROM user WHERE username = admin
However, if you use the username 'admin x' for registration (note that there are 11 spaces between admin and x), the registration process is as follows:

The isAlreadyRegistered function uses the preceding SQL statement to check whether there are users with the same user name in the user table. The query results certainly do not exist. The user registration is successful!

In fact, the username actually inserted into the user table is 'admin '! That is to say, MySQL not only truncates strings that exceed the length limit, but also truncates the blank characters at the beginning and end of the string! Therefore, two admin users exist in the user table!

Next, the user logs in. He uses the username admin and the password 'admin x' he just set. Assume that the login authentication and authorization functions of Web applications are as follows:

$ Userdata = null;
If (isPasswordCorrect ($ username, $ password )){
$ Userdata = getUserDataByLogin ($ username );
...
}
The SQL statement used by the isPasswordCorrect function is:

SELECT username FROM users WHERE username =? AND passhash =?
The SQL statement used by the getUserDataByLogin function is:

SELECT * FROM users WHERE username =?
As you can see, the preceding statements use pre-compiled SQL statements and cannot implement SQL injection. However, because of the default MySQL Field Truncation policy, the isPasswordCorrect function will successfully execute and return the username admin. The subsequent getUserDataByLogin operation will also be executed correctly. The returned result is an array, however, Web applications generally obtain the first result in the returned array, that is, all the data of the real administrator admin!

How can this problem be solved? Without SQL injection, you can get the administrator privilege!

Note: after testing, the above vulnerability exploitation process exists and can be used in MySQL 4. However, in MySQL 5, the local test fails. The key to failure is that MySQL 5 reports an error when inserting a string that exceeds the field length limit and stops the string insertion Operation.

Appendix: MySQL 4 Testing Process

Mysql> select * from tb_sqltest where name = jason;
+ ---- + ------- + -------- + ------ +
| Id | name | remark | time | col1 | col2 | col3 |
+ ---- + ------- + -------- + ------ +
| 1 | jason | NULL |
+ ---- + ------- + -------- + ------ +
1 row in set

Mysql> select * from tb_sqltest where name = jason x;
Empty set

Mysql> insert into tb_sqltest (id, name) values (2, jason x );
Query OK, 1 row affected

Mysql> select * from tb_sqltest where name = jason;
+ ---- + ------- + -------- + ------ +
| Id | name | remark | time | col1 | col2 | col3 |
+ ---- + ------- + -------- + ------ +
| 1 | jason | NULL |
| 2 | jason | NULL |
+ ---- + ------- + -------- + ------ +
2 rows in set