Oct 13, 2022

Level 6

How to parameterized a query using LIKE

I am having an issue with the syntax for parameterized a query using LIKE. This works

$data = DB::table('users')
                ->select("users.id",
                    "users.firstname",
                    "users.lastname",
                    "users.email"
                )
                ->whereNotIn('users.id', $usersEnrolled)
                ->where(function ($q) use ($query) {
                    $q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', "%".$query."%")
                        ->orwhere('email', 'LIKE', "%{$query}%");
                })
                ->orderBy('lastname')
                ->take(25)
                ->get();

I dont want to do this. $query is user supplied data.

Ive tried:

 $q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', "%?%",[$query])

But this gives a array to string conversion. If I just use:

 $q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', "%?%",$query)

I get a syntax error.

Any ideas?

rodrigo.pedra

3 years ago

Best Answer

Level 56

Both bindings here are fine:

 $q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', "%".$query."%")
                        ->orwhere('email', 'LIKE', "%{$query}%");

As Eloquent's ->where() will bind the string as a parameter and will pass it to PDO as a prepared statement parameter.

The issue would be if you where doing something like this:

 $q->whereRaw("CONCAT(`firstname`, ' ', `lastname`) LIKE '%{$query}% '")

where you tell the query builder: "hey don't touch this expression, I know what I am doing!" While concatenating the $query parameter manually.

->whereRaw() accepts a bindings array, which I prefer when adding conditions like yours:

 $q->whereRaw("CONCAT(`firstname`, ' ', `lastname`) LIKE ?", ['%' . $query . '%'])

Note that one thing is escaping against SQL Injection attacks, this is properly done by PDO when using prepared statements, and is the case on your code samples and on this last one.

The other is escaping against wildcard usage.

For example: If a user provides this search string: John%Doe, they will be able to add a SQL wildcard character in the middle of their search string on their will.

If you want to prevent this too, you need to escape the search string against its wildcard characters manually. See this example from a package that does that:

https://github.com/spatie/laravel-searchable/blob/da091737d164e59a569345cd45193b9964a55c1d/src/ModelSearchAspect.php#L127-L133

1 like

ctyler

3 years ago

Level 6

@rodrigo.pedra Thank you for the thorough reply. I didn't even consider the wild card escaping.

1 like

rodrigo.pedra

3 years ago

Level 56

@ctyler you're welcome! Hope it helps you out. Have a nice day =)

jlrdw

3 years ago

Level 75

Just example:

"SELECT COUNT(petid) as total FROM dc_pets WHERE petname LIKE :sch";
$params = [':sch' => $petsearch . "%"];

or

$params = [':sch' => "%" . $petsearch . "%"];

FYI basics are covered in the Mysql manual.

1 like

MohamedTammam

3 years ago

Level 51

That part

$q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', "%".$query."%")

And that part

orwhere('email', 'LIKE', "%{$query}%");

Are getting escaped and prevented from SQL injection, don't worry as long as you don't use DB::raw for users inputs.

You can use laravel-debugbar to see the what queries your run: https://github.com/barryvdh/laravel-debugbar

1 like

tykus

3 years ago

Level 104

The parameter marker is simply ?, so you typically would wrap the $query variable something like this:

$q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', '?', ["%{$query}%"])

However, this is nonsense because it is automatically parameterized by the query builder since you are not passing the variable directly into the raw Expression, unlike this approach where manually parameterizing is necessary:

$q->whereRaw("CONCAT(`firstname`, ' ', `lastname`) LIKE ?", ["%{$query}%"])

TLDR; this:

$q->where(DB::raw("CONCAT(`firstname`, ' ', `lastname`)"), 'LIKE', "%{$query}%")

1 like

Please or to participate in this conversation.