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

Question

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()?

Answer 1

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();