Prevent SQL injection for queries that combine the

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

Eloquent uses PDO under the hood to sanitize items. It won't sanitize items added to SELECT statements.

The mysqli_real_escape_string method is still useful for sanitizing SQL strings, however.

Consider also (or instead) keeping an array of valid field names from the users table and checking against that to ensure there isn't an invalid value being used.

$allowedFields = ['username', 'created_at'];

if( ! in_array($field, $allowedFields) )
{
    throw new \Exception('Given field not allowed or invalid');
}

$user = DB::table('users')
            ->select(DB::raw("$field as foo"))
            ->whereId(1)->get();

Answer 2

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