php - 使用LIKE子句和NO_BACKSLASH_ESCAPES模式在mysqli prepare()中转义％号

Question

问题:

注意: NO_BACKSLASH_ESCAPES模式已启用。

我正在使用mysqli准备的语句来查询数据库。目标代码如下:

$conn = new mysqli('localhost', 'root', 'mypass', 'mydb');
$stmt = $conn->prepare('select * from `table` where `data` like ?;');

现在，假设我手动(从mysql客户端而不是从php)插入3条记录，它们的文字值分别为 JDxDD， JD_DD和 JD\DD:

INSERT INTO `table` SET `data` = 'JDxDD';
INSERT INTO `table` SET `data` = 'JD_DD';
INSERT INTO `table` SET `data` = 'JD\DD';

现在，考虑一下:

$stmt = $conn -> prepare('SELECT * from `table1` where `data` like ?;');
$key = 'JD_DD';
$stmt -> bind_param('s', $key);
$stmt-> execute();

如预期的那样，这将返回所有3行。现在，我只想选择一个值为 JD_DD的代码(即排除 JDxDD和 JD\DD)。因此，作为 here的答案，我需要使用 _的 escape关键字对 $key = 'JD\_DD';进行转义。

但是，这:

$stmt = $conn -> prepare('SELECT * from `table1` where `data` like ? escape \'\\\';');
/*
first and last \ are for putting the ' quote,
middle 2 are for putting a \ as a part of string.
*/

计算$ stmt为 false，调用 $conn->error会出现此错误:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''\'' at line 1

以下两个代码都提供与上面相同的Syntax error:

(1)我尝试切换到“以封装查询，如下所示:

$stmt = $conn -> prepare("SELECT * from `table1` where `data` like ? escape '\\';");

(2)这是:

$stmt = $conn -> prepare("SELECT * from `table1` where `data` like ? escape '\';");

请注意，尽管 $key = 'JD\_DD';可以与 LIKE运算符(而不是 =)一起有效地使用，但这只是一个示例。 $key可以很复杂，例如 'JD\_D%\_X%';等。

我的努力:

以下是 产生所需输出的一些代码:

(1)使用其他转义字符:

$stmt = $conn -> prepare("SELECT * from `table1` where `data` like ? escape '|';");
$key = 'JD|_DD';
$stmt->bind_param('s', $key);

正如人们所期望的那样，这可以正常工作。

(2)完全省略 escape单词:

$stmt = $conn -> prepare('SELECT * from `table1` where `data` like ?;');
$key = 'JD\_DD';
$stmt->bind_param('s', $key);

这将产生所需的 JD_DD输出。 为什么？ 即使使用 NO_BACKSLASH_ESCAPES MODE ENABLED ，为什么 \仍可以用作转义符？

(3)以下内容也适用:

$stmt = $conn -> prepare("SELECT * from `table1` where `data` like ? escape '\\\';");
$key = 'JD\_DD';
$stmt->bind_param('s', $key);

(4)以下内容也是如此:

$stmt = $conn -> prepare("SELECT * from `table1` where `data` like ? escape '\\\\';");
$key = 'JD\_DD';
$stmt->bind_param('s', $key);

(3)和(4)产生相同的输出。同样， 为什么？

(5)另外，我可以关闭NO_BACKSLASH_ESCAPES模式，但这似乎更像是一种解决方法，而不是解决方案。

mariadb docs说:

If you need to match the characters _ or %, you must escape them. By default, you can prefix the wildcard characters the backslash characer \ to escape them. The backslash is used both to encode special characters like newlines when a string is parsed as well as to escape wildcards in a pattern after parsing. Thus, to match an actual backslash, you sometimes need to double-escape it as "\\\\".

To avoid difficulties with the backslash character, you can change the wildcard escape character using ESCAPE in a LIKE expression. The argument to ESCAPE must be a single-character string.

此外， this SO post显示了一个在MariaDB 10.1.22中修复的错误。我正在使用MariaDB 10.1.13，该错误已在我的版本中修复。 (即 select 'a%b' like '%\%\%';按预期返回 0。)

我还经历了 this mysql bug。

非常感谢您理解此问题的性质和起源以及/或者在启用NO_BACKSLASH_ESCAPES模式的情况下正确编码 like子句的任何帮助。

另外，这是我的第一个SO问题，因此我尝试尽可能地提供丰富信息和适当信息。任何对表达我的问题的更好方法的批评家/建议也应受到赞赏。

谢谢!

编辑:

仅仅使用 =而不是 LIKE将无济于事。根据用户的偏好，可能会有通配符。例如，如果用户希望根据他的输入(例如 'J%D')获取所有相关建议，则该查询将变为 WHERE data LIKE '%J\%D%';。如果用户只想获取他输入的内容，那么我可以肯定地使用 =代替 LIKE。

编辑2:

这是我做过的一些测试:

使用NO_BACKSLASH_ESCAPES模式已禁用

(1) INSERT INTO table set name = 'abc\def';
这将按预期插入 abcdef。

(2) INSERT INTO table set name = 'xyz\\pqr';
这将按预期插入 xyz\pqr。

(3) SELECT * FROM table where name = 'abc\def';
这将按预期返回 abcdef。

(4) SELECT * FROM table where name = 'abc\\def';
这将返回0行，按预期方式。

(5) SELECT * FROM table where name = 'xyz\pqr';
这将返回0行，按预期方式。

(6) SELECT * FROM table where name = 'xyz\\pqr';
这将按预期返回 xyz\pqr。

(7) SELECT * FROM table where name LIKE 'abc\def';
这将按预期返回 abcdef。

(8) SELECT * FROM table where name LIKE 'abc\\def';
这将返回 abcdef。 不可预期的。 (8)是否仅在存在 abc\def时才显示？

(9) SELECT * FROM table where name LIKE 'xyz\pqr';
这将返回0行，按预期方式。

(10) SELECT * FROM table where name LIKE 'xyz\\pqr';
这将返回0行。 不可预期的。 (10)是否应该显示 xyz\pqr？

现在以NO_BACKSLASH_ESCAPES模式启用
插入在(1)和(2)处的数据也将保留。

(11) INSERT INTO table set name = 'abc\def';
这将按预期插入 abc\def。

(12) INSERT INTO table set name = 'xyz\\pqr';
这将按预期插入 xyz\\pqr。

(13) SELECT * FROM table where name = 'abc\def';
这将按预期返回 abc\def(插入11)。

(14) SELECT * FROM table where name = 'abc\\def';
这将返回0行，按预期方式。

(15) SELECT * FROM table where name = 'xyz\pqr';
这将按预期返回 xyz\pqr(插入2)。

(16) SELECT * FROM table where name = 'xyz\\pqr';
这将按预期返回 xyz\\pqr(插入12)。

(17) SELECT * FROM table where name LIKE 'abc\def';
这将返回 abcdef(插入(1))。 不可预期的。启用此模式后，我希望它按字面意义使用 \(除非与 escape = '\'一起使用)。预期结果是 abc\def(插入11)。

(18) SELECT * FROM table where name LIKE 'abc\\def';
这将返回 abc\def(在(11)处插入)。 不可预期的。启用此模式后，我希望它能同时使用 \\。预期结果为0行，因为没有像 abc\\def这样的数据。

(19) SELECT * FROM table where name LIKE 'xyz\pqr';
这将返回0行。 不可预期的。启用此模式后，我希望它采用字面意义上的 \。预期结果是 xyz\pqr(插入2)。

(20) SELECT * FROM table where name LIKE 'xyz\\pqr';
这将返回 xyz\pqr(在(11)处插入)。 不可预期的。启用此模式后，我希望它能同时使用 \\。预期结果是 xyz\\pqr(插入12)。

因此，总而言之，NO_BACKSLASH_ESCAPES模式按预期方式在 INSERT和 WHERE col = value中进行操作-设置为ON时，逐字逐字地获取每个 \。
但是，通过 WHERE col LIKE value，它变得如上所示变得奇怪。

对于 LIKE子句，即使将模式设置为ON，也不按字面意义使用 \。

我现在强烈怀疑我误解了有关 LIKE子句的一些非常基本的知识。任何澄清都非常欢迎!

Answer 1

只需使用=而不是LIKE ??

仅当需要LIKE和/或%的通配符操作时，才应使用_。

修改后的案例

要求:

用户可以输入_，％或\-测试是按字面意义加上

用户输入是字符串

的开头

解决方案1避免处理 LIKE:

WHERE LEFT(data, CHAR_LENGTH($input)) = $input

解决方案2在不更改 LIKE的情况下使 NO_BACKSLASH_ESCAPES工作:

首先，将每个\增加四倍，第二个在每个_或％之前加一个\。然后添加 %并执行 LIKE。

如果要求是$ input必须位于 data中的任何位置，则

WHERE INSTR($input, data)