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

Asked

Viewed 130 times

3

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

So I consult this way

SELECT * FROM usuarios WHERE nome LIKE 'Gu%'

I’m using the Laravel 3 to make this consultation, then the consultation is more or less like this.

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

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

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

This is because the research I do, would generate the following results:

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

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

  • Taking the issue of functionality, there is also another problem in allowing the user to type, in searches where use LIKE, the character %?

  • How could I resolve this, in PHP or MYSQL, for my query not to be changed. And, instead, if the user wants to find something that contains %, it finds the character registered in the table? That is, there is some way to escape the % in consultation with LIKE?

php mysql operator-like
  • 1

    And if you replace the % for '' in your input, didn’t solve the problem? Now if it is the best solution I don’t know. Or else don’t let the user press the character %

  • @Cesarmiguel, but what if the user really wants to search there in the bank something like "30% discount"? Our system uses this a lot!

  • I think I get it, you want him to search anyway for %, does not assign the value of the % in the like, that is?

  • Yes. Both the removal solution and the escape solution are good. But the best solution is to escape. Best of all is to have a solution :)

  • 1

    depends on the case... in the user name query example, you don’t need special characters because names are alphanumeric, so pass a filter in the user name before mounting the query.. in the other case of the "30% discount", it’s kind of weird that someone seeks it freely.. normally the tables are normalized so that the value 30 is not mixed with texts.. but in case of searching in texts, just escape the reserved characters of SQL.. simple like this.. all business logic issue.

  • You can escape % in a like.

  • @wallaceMaxters I didn’t quite understand the question, let’s see if that’s what I got. gu the condition should stay LIKE 'gu%'. In case he comes to type %gu should stay LIKE '%gu' or type gu% should stay LIKE 'gu%'?

  • is gluglgu : http://bit.ly/1MeCdE4

Show 3 more comments

1 answer

5


You can replace % for \%. Taking advantage, also replace _ for \_:

$search = trim(Input::get('search'));
$search = str_replace(['_', '%'], ['\_', '\%'], $search)
  • 1

    What the _ does in the LIKE?

  • Also I didn’t understand. Ends up not responding to what @Wallacemaxters wants

  • 1

    _ means any character only one. @Wallacemaxters

  • 1

    @Cesarmiguel, so it’s important he put the _, since it is part of something internal LIKE.

  • 1

    It’s all in the mysql documentation, including how to handle the backslashes themselves (such as fetching texts containing them.)

Browser other questions tagged php mysql operator-like

You are not signed in. Login or sign up in order to post.