PHP anti-SQL injection should not be used with addslashes and mysql_real_escape_string

: This article describes how to prevent SQL injection in PHP and avoid using addslashes and mysql_real_escape_string. For more information about PHP tutorials, see. Bloggers are keen on a variety of Internet technologies. they are often nagging and often accompanied by obsessive-compulsive disorder and are often updated. if they think that the articles are helpful to you, they can pay attention to me. For more information, see "Deep Blue sickle"

I have seen that many PHP websites are still using addslashes and str_replace in anti-SQL injection. Baidu's "PHP anti-injection" is also using them, in practice, I found that even mysql_real_escape_string can be bypassed by hackers. if your system is still using the above three methods, this blog post will be meaningful, to remind all later users to bypass this pitfall.

In consideration of planting trees for future generations rather than digging holes, the PHP and MYSQL versions are provided to avoid "problems" in the future.

It is no longer necessary to use str_replace and various php character replacement functions to prevent injection. this blacklist-based defense has proved to be time-tested.

The following describes how to bypass addslasher and mysql_real_escape_string (Trick ).

Note: although the Trick has been fixed in MYSQL5.5.37-log, it still does not solve the injection problem exactly. in many companies, the system is still using Mysql5.0. I suggest immediate improvement, this is also a very important point I mentioned in "I also talk about several ways for programmers to quickly improve their capabilities.

Note: If you are not sure whether your system has the risk of SQL injection, deploy the following DEMO to your server. if the running result is the same, please refer to the final perfect solution.

MYSQL:

mysql> select version();+---------------------+| version()           |+---------------------+| 5.0.45-community-ny |+---------------------+1 row in set (0.00 sec)mysql> create database test default charset GBK;Query OK, 1 row affected (0.00 sec)mysql> use test;Database changedmysql> CREATE TABLE users (    username VARCHAR(32) CHARACTER SET GBK,    password VARCHAR(32) CHARACTER SET GBK,    PRIMARY KEY (username));Query OK, 0 rows affected (0.02 sec)mysql> insert into users SET username='ewrfg', password='wer44';Query OK, 1 row affected (0.01 sec)mysql> insert into users SET username='ewrfg2', password='wer443';Query OK, 1 row affected (0.01 sec)mysql> insert into users SET username='ewrfg4', password='wer4434';Query OK, 1 row affected (0.01 sec)＝

PHP:

 
Result:
PHP version: 5.2.5int(3)string(6) "latin1"int(3)string(6) "latin1"int(0)string(3) "gbk" 
We can see that, whether using addslashes or mysql_real_escape_string, I can exploit the encoding vulnerability to input any password to log on to the server for injection attacks !!!! (I will not talk much about the attack principle. if you are interested, you can study the single-byte and multi-byte character encoding issues)
Note: The third reason why mysql_real_escape_string can defend against injection is that mysql_escape_string itself cannot determine the current encoding. you must specify the server-side encoding and client-side encoding at the same time to prevent injection of encoding problems. Although it can prevent SQL injection to a certain extent, we recommend the following perfect solution.
The perfect solution is to use PDO and MYSQLi with the Prepared Statement mechanism to replace mysql_query (note: mysql_query has been deprecated since PHP 5.5.0 and will be removed in the future ):
PDO:
$pdo = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');$stmt->execute(array('name' => $name));foreach ($stmt as $row) {    // do something with $row}

MYSQLi:
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');$stmt->bind_param('s', $name);$stmt->execute();$result = $stmt->get_result();while ($row = $result->fetch_assoc()) {    // do something with $row}

                                                
The above introduces PHP anti-SQL injection and does not use addslashes and mysql_real_escape_string. It includes some content and hopes to be helpful to friends who are interested in PHP tutorials.