Don't use addslashes and mysql_real_escape_string anymore to prevent SQL injection in PHP

The blogger is keen on various Internet technologies. He is often wordy and often accompanied by obsessive-compulsive disorder. He updates frequently. If you think the article is helpful to you, you can follow me. Please indicate "Dark Blue Sickle" when reprinting

---

I have seen that many PHP websites still use addslashes and str_replace to prevent SQL injection. Baidu's "PHP Anti-Injection" also uses them, and I found in practice Even mysql_real_escape_string has ways that hackers can bypass it. If your system is still using the above three methods , then my this blog post is meaningful to remind all latecomers to bypass this pit. .

For the sake of planting trees instead of digging holes for future generations, the version information of PHP and MYSQL is given so that the "problem" will no longer be a "problem" in the future.

It goes without saying that str_replace and various PHP character replacement functions are used to prevent injection. This "blacklist" type of defense has been proven to be unable to withstand the test of time.

The following is a method to bypass addslasher and mysql_real_escape_string (Trick).

Note: Although in MYSQL5.5.37 The Trick under -log has been fixed, but it is still not resolved exactly. Injection problem, because many companies' systems are still using Mysql5.0, I suggest making improvements immediately. This is also my "I will also talk about several methods for programmers to quickly improve their capabilities. A very important point mentioned in ".

Note: If you are not sure whether your system is at risk of SQL injection, please deploy the following DEMO to your server, if the running result Same, then 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 changed
mysql> 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:

<?php
echo "PHP version: ".PHP_VERSION."\n";

mysql_connect(&#39;servername&#39;,&#39;username&#39;,&#39;password&#39;);
mysql_select_db("test");
mysql_query("SET NAMES GBK");

$_POST[&#39;username&#39;] = chr(0xbf).chr(0x27).&#39; OR username = username /*&#39;;
$_POST[&#39;password&#39;] = &#39;guess&#39;;

$username = addslashes($_POST[&#39;username&#39;]);
$password = addslashes($_POST[&#39;password&#39;]);
$sql = "SELECT * FROM  users WHERE  username = &#39;$username&#39; AND password = &#39;$password&#39;";
$result = mysql_query($sql) or trigger_error(mysql_error().$sql);

var_dump(mysql_num_rows($result));
var_dump(mysql_client_encoding());

$username = mysql_real_escape_string($_POST[&#39;username&#39;]);
$password = mysql_real_escape_string($_POST[&#39;password&#39;]);
$sql = "SELECT * FROM  users WHERE  username = &#39;$username&#39; AND password = &#39;$password&#39;";
$result = mysql_query($sql) or trigger_error(mysql_error().$sql);

var_dump(mysql_num_rows($result));
var_dump(mysql_client_encoding());

mysql_set_charset("GBK");
$username = mysql_real_escape_string($_POST[&#39;username&#39;]);
$password = mysql_real_escape_string($_POST[&#39;password&#39;]);
$sql = "SELECT * FROM  users WHERE  username = &#39;$username&#39; AND password = &#39;$password&#39;";
$result = mysql_query($sql) or trigger_error(mysql_error().$sql);

var_dump(mysql_num_rows($result));
var_dump(mysql_client_encoding());

Result:

PHP version: 5.2.5
int(3)
string(6) "latin1"
int(3)
string(6) "latin1"
int(0)
string(3) "gbk" 

It can be seen that whether using addslashes or mysql_real_escape_string,I can use coding vulnerabilities to achieve injection attacks that allow me to log in to the server by entering any password! ! ! ! (I won’t go into details about the principle of the attack. Interested students can study the single-byte and multi-byte issues in character encoding)

Note: The reason why the third mysql_real_escape_string can prevent injection is Because mysql_escape_string itself cannot determine the current encoding, you must specify the server encoding and the client encoding at the same time. This can prevent the injection of encoding problems. Although SQL injection can be prevented to a certain extent, the following perfect solution is still recommended.

The perfect solution is to use PDO and MYSQLi with 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 how to prevent SQL injection in PHP. Don’t use addslashes and mysql_real_escape_string anymore. It includes the relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.