Security Tip: Why Parameterised Queries Are Important!
[Tip#4] We're following the theme of reminders for simple features that are easy to overlook with a reminder to use Parameterised Queries!
👉 Looking to dive deeper into Laravel security? Check out Practical Laravel Security, my hands-on security course that uses interactive hacking challenges to teach you about how vulnerabilities work, so you can avoid them in your own code! 🕵️
👉 When was your last Security Audit or Penetration Test? Book in a Laravel Security Audit and Penetration Test today! 🕵️
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.
Check out my SQL Injection In Depth article for more details, 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()`1 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.
