Escaping MySQL Wild Cards

Searching using MySQL: How to escape wildcards

First, don't use LCASE with LIKE unless you're using a case-sensitive locale (which is not the default with MySQL).

As far as escaping those characters, just prefix them with a \ character, so foo%bar becomes foo\%bar.

(It's been a while since I've used Java, but might this work:)

searchString.replaceAll('%', '\\\\%').replaceAll('_', '\\\\_')

(or using a regex):

Regex r = new Regex('(?:%|_)', '\\\\$&');
r.replaceAll(searchString)

As far as preventing SQL injection, just bind the variable as normal:

WHERE LCASE(Items.Name) LIKE ?

And create the bound string like:

'%' + searchString.replaceAll('%', '\\\\%').replaceAll('_', '\\\\_') + '%'

Escaping MySQL wild cards

_ and % are not wildcards in MySQL in general, and should not be escaped for the purposes of putting them into normal string literals. mysql_real_escape_string is correct and sufficient for this purpose. addcslashes should not be used.

_ and % are special solely in the context of LIKE-matching. When you want to prepare strings for literal use in a LIKE statement, so that 100% matches one-hundred-percent and not just any string starting with a hundred, you have two levels of escaping to worry about.

The first is LIKE escaping. LIKE handling takes place entirely inside SQL, and if you want to turn a literal string into an literal LIKE expression you must perform this step even if you are using parameterised queries!

In this scheme, _ and % are special and must be escaped. The escape character must also be escaped. According to ANSI SQL, characters other than these must not be escaped: \' would be wrong. (Though MySQL will typically let you get away with it.)

Having done this, you proceed to the second level of escaping, which is plain old string literal escaping. This takes place outside of SQL, creating SQL, so must be done after the LIKE escaping step. For MySQL, this is mysql_real_escape_string as before; for other databases there will be a different function, of you can just use parameterised queries to avoid having to do it.

The problem that leads to confusion here is that in MySQL uses a backslash as an escape character for both of the nested escaping steps! So if you wanted to match a string against a literal percent sign you would have to double-backslash-escape and say LIKE 'something\\%'. Or, if that's in a PHP " literal which also uses backslash escaping, "LIKE 'something\\\\%'". Argh!

This is incorrect according to ANSI SQL, which says that: in string literals backslashes mean literal backslashes and the way to escape a single quote is ''; in LIKE expressions there is no escape character at all by default.

So if you want to LIKE-escape in a portable way, you should override the default (wrong) behaviour and specify your own escape character, using the LIKE ... ESCAPE ... construct. For sanity, we'll choose something other than the damn backslash!

function like($s, $e) {
return str_replace(array($e, '_', '%'), array($e.$e, $e.'_', $e.'%'), $s);
}

$escapedname= mysql_real_escape_string(like($name, '='));
$query= "... WHERE name LIKE '%$escapedname%' ESCAPE '=' AND ...";

or with parameters (eg. in PDO):

$q= $db->prepare("... WHERE name LIKE ? ESCAPE '=' AND ...");
$q->bindValue(1, '%'.like($name, '=').'%', PDO::PARAM_STR);

(If you want more portability party time, you can also have fun trying to account for MS SQL Server and Sybase, where the [ character is also, incorrectly, special in a LIKE statement and has to be escaped. argh.)

Escape a '%' in a mySQL query being executed via python with LIKE and % wildcards

You don't need to escape the % instances in the string that is to the right of the % in the generation of the query so:

match = r"%exam\%ple%"  # Use r string to avoid having to double \s
query = """
SELECT *
FROM table
WHERE name LIKE "%s"
""" % (match)
print(query) # as a check

Results in:

SELECT *
FROM table
WHERE name LIKE "%exam\%ple%"

Also note that you can effectively escape, i.e. make literal, % characters by doubling them, i.e. %d = insert number but %%d = insert "%d" however you will need to double for each % operation that you are performing.

A better alternative would be to use the string format command which is available in both python 3.4+ any python 2.7. This uses {} as a marker for insertion points, (it is classed as a mini-language), and will not touch your % signs.

In [2]: "{} == {}".format(1,2)
Out[2]: '1 == 2'

In [3]: match = r"%exam\%ple%" # Use r string to avoid having to double \s

In [4]: query = """
...: SELECT *
...: FROM table
...: WHERE name LIKE "{}"
...: """.format(match)

In [5]: print(query)

SELECT *
FROM table
WHERE name LIKE "%exam\%ple%"

Escaping wildcards in LIKE

You can use the escape syntax

You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character.

So you can do:

select * from property where name like '%\_%' escape '\';

NAME VALUE
-------------------- --------------------------------------------------
max_width 90

select * from property where name like '%\%%' escape '\';

NAME VALUE
-------------------- --------------------------------------------------
taxrate% 5.20

SQL LIKE query: escape wildcards

Yes. The simplest way is to use =, if that is appropriate.

Otherwise, the LIKE syntax includes an escape character. In your example, $ doesn't appear anywhere, so you could do:

where x like replace(y, '%', '$%') escape '$'

I believe that in all databases, the default is \, so you could also do:

where x like replace(y, '%', '\%') 

However, I prefer using the escape keyword so the intention is really clear.

Is a mysql LIKE statement with an escaped string containing unescaped wildcards '%' (percent) or '_' (underscore) vulnerable?

Could someone exploit this?

For SQL-injection? No.

For an easter-egg like behavior? Probably. In this case, if you don't want let your users use wildcards in this search, you can do 2 things:

  1. proper escape wildcards (and the escape character),

    str_replace(array('\\', '%', '_'), array('\\\\', '\\%', '\\_'), $str);
    // or:
    str_replace(array('|', '%', '_'), array('||', '|%', '|_'), $str);
    // with SELECT * FROM users WHERE username LIKE ? ESCAPE '|'
  2. or use LOCATE(substr, str) > 0 to find exact matches.

should I escape wildcards in a LIKE sql query?

Using PDO:

$pdo = new PDO(/* db info */);

$original = $_POST['movie_title'];
$wildcarded = '%'.$original.'%';
$stmt = $pdo->prepare('SELECT movie FROM movies WHERE title LIKE :var');
$stmt->bindParam(':var', $wildcarded);
$stmt->execute();
// fetching and stuff...

How to programmatically escape wildcards in SQL LIKE?

Need to insert on each special character a escape character. As you said, you can stack a bunch of REPLACE functions:

DECLARE @Prefix VARCHAR(100) = 'My%Prefix_'

DECLARE @Replaced VARCHAR(100) = REPLACE(REPLACE(REPLACE(REPLACE(@Prefix, '%', '\%'), '^', '\^'), '_', '\_'), '[', '\[')

SELECT
Original = @Prefix,
Translated = @Replaced

Result:

Original    Translated
My%Prefix_ My\%Prefix\_

Then use the ESCAPE clause for the LIKE to tell the engine that your character is the escape indicator:

SELECT * 
FROM Logs
WHERE IDString LIKE @Replaced + '[-]%'
ESCAPE '\'

You could also create a scalar function with the replaces:

CREATE FUNCTION dbo.ufnEscapeLikeSpecialCharacters(@Input VARCHAR(100), @EscapeCharacter CHAR)
RETURNS VARCHAR(100)
AS
BEGIN
RETURN REPLACE(REPLACE(REPLACE(REPLACE(@Input, '%', @EscapeCharacter + '%'), '^', @EscapeCharacter + '^'), '_', @EscapeCharacter + '_'), '[', @EscapeCharacter + '[')
END

And then use it like:

DECLARE @Prefix VARCHAR(100) = 'My%Prefix_'

SELECT *
FROM Logs
WHERE IDString LIKE dbo.ufnEscapeLikeSpecialCharacters(@Prefix, '\') + '[-]%'
ESCAPE '\'


Related Topics



Leave a reply



Submit