Geek Answers Handbook

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

In Laravel 4, I want to protect some complex database queries from SQL injection. These queries use a combination of 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 read the Chris Fidao tutorial that you can pass an array of bindings to the select () method and therefore prevent SQL injections correctly using prepared statements. For instance:

$results = DB::select(DB::raw("SELECT :field FROM users WHERE id=1"), 
               ['field' => $field]
           ));

This works, but the example puts the entire query in the original expression. It does not combine the query builder with DB :: raw (). When I try something like this 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 a string, the array is given

SQL- , DB:: raw()?

+4
2

, setBindings(), :

$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();
+2

PDO . , SELECT.

mysqli_real_escape_string - SQL.

( ) users , , .

$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();
+2

More articles:


All Articles