So fügen Sie eine PHP-Variable in eine MySQL-Anweisung ein

Question

Ich versuche, Werte in eine Inhaltstabelle einzufügen. Es funktioniert gut, wenn in VALUES keine PHP-Variablen vorhanden sind. Wenn ich jedoch die Variable $type放在VALUES einfüge, funktioniert es nicht. Was habe ich falsch gemacht?

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) 
     VALUES($type, 'john', 'whatever')");

Answer 1

为了避免SQL注入，插入语句将会是：

$type = 'testing';
$name = 'john';
$description = 'whatever';

$con = new mysqli($user, $pass, $db);
$stmt = $con->prepare("INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $type , $name, $description);
$stmt->execute();

Answer 2

在任何MySQL语句中添加PHP变量的规则是简单明了的：

1. 使用预处理语句

这个规则适用于99%的查询，也适用于你的查询。任何代表SQL数据字面量的变量（或者简单地说，就是一个SQL字符串或者数字），都必须通过预处理语句添加。没有例外。

这种方法包括四个基本步骤：

• 在你的SQL语句中，用占位符替换所有的变量
• 对得到的查询进行准备
• 将变量绑定到占位符上
• 执行查询

下面是如何在所有流行的PHP数据库驱动程序中实现这一点：

使用mysqli添加数据字面量

当前的PHP版本允许你在一次调用中完成准备/绑定/执行：

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description) 
             VALUES(?, ?, 'whatever')";
$mysqli->execute_query($query, [$type, $reporter]);

如果你的PHP版本较旧，则必须显式地执行准备/绑定/执行：

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description) 
             VALUES(?, ?, 'whatever')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $type, $reporter);
$stmt->execute();

这段代码有点复杂，但是关于所有这些操作符的详细解释可以在我的文章如何使用Mysqli运行INSERT查询中找到，以及一个可以大大简化这个过程的解决方案。

对于SELECT查询，你可以使用与上面相同的方法：

$reporter = "John O'Hara";
$result = $mysqli->execute_query("SELECT * FROM users WHERE name=?", [$reporter]);
$row = $result->fetch_assoc(); // 或者 while (...)

但是，如果你的PHP版本较旧，你需要执行准备/绑定/执行的例程，并且还需要调用get_result()方法，以便从中获取一个熟悉的mysqli_result，从中可以按照通常的方式提取数据：

$reporter = "John O'Hara";
$stmt = $mysqli->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $reporter);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc(); // 或者 while (...)

使用PDO添加数据字面量

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description) 
             VALUES(?, ?, 'whatever')";
$stmt = $pdo->prepare($query);
$stmt->execute([$type, $reporter]);

在PDO中，我们可以将绑定和执行部分合并在一起，非常方便。PDO还支持命名占位符，有些人觉得非常方便。

2. 使用白名单过滤

任何其他查询部分，比如SQL关键字、表名或字段名，或者操作符，都必须通过一个白名单进行过滤。

有时我们必须添加一个代表查询的另一部分的变量，比如关键字或标识符（数据库、表或字段名）。这是一个罕见的情况，但最好做好准备。

在这种情况下，你的变量必须与你的脚本中明确写入的值列表进行检查。这在我的另一篇文章根据用户的选择在ORDER BY子句中添加字段名中有详细解释：

这是一个例子：

$orderby = $_GET['orderby'] ?: "name"; // 设置默认值
$allowed = ["name","price","qty"]; // 允许的字段名的白名单
$key = array_search($orderby, $allowed, true); // 看看是否有这个名字
if ($key === false) { 
    throw new InvalidArgumentException("无效的字段名"); 
}

$direction = $_GET['direction'] ?: "ASC";
$allowed = ["ASC","DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) { 
    throw new InvalidArgumentException("无效的ORDER BY方向"); 
}

在这样的代码之后，$direction和$orderby变量可以安全地放入SQL查询中，因为它们要么等于允许的变体之一，要么会抛出错误。

最后要提到的是，标识符也必须根据特定的数据库语法进行格式化。对于MySQL来说，标识符周围应该是backtick字符。所以我们的ORDER BY示例的最终查询字符串应该是：

$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";