Security Tip: Parameterised Queries

[Tip#4] We're following the theme of reminders for simple features that are easy to overlook.

Greetings everyone! We’re now a month into Laravel Security in Depth, which is an exciting milestone to hit. I hope the weekly tips have been great reminders, and our first in depth email taught you something new about Encryption in Laravel. The curiosity for me was learning Laravel wraps the encrypted string inside JSON and base64, to make it easy to pass around. A simple but powerful trick I had no idea about. 🤯

This week we’re going to follow the theme of reminders for simple features in Laravel that are easy to forget, by briefly covering Parameterised Queries¹. Next week we’ll expand on this topic by looking into the mechanics of SQL Injection Attacks², to help tie it all together into why Parameterised Queries are important. I’m looking forward to digging into actual attacks with you next week!

Now, before we get to the security tip, I just want to remind all the free subscribers that you can become a premium subscriber for only $7 per month to receive our monthly In Depth emails and weekly Security Tips³.

That’s it from me, let’s get on to Parameterised Queries!

---

Why Parameterised Queries Are Important

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 next week, 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.

https://xkcd.com/327/

1. Yup, it’s spelled with an S not a Z because we use real English in Australia. 😝
(Well, it’s closer to real English than American English.)

2. I’ll try and get a test site up where you can run your own SQLi attacks safely, but no promises. It may end up being a local dev type project you can download to play with locally.

3. It was recently pointed out to me that you can preview all of our weekly topics here.