mysql选择带有转义引号的记录 [英] mysql select record with an escaped quote

问题描述

我有一个功能，用户可以通过文本字段搜索记录.输入被放入以下 ​​SQL 查询:

I have a function where users can pull a record searching by a text field. The input is put into the following SQL query:

$db->query("Select * from users where name = '".$db->real_escape_string($posted_var)."' limit 1");

$db 是一个 mysqli 连接对象

$db is a mysqli connection object

工作完美，直到我在名称中有一个 ' .. 然后用户需要手动转义引号以使其匹配...... real_escape_string() 函数不应该为我做这件事?

Works perfect until I have a ' in the name.. then the user is required to manually escape the quote to get it to match........ Shouldn't the real_escape_string() function do that for me?

我有一个测试记录，数据库中的名称是test\'string".

I have a test record where name is "test\'string" in the database.

$posted_var = "test'string"; // no match 
$posted_var = "test\'string"; // match

有人知道为什么 real_escape_string() 不添加反斜杠吗?

Anyone have any idea why real_escape_string() wouldn't add the backslashes?

MySQL 服务器和这个特定的表都是 utf8_unicode_ci.我不知道还有什么可能导致这个......

The MySQL server and this particular table are both utf8_unicode_ci. I don't know what else could cause this....

感谢阅读！

推荐答案

如果 \ 字面上是在 db 记录中的文本内，那么你需要

If the \ is literally inside the text in the db record, then you need

$sql = "SELECT .... name='foo\\bar'";
                             ^^---double escaped

第一个 \ 将被数据库的 sql 解析器剥离.然后剩余的 foo\bar 将用于字段搜索.

The first \ will be stripped off by the DB's sql parser. Then the remaining foo\bar will be used for the field searches.

评论跟进:你没有理解转义的目的:

comment followup: You're not understanding the purpose of escaping:

$name = "Miles O'Brien";
$sql = "SELECT * FROM yourtable WHERE name='" . $db->real_escape_string($name) . "';";

这将产生:

SELECT * FROM yourtable WHERE name='Miles O\'Brien';

这就是真正发送到数据库引擎的内容.当 SQL 解析器得到它时，第一个 \ 转义被删除，数据库将在 name 中寻找 Miles O'Brien> 字段 - 请注意反斜杠不再存在.

and that's what literally gets send to the DB engine. When the SQL parser gets hold of it, that first \ escape is removed, and the DB will be looking for Miles O'Brien in the name field - note that the backslash is no longer there.

由于您的数据库记录字面上包含 Miles O\'Brien，WITH\ 嵌入记录中，您需要双重转义，所以查询变成:

Since your DB record literally is contains Miles O\'Brien, WITH the \ embedded in the record, you need to double escape, so the query becomes:

SELECT * FROM yourtable WHERE name='Miles O\\'Brien';

同样的事情发生:SQL 解析器移除了第一层转义，数据库现在将搜索 Miles O\'Brien.

Same thing happens: The SQL parser removes the first layer of escaping, and the DB will now be searching for Miles O\'Brien.

这篇关于mysql选择带有转义引号的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！