我应该如何在此LIKE查询中转义字符? [英] How should I escape characters inside this LIKE query?

问题描述

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

I have a field in one of my tables that contains this string:

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

(仅用于课程测试).我尝试了无休止的查询来正确选择此字段，并且当然不返回任何错误，但是我似乎无法正确地做到这一点.

(Only for test purposes ofcourse). I've tried endless of queries to properly select this field, and without returning any errors of course, but I just can't seem to get it right.

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

This is the query I'm using currently:

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

有人能说明我做错了什么吗?还有其他我应该转义的字符('除外)吗?我在任何地方都没有读过它(但是我确实尝试在流行符号前添加反斜杠).

Can anyone shed some light on what it is I'm not doing right? Are there any other characters (other than ') that I should escape? I haven't read about it anywhere... (I did however try adding backslashes before the precent symbols).

推荐答案

来自

因为MySQL在字符串中使用C转义语法(例如，"\n"表示换行符)，所以必须将在LIKE字符串中使用的所有"\"加倍.例如，要搜索"\n"，请将其指定为"\\n".要搜索"\"，请将其指定为"\\\\"；这是因为解析器会一次删除反斜杠，而在进行模式匹配时会再次反斜杠，因此要匹配一个反斜杠.

Because MySQL uses C escape syntax in strings (for example, "\n" to represent a newline character), you must double any "\" that you use in LIKE strings. For example, to search for "\n", specify it as "\\n". To search for "\", specify it as "\\\\"; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

因此，您应该分两个步骤为LIKE运算符转义字符串.

So, you should escape string for LIKE operator in two steps.

在PHP中，可能是这样的:

In PHP it can be like this:

// 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 扩展名.

MySQL extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

使用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准备好的声明，而不是第二步(文字转义):

Or you can use PDO prepared statement, instead of second step (literal escaping):

// 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}%"]);

这篇关于我应该如何在此LIKE查询中转义字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！