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

I may've solved it, but I'm a little confused... I put 2 extra backslashes behind the current 3 backslashes at the end of the escape string, and this works just like I want it to. Why do I have to use 5? Shouldn't it be enough with one (escaping the backslash there already is in the original string), and one escaping the apostrophe that comes right after? — Nisto, Sep 07 '11 at 00:54
Show us the code you're using to test this. Your problem may be related to the fact that PHP unescapes the string for you, then passes that to the database. Using mysql_real_escape_string as nulll suggests instead of trying to escape the characters yourself may fix your problem. — Nate C-K, Sep 07 '11 at 15:34
BTW, I mean the PHP code, not the SQL code that you assume is being sent to MySQL. — Nate C-K, Sep 07 '11 at 15:35

score 16 · Answer 1 · edited Feb 12 '22 at 20:56

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

The mysql_real_escape_string function is no longer supported by PHP as can be found in https://stackoverflow.com/questions/13856639/mysql-real-escape-string-is-undefined — Eaton Emmerich, Feb 13 '18 at 16:44
This approach below is probably superior as it avoids backslash hell. https://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards — Andrew, Jul 18 '18 at 09:07
This question appears quite high in search results for escaping % while using pymysql query formatting. I know this doesn't answer the question directly, but the answer is: double your %. — aJetHorn, Apr 05 '22 at 21:03
@Modder Did you test it? I checked $string = '\\'; for column value in DB: 2\1. And it didn't find that. — , Jun 20 '23 at 09:05

score 1 · Answer 2 · answered Sep 06 '11 at 23:40

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

Don't we also need to escape the backslash itself, in case someone includes that in their search query? — bdsl, May 01 '15 at 11:26

score 0 · Answer 3 · answered Jun 26 '23 at 08:50

In some cases it's simpler to use LOCATE: https://www.w3schools.com/sql/func_mysql_locate.asp

I can't say for sure what is faster: MySQL LIKE vs LOCATE

ORM is not a problem too:

For 99% projects LOCATE would be better solution than LIKE. You wouldn't have headache with special characters. The main reason why do you need LIKE is just a full text search and if you use LIKE for that purpose then it means you don't need something special for that.

score -1 · Answer 4 · answered Jun 27 '21 at 10:11

If that field that are you comparing with (the pattern), is not a constant (like the one which you've specified: %!"#¤%&/()=?...), you can escape it directly in MySQL like this:

SELECT * FROM `mytable` WHERE `column` LIKE REGEXP_REPLACE(pattern, '([%_])', '\\\\$1');

(where pattern can be either another column: column2, a MySQL variable, or an expression)

This can be also useful in a stored procedure/function.

score -1 · Answer 5 · answered Sep 06 '11 at 23:40

-1

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?

answered Sep 06 '11 at 23:40

Robert Martin

16,759
15
61
87

How should I escape characters inside this LIKE query?

5 Answers5

Use MySQLi

Use PDO

Linked

Related