CODEWITHSUNDEEP
mysqlescapingsql-like
Nisto
Nisto

Reputation: 101

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

Upvotes: 10

Views: 20774

Answers (5)

user2636594
user2636594

Reputation:

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.

Upvotes: 0

Modder
Modder

Reputation: 893

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

Upvotes: 17

Bogdan Velcea
Bogdan Velcea

Reputation: 17

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.

Upvotes: -1

Robert Martin
Robert Martin

Reputation: 17157

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?

Upvotes: -1

nulll
nulll

Reputation: 1603

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

Upvotes: 1

Related Questions