从
MySQL Manual开始:
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运算符转义字符串.
在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 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预处理语句,而不是第二步(文字转义):
// 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}%"]);