How do you parameterize whereRaw() in the query builder?

I have this query:

$query = DB::table('clan_teams')
    ->select('clans.name', 'ibf_members.members_display_name')
    ->leftJoin('clans', 'clan_teams.clan_id', '=', 'clans.id')
    ->leftJoin('ibf_members', 'clans.leader_id', '=', 'ibf_members.id')
    ->whereRaw("(CONCAT(clans.name,' ',ibf_members.members_display_name) like '%:search%')")
    ->get();

The purpose of the CONCAT statement at the bottom is to create a simple multi-faceted searchable string, but I need to parameterize the actual search string I use so that it's safe from SQL injection.

But I can't find any information on how to do this with the query builder. Do I have to do a completely raw query?

bimalshah72

10 years ago

Level 6

@tag Did you try this?

 ->whereRaw("(CONCAT(clans.name,' ',ibf_members.members_display_name) like '%?%')",$searchString);

1 like

ATOM-Group

10 years ago

Level 11

@bimalshah72

Yes, I figured out my problem, you made the same mistake I did:

You can't put the % in the where clause, they have to go around the parameter before binding, like this:

$searchString = '%batman%';
 ->whereRaw("(CONCAT(clans.name,' ',ibf_members.members_display_name) like ?)", [$searchString]);

Also, the second argument apparently must be an array.

11 likes

bimalshah72

10 years ago

Level 6

@tag - yes

If you visit Illuminate\Database\Query\Builder you would find method This might help you

public function whereRaw($sql, array $bindings = [], $boolean = 'and')
    {
        $type = 'raw';

        $this->wheres[] = compact('type', 'sql', 'boolean');

        $this->addBinding($bindings, 'where');

        return $this;
    }

1 like

Please or to participate in this conversation.