本来想写一篇 “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
868 total views