Raw Queries in Laravel

Business logic is often complicated. Because of this, we often need to write our own SQL queries. Luckily, Laravel‘s query builder has the tools we need to safely run such queries.

A key concern when writing our own queries is protecting our application from SQL injection attacks. Normally, the query builder does this for us. However, when we write our own SQL, we need to make sure we don’t inadvertently remove this protection.

Here’s what we want to avoid:

$someVariable = Input::get("some_variable");
$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );

In the above query, we’re directly adding user input into the query without sanitizing it. This leaves us open to attack!

DB::raw() is used to make arbitrary SQL commands which aren’t parsed any further by the query builder. They therefore can create a vector for attack via SQL injection.

Since the query builder is using PDO in the background, we know there is a way to bind parameters to our query so it will sanitize the bound variables.

Now, as you’ve seen, arbitrary (raw) queries are done in the query builder using the DB::select() method. Let’s look at the select() method in Illuminate\Database\Connection to see if it has any way to bind our parameters:

public function select($query, $bindings = array())
{
    return $this->run($query, $bindings, function($me, $query, $bindings)
    {
        if ($me->pretending()) return array();
        // For select statements, we'll simply execute the query and return an array
        // of the database result set. Each element in the array will be a single
        // row from the database table, and will either be an array or objects.
        $statement = $me->getPdo()->prepare($query);
        $statement->execute($me->prepareBindings($bindings));
        return $statement->fetchAll($me->getFetchMode());
    });
}

Perfect! We see above that we can pass an array of bindings to the select() method. This array is bound to the query via the PDO connection.

We can, therefore, change our previous query in a way that sanitizes the user input:

$someVariable = Input::get("some_variable");
$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array(
   'somevariable' => $someVariable,
 ));

Voìla! Safe queries!

Lastly, if you are performing queries which don’t return data, then using a SELECT query will result in errors. For example, if you want to start the auto-increment ID of a MySQL table to something other than zero, we can use the statement method. With statement, we don’t need to use the raw() method:

// Warning: This is a MySQL-specific query
DB::statement( 'ALTER TABLE HS_Request AUTO_INCREMENT=9999' );

The statement method can also accept parameters:

DB::statement( 'ALTER TABLE HS_Request AUTO_INCREMENT=:incrementStart', array('incrementStart' => 9999) );