Prevent SQL injection for queries that combine the query builder with DB::raw()

laravel, laravel-4

Solution

I discovered the query builder has a method called setBindings() that can be useful in this instance:

$field = 'email';
$id = 1;
$user = DB::table('users')->select(DB::raw(":field as foo"))
        ->addSelect('email')
        ->whereId(DB::raw(":id"))
        ->setBindings(['field' => $field, 'id' => $id])
        ->get();

Problem

In Laravel 4, I want to protect some complex database queries from SQL injection. These queries use a combination of the query builder and DB::raw(). Here is a simplified example: ``` $field = 'email'; $user = DB::table('users')->select(DB::raw("$field as foo"))->whereId(1)->get(); ``` I've read Chris Fidao's tutorial that it is possible to pass an array of bindings to the select() method, and therefore prevent SQL injection correctly, by using prepared statements. For example: ``` $results = DB::select(DB::raw("SELECT :field FROM users WHERE id=1"), ['field' => $field] )); ``` This works, but the example puts the entire query into a raw statement. It doesn't combine the query builder with DB::raw(). When I try something similar using the first example: ``` $field = 'email'; $user = DB::table('users')->select(DB::raw("$field as foo"), ['field' => $field]) ->whereId(1)->get(); ``` ... then I get an error: strtolower() expects parameter 1 to be string, array given What is the correct way to prevent SQL injection for queries that combine the query builder with DB::raw()?

Original source