18

How can I escape a LIKE clause in Laravel/Eloquent? e.g.,

$search = Input::query('sSearch', ''); if($search !== '') { $paginatedBookings->where('first_name', 'LIKE', '%' . $search . '%'); }

If $search contains a % or _ they need to be escaped.

Improve this question
2
  • Your $search variable will always/only contain these two wildcards? Commented Mar 30, 2014 at 20:17
  • 1
    @RahilWazir: No..? It will contain whatever the user types in. They shouldn't be allowed to search for wildcards. I just want to put a backslash in front of them. It's a one-liner but I was hoping for something database-agnostic. Commented Mar 30, 2014 at 20:18

4 Answers 4

Reset to default
32

The other answer forgets about escaping the escape character itself, here is a more robust solution:

/** * Escape special characters for a LIKE query. * * @param string $value * @param string $char * * @return string */ function escape_like(string $value, string $char = '\\'): string { return str_replace( [$char, '%', '_'], [$char.$char, $char.'%', $char.'_'], $value ); }
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This may not be 100% accurate in case of escape char itself and MySQL at least. In docs: 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. (...) 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.
3

Temporary solution:

$search = Input::query('sSearch', ''); if($search !== '') { $escSearch = Util::escapeLike($search); $paginatedBookings->where('first_name', 'LIKE', '%' . $escSearch . '%'); $paginatedBookings->orWhere('last_name', 'LIKE', '%' . $escSearch . '%'); } class Util { public static function escapeLike($str) { return str_replace(['\\', '%', '_'], ['\\\\', '\%', '\_'], $str); } }

reference

I was hoping for something database-agnostic and more robust. I think you can change the escape char in MySQL, although I don't know why you would.

Improve this answer

1 Comment

@RahilWazir Double slashes aren't necessary when used in single quotes.
0

This problem occurs especially when we want to query on a polymorphic related table.As an example, I wanted to find a column value named App\Example\Files by querying with like. But it failed because of the backslash.

While testing, I found that we need "4 backslashes" for the Laravel side and for the mysql side. For the query to work properly.

I also verified this with the toSql () method. As a result, the output of the mysql query was like this and it worked:

select `file` from `examples` where file like '%App\\\\Example\\\\Files%'

I wrote a little helper function to do the backslash correction

before:

 DB::table('examples') ->select('file') ->whereRaw("state like '%App\Example\Files%'") ->toSql();

incorrect output:

select `file` from `examples` where file like '%App\Example\Files%'

after

function handle_backslash($value) :string { return str_replace('\\', '\\\\\\\\', $value); } DB::table('examples') ->select('file') ->whereRaw("file like '%" . handle_backslash('App\Example\Files') . "%'") ->toSql();

correct output:

select `file` from `examples` where file like '%App\\\\Example\\\\Files%'
Improve this answer

2 Comments

It's not just the backslashes that need to be escaped. Please check out the accepted answer. If your value contains % or _ you will also have problems.
@mpen you are right, while still you can update the helper function to assign new properties for this purpose and you can use this for the expression that you mentioned.
0

Considering other answers, have you tried the following?:

// Escape characters `%` and `_`. do { $value = preg_replace('/([^\\\\]|^)([%_])/', '$1\\\\$2', $value, -1, $c); } while ($c > 0); // Input::where($column, 'LIKE', "%$value%")...

In the result we have \%%%test%\% -> \%\%\%test\%\%.

In case of Laravel, for example, we could have a macro even:

use Illuminate\Database\Eloquent\Builder; // ... /** * Make a "WHERE... LIKE" database query with special characters `%` and `_` escaped. * * @see https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like */ Builder::macro('whereLikePlain', function ($column, $value) { do { $value = preg_replace('/([^\\\\]|^)([%_])/', '$1\\\\$2', $value, -1, $c); } while ($c > 0); return $this->where($column, 'LIKE', "%$value%"); });
Improve this answer

6 Comments

Why are you running preg_replace in a loop?
@mpen, to circumvent recursion changing an already modified input. If I am not mistaken, this is how PHP's PCRE algorithm function works under the hood, where a single iteration would result in: %%%% -> %\%%\%. Please check the following: stackoverflow.com/a/6364350/5113030
Oh.. that's because you have this junk /([^\\\\]|^) before it. I assume that's to prevent double-escaping? You shouldn't do that. Just escape the backslashes too: preg_replace('/[\\%_]/', '\\\\$0', '%%%%\\%')
Thank you very much, @mpen! That's an idea indeed! I'll reconsider it.
@mpen , I recalled the rationale behind it. It's to try to guarantee that all the % and _ are escaped in the result, since these were coming from User-space. E.g. In case of input %%%%\\%, with the loop, we have \%\%\%\%\%, but with the technique you shared, we leave it unescaped - \%\%\%\%\\%. More to that the latter would result in the following also: \\%%%%\\% -> \%\%\%\%\\%.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.