How to implement long character truncation in MySQL

MySQL ultra-long character truncation, also known as "SQL-Column-Truncation", was proposed by security researcher Stefan Esser in August 2008.

There is a sql_mode option in a setting in MySQL. When sql_mode is set to default, that is, when the STRICT_ALL_TABLES option is not turned on (MySQLsql_mode defaults to default), MySQL will only prompt a warning when inserting an overly long value. Instead of error, this may cause some truncation problems.

Create a new table for testing. The table structure is as follows (MySQL5.1):

CREATE TABLE USERS(
id int(11) NOT NULL, //长度为7
username varchar(7)NOT NULL,
password varchar(12)NOT NULL , 
)

Insert the following SQL statements (inject prompt messages) respectively.

①Insert normal SQL statement.

mysql> insert into users(id,username,password)values(1,'admin','admin');//成功插入，无警告，无错误
 
Query OK,1 row affected(0.00 sec)

② Insert the wrong SQL statement. At this time, there are three spaces to the right of "admin" and the length is 8, which has exceeded the original specified length.

mysql> insert into users(id,username,password)values(2,'admin       ','admin');
//成功插入，一个警告
Query OK,1 row affected,1 warning(0.00 sec)

③Insert the wrong SQL statement, and the length has exceeded the original specified length.

mysql> insert into users(id,username,password) values(3,'admin    x','admin');
//成功插入，一个警告
Query OK,1 row affected,1 warning(0.00 sec)

MySQL prompts that all three statements have been inserted into the database, but the last two statements generated warnings. You can confirm whether it was inserted into the database by executing a SQL statement.

mysql> select username from users;

You can see that all three pieces of data have been inserted into the database, but the values ​​have changed. At this time, the length is obtained through length to determine the length of the value.

mysql> select length(username)from users where id =1 ;

It can be found that the length of the second and third data is 7, which is the specified length of the column. It can be seen that by default, if the data exceeds the default length of the column, MySQL will Truncated.

But how can this be an attack?

You will know by querying the user whose user name is 'admin'.

mysql> select username from users where username='admin';

Only the user named admin is queried, but the other two admin users with inconsistent lengths are also queried, which will cause some security problems. For example, there is an administrator login that is judged like this: The statement is as follows:

$sql = "select count(*) from users where username='admin' and password='*******;

Even if the SQL statement does not have an injection vulnerability, an attacker may still log in to the management page. Assuming that the user name of the administrator login is admin, then the attacker only needs to register an "admin" user to easily enter the backend management page. The famous WordPress has been attacked in this way.

The above is the detailed content of How to implement long character truncation in MySQL. For more information, please follow other related articles on the PHP Chinese website!