How should I escape characters inside this LIKE query?

Question

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

Answer 1

From MySQL Manual:

MySQL uses C escape syntax in strings (for example, \n to represent the newline character). If you want a LIKE string to contain a literal \, you must double it. (Unless the NO_BACKSLASH_ESCAPES SQL mode is enabled, in which case no escape character is used.) 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}%"]);