Why Parameterised Queries Are Important

This is the fourth security tip from my Laravel Security in Depth newsletter, sent out to all subscribers on October 8th. Please subscribe if you’d like these tips delivered weekly.

Laravel provides an expressive fluent interface for building database queries, either as raw queries through the query builder or as part of Eloquent (Laravel’s Object-Relational Mapper, ORM). The query builder allows you to write queries regardless of the database backend, and makes it easy to write secure queries. This is important to protect us against SQL Injection (SQLi) attacks.

We’ll go into SQL Injection attacks in depth on Laravel Security in Depth, but in a nutshell, an SQL Injection attack is where the attacker can modify the query being executed on the database to perform some other (usually malicious) behaviour. SQLi is often used to bypass authorization checks or download copies of the database, and in some circumstances, it can even be used for more destructively to drop (delete) an entire database.

This is why we need to use parameterised queries.

Consider this PHP SQL query string:

SELECT * FROM users WHERE name = '{$name}'

In a typical scenario, the $name variable could be populated from user input. Maybe it’s a search box, or the user has specified their name on a profile form. Either way, it’s probably user data, and we simply cannot trust it.

We can’t trust it because an attacker could pass in a string with multiple single quotes (‘) in an attempt to re-write part of the query by closing the string early and adding alternate conditions.

Such as:

' OR '' = '

Which can transform the query into:

SELECT * FROM users WHERE name = '' OR '' = ''

The result is a very different conditional.

How does Laravel Help Us?

Laravel’s query builder helps us out by providing a way to safely provide variables (i.e. user data) to the database in the form of a parameter. The database itself will treat the whole parameter as a single value and use it when it performs the condition, without the value being injected into a string. The end result being, no combination of characters within the parameter can modify the query itself.

Laravel does this with the where() method on the query builder:

$user = DB::table('users')->where('name', $name)->get();

The $name parameter, the conditional value, is now completely safe, and you can pass whatever your users provide directly in there.

Note, only the value is safe. The column name (‘name’) is used in the query, and allowing users to provide that directly is asking for trouble!

You can also define the conditional operator as the second parameter, and bump the value to the third parameter:

$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

This will still ensure the value is handled safely by the database, but allow you to perform more complex queries.

Finally, if you need to use more complex logic or database functions that aren’t handled by the query builder, you can pass parameters into whereRaw() too.

$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

The database will see any ? and automatically replace them safely with the values in the parameter array sequentially.

So you’ve got no excuses not to write secure parameterised queries.

Just remember: always use a method like where() when building queries.