我应该如何转义此 LIKE 查询中的字符？

Question

我的一个表中有一个字段包含此字符串：（

!"#¤%&/()=?´`?=)(/&%¤#"!\'\'"'

当然仅用于测试目的）。我尝试了无数的查询来正确选择该字段，当然不会返回任何错误，但我似乎无法正确选择。

这是我当前正在使用的查询：

SELECT * FROM mytable WHERE `column` LIKE '%!"#¤%&/()=?´`?=)(/&%¤#"!\\\'\\\'"\'%'

任何人都可以阐明我做错了什么吗？是否还有其他字符（' 除外）需要转义？我没有在任何地方读过它......（但是我尝试在前面的符号之前添加反斜杠）。

Answer 1

来自 MySQL 手册：

MySQL 在字符串中使用 C 转义语法（例如，\n 表示换行符）。如果您希望 LIKE 字符串包含文字 \，则必须将其加倍。 （除非启用 NO_BACKSLASH_ESCAPES SQL 模式，在这种情况下不使用转义字符。）例如，要搜索 \n，请将其指定为 \\ n。要搜索 \，请将其指定为 \\\\；这是因为反斜杠被解析器剥离一次，并在进行模式匹配时再次剥离，留下一个反斜杠进行匹配。

因此，您应该分两步转义 LIKE 运算符的字符串。

在 PHP 中，它可以是这样的：

// Your search string, for example, from POST field
$string = $_POST['column'];

// First step - LIKE escaping
$string = str_replace(array('\\', '_', '%'), array('\\\\', '\\_', '\\%'), $string);

// Second step - literal escaping
$string = mysql_real_escape_string($string);

// Result query
mysql_query("SELECT * FROM `table` WHERE `column` LIKE '%".$string."%'");

---

更新：

MySQL 扩展在 PHP 5.5.0 中已弃用，并在 PHP 7.0.0 中被删除。相反，应使用 MySQLi 或 PDO_MySQL 扩展。

使用 MySQLi

// Connect to database
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

// Your search string, for example, from POST field
$string = $_POST['column'];

// First step - LIKE escaping
$string = str_replace(['\\', '_', '%'], ['\\\\', '\\_', '\\%'], $string);

// Second step - literal escaping
$string = $mysqli->real_escape_string($string);

// Result query
$mysqli->query("SELECT * FROM `table` WHERE `column` LIKE '%{$string}%'");

使用 PDO

// Connect to database
$conn = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');

// Your search string, for example, from POST field
$string = $_POST['column'];

// First step - LIKE escaping
$string = str_replace(['\\', '_', '%'], ['\\\\', '\\_', '\\%'], $string);

// Second step - literal escaping
$string = $conn->quote($string);

// Result query
$conn->query("SELECT * FROM `table` WHERE `column` LIKE '%{$string}%'");

或者您可以使用 PDO 准备好的语句，而不是第二步（文字转义）：

// Connect to database
$conn = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');

// Your search string, for example, from POST field
$string = $_POST['column'];

// First step - LIKE escaping
$string = str_replace(['\\', '_', '%'], ['\\\\', '\\_', '\\%'], $string);

// Prepare a statement for execution
$statement = $conn->prepare("SELECT * FROM `table` WHERE `column` LIKE ?");

// Execute a prepared statement
$statement->execute(["%{$string}%"]);

Answer 2

不清楚您想要获得什么以及出了什么问题。

顺便说一句，如果你想保护你的查询免受 SQL 注入，你应该使用 mysql_real_escape_string
http://dev.mysql.com/doc /refman/5.0/en/mysql-real-escape-string.html

假设你在 PHP

$query = "SELECT * FROM mytable WHERE `column` LIKE '".mysql_real_escape_string($whatever)."'"

但你必须记住 LIKE 运算符有他自己的特殊字符（通配符）
http://dev.mysql.com/doc /refman/5.0/en/string-comparison-functions.html#operator_like

% 匹配任意数量的字符，甚至零个字符
_ 精确匹配一个字符

因此，如果你想阻止它们的魔力，这个字符必须用反斜杠转义

假设你在 PHP 中，我会这样做

// This removes magic on LIKE wildchars
$whatever = preg_replace('#(%|_)#', '\\$1', $input);

// This secures the query from sql injection 
// and hads the trailing % wildchars to the search string
$query = "SELECT * FROM mytable WHERE `column` LIKE '%".mysql_real_escape_string($whatever)."%'"

Answer 3

在某些情况下，使用 LOCATE 更简单： https://www.w3schools.com/sql/func_mysql_locate .asp

我不能确定哪个更快：MySQL LIKE 与 LOCATE

ORM 也不是问题：

• Doctrine - https://www.doctrine-project.org/projects/doctrine-orm/en/2.15/reference/dql-doctrine-query-language.html#dql-functions
• Hibernate - https://docs.oracle.com/html/E24396_01/ejb3_langref.html#ejb3_langref_string_fun

对于 99% 的项目，LOCATE 比 LIKE 是更好的解决方案。您不会因特殊字符而头痛。您需要 LIKE 的主要原因只是全文搜索，如果您使用 LIKE 来实现此目的，则意味着您不需要为此进行特殊的操作。

Answer 4

您使用 PHP 吗？如果是这样，你可以尝试这样的事情：

$a = mysql_real_escape_string('%!"#¤%&/()=?´`?=)(/&%¤#"!\'\'"\'%');
$query_string = "SELECT * FROM mytable WHERE `column` LIKE '$a'";

这能解决你的问题吗？

Answer 5

如果您要比较的字段（模式）不是常量（如您指定的常量：%!"#¤%&/()=?... ），您可以像这样直接在 MySQL 中转义它：（

SELECT * FROM `mytable` WHERE `column` LIKE REGEXP_REPLACE(pattern, '([%_])', '\\\\$1');

其中 pattern 可以是另一列：column2、MySQL 变量或表达式）

这也很有用在存储过程/函数中。