How should I escape characters inside this LIKE qu

问题:

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).

回答1:

From 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.

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

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."%'");

---

UPDATE:

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.

Use 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}%'");

Use 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}%'");

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

回答2:

Is not clear what you are trying to obtain and what is going wrong.

By the way, if you want to protect your query from SQL injection you should use mysql_real_escape_string
http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html

Assuming that you are in PHP

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

But you have to remember that LIKE operator has his own special chars (wildchars)
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

% Matches any number of characters, even zero characters
_ Matches exactly one character

So this chars must be escaped with backslash if you want to stop their magic

Assuming that you are in PHP I would do

// 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)."%'"

回答3:

Are you using PHP? If so, you may try something like:

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

Would that solve your problem?