Should I avoid injecting "%" into a query where I use "LIKE"?

3

I have a page where I consult the user through the name. The query aims to use LIKE to capture the user name from the first letter onwards.

So I do the query this way

SELECT * FROM usuarios WHERE nome LIKE 'Gu%'

I'm using Laravel 3 to make this query, so the query looks something like this.

 $search = trim(Input::get('search'));

 Usuario::where('nome', 'LIKE', "{$search}%")->get();

The problem I noticed is that if the user places the "%gu" text in the form field, instead of searching for Gustavo , Gusmão and Gumercindo , it would also search for Al[gu]sto , Aldalberto [Gu]smão . And it's not that intention.

This happens because the search I'm doing would generate the following results:

Pesquisa: wal  => wal%
Pesquisa: %wal => %wal%

But what if the user really wants to search for things like 30 % ? This would be a problem because of LIKE use % as a representation of "anything".

  • With I could solve this, in LIKE or % , so my query will not be changed. And, instead, if the user wants to find something that contains PHP , it finds the character registered in the table? That is, is there any way to escape MYSQL in a query with % ?

asked by anonymous 17.09.2015 / 17:07

1 answer

5

You can replace % with \% . Taking advantage of it, also replace _ with \_ :

$search = trim(Input::get('search'));
$search = str_replace(['_', '%'], ['\_', '\%'], $search)
    
17.09.2015 / 18:13