php - MySQLi 使用 php 进行查询 - 查询字符串包含单引号和大括号

Question

我正在编写一个 php 脚本，将字典文件放入 Mysql 数据库中。它工作得很好，除了在某些情况下，定义字符串同时包含单引号和多组花括号。这是失败的定义字符串之一。

(n) (1) {sports} carry-back/bringing the ball back to one's own position (in rugby)/(2) {econ} carryback/carrying over a deduction or credit from a prior year to the current year (to reduce income tax)

这是 **MySQLi ** 错误消息:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's own position (in rugby)/(2) econ', {'(n) (1) {sports} carry-back/bringing the ' at line 1

这是有关定义字符串的脚本部分:

$definition = substr($definition_string, 0, $pos);

$definition = substr($definition, 1);

// Escape single quote
$definition = str_replace(["'"], "''" , $definition);

$mysqli->set_charset("utf8");

$result = $mysqli->query("INSERT INTO dict (entry, reading, category, definition, entry_number) VALUES ('$entry', '$reading', '$category', '$definition', '$entry_number')");   

我不明白为什么它会失败，并且错误消息没有多大帮助。有什么想法吗？

Answer 1

我建议您阅读此内容 here 。他们提供了几种不同的方法来保护进入数据库的数据。

以下是多种方法之一:

$result = $mysqli->query("INSERT INTO dict (entry, reading, category, definition, entry_number) VALUES (
'" . $mysqli->escape_string($entry) . "',
'" . $mysqli->escape_string($reading) . "',
'" . $mysqli->escape_string($category) . "',
'" . $mysqli->escape_string($definition) . "',
'" . $mysqli->escape_string($entry_number) . "')");

另一个更有说服力的解决方案:

$stmt = $mysqli->prepare("INSERT INTO dict (entry, reading, category, definition, entry_number) VALUES (
?, ?, ?, ?, ?)");
$stmt->bind_param('sssss', $entry, $reading, $category, $definition, $entry_number);
$stmt->execute();
$result = $stmt->get_result();