我应该如何在此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).
推荐答案
// 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屋!