addslashes() vs mysqli_real_escape_string()

本来想写一篇 “How to escape string in PHP?”,但是这个题目太大了我驾驭不了,所以还是先从具体的函数讲起。


0. 一点准备工作

create database test;
use test;
create table escape_string (escape_string text);

1. addslashes()

addalshes() 会通过在前面加 \ 的方式对 ‘, “, \, NUL 等特殊字符进行转义。(Returns a string with backslashes before characters that need to be escaped. These characters are single quote (‘), double quote (“), backslash (\) and NUL (the NULL byte).)

但是为了安全考虑,不建议在进行数据库操作时使用 addalshes() 方法,推荐使用数据库对应的转义方法:MySQL 数据库请使用 mysqli_real_escape_string(),PostgreSQL 请使用 pg_escape_literal(), pg_escape_string()。(To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons.)

$mysqli = new \mysqli("127.0.0.1", "root", "", "test");

$mysqli->set_charset('utf8mb4');

$escape_string = addslashes("Let's go!");
$escape_string = addslashes("This is a new line.\n");
$escape_string = addslashes(json_encode(["你好" => "再见?"]));
$escape_string = addslashes(json_encode(["你好" => "再见?"], JSON_UNESCAPED_UNICODE));

$sql = "insert into escape_string(escape_string) values ('{$escape_string}')";

if ($mysqli->query($sql)) {
    echo 'ok';
} else {
    echo $mysqli->error;
}

2. mysqli_real_escape_string()

mysqli_real_escape_string() 会对 NUL (ASCII 0), \n, \r, \, ‘, “, 和 Control-Z 等特殊字符进行转义。

但是有两点需要注意:1. 需要先建立 MySQL 连接才能使用 mysqli_real_escape_string() 方法,2. 需要使用 $mysqli->set_charset(‘utf8mb4’) 方法设置默认编码,不要使用 $mysqli->query(‘set names utf8mb4’) 。

$mysqli = new \mysqli("127.0.0.1", "root", "", "test");

$mysqli->set_charset('utf8mb4');

$escape_string = $mysqli->real_escape_string("Let's go!");
$escape_string = $mysqli->real_escape_string("This is a new line.\n");
$escape_string = $mysqli->real_escape_string(json_encode(["你好" => "再见?"]));
$escape_string = $mysqli->real_escape_string(json_encode(["你好" => "再见?"], JSON_UNESCAPED_UNICODE));

$sql = "insert into escape_string(escape_string) values ('{$escape_string}')";

if ($mysqli->query($sql)) {
    echo 'ok';
} else {
    echo $mysqli->error;
}

3. mysqli_prepare()

$mysqli = new \mysqli("127.0.0.1", "root", "", "test");

$mysqli->set_charset('utf8mb4');

$escape_string = "Let's go!";
$escape_string = "This is a new line.\n";
$escape_string = json_encode(["你好" => "再见?"]);
$escape_string = json_encode(["你好" => "再见?"], JSON_UNESCAPED_UNICODE);

$sql = "insert into escape_string(escape_string) values (?)";

if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("s", $escape_string);
    $stmt->execute();
    echo 'ok';
} else {
    echo $mysqli->error;
}

使用了 addslashes(), mysqli_real_escape_string(),mysqli_prepare() 三种方法对 “Let’s go!”,”This is a new line.\n”,json_encode([“你好” => “再见?”]),json_encode([“你好” => “再见?”], JSON_UNESCAPED_UNICODE) 四个字符串进行了转义,都能正常存入数据库。

对于网上说的 GBK 字符集的漏洞并没有去验证(会影响到 addslashes() 和 mysqli_real_escape_string()),统一使用 UTF-8 编码应该能避免这种问题。但是还是推荐使用 Prepared Statements,不仅能够自动转义 MySQL 语句中的参数,还能兼顾安全问题。


参考:

http://php.net/manual/en/function.addslashes.php

http://php.net/manual/en/mysqli.real-escape-string.php

http://php.net/manual/en/mysqli.prepare.php

https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

 807 total views,  1 views today

Leave a Reply

Your email address will not be published. Required fields are marked *