How should I sanitize Laravel query if using whereRaw()?

Question

I am using Laravel 5.1 and the Eloquent ORM in a project. I need to query the database to find people using their name or part of the name.

If a person is named John Doelington, I want all the search terms john, doelin, john doe to find that John Doelington. However, the first name and last name are stored in different columns. Currently I am using this query:

$people = Person::whereRaw(
                     'CONCAT(first_name, \' \', last_name) 
                      LIKE \'%'.$search.'%\'')
                 ->get();

Looking at the logs I see a lot of errors - every time there is a ' symbol it breaks the query. It seems that I am outside the safe zone with the whereRaw() function and someone could pass god knows what to SQL.

What should I do here?

Should I sanitize the input manually? If so, what should I do? Remove the ' and what else? And what if query actually has to contain the ' symbol?

Maybe there is a safer way to perform this query? Can I use Eloquent but pass parameters to query in the PDO style when using whereRaw()?

If it makes difference, I am using MySQL for the database.

I am aware that I could make a concatenated column for the full name, however I am looking for an Eloquent solution first before using this fallback.

Answer 1

From the source code, it's clear that whereRaw's second argument accepts array bindings, so you can write your query as:

$search = "%{$search}%";
$people = Person::whereRaw("(CONCAT(first_name,' ',last_name) like ?)", [$search])
                 ->get();

You can also try the Full-Text search.