Laravel SQL Injection
Guide: Examples and
Prevention

stackhawk

StackHawk|March 2, 2021

Learn how SQL injection can be carried out on a Laravel app and see some examples of SQL injection in Laravel and ways to prevent them.

“With great power comes great responsibility.” —Uncle Ben 

As a developer creating apps, you have great power at the tip of your fingers. However, you’re also charged with the responsibility of keeping the data your app stores safe. Structured Query Language (SQL) is a relational database management system for software development. SQL injection is an old and very common security issue in SQL. 

In this post, we’ll explain how SQL injection can be carried out on a Laravel app and suggest some prevention techniques. First, we’ll take a look at what SQL injection is. After that, we’ll see some examples of SQL injection in Laravel and ways to prevent them. 

It’s possible to feel a false sense of safety while using a framework or tool by assuming that the maintainers of the tool have covered everything security related. Laravel provides many safe ways to work with SQL. However, it’s still worth learning about what’s not covered. Keep reading along to learn about some use cases and features in Laravel that may leave your application exposed to SQL injection. 

Before we dive into examples of Laravel SQL injection, let’s take a look at what SQL injection is. 

What Is SQL Injection?

A hacker can inject malicious code and perform even more serious operations on your database.

First up, we’ll explain SQL injection using a practical example. Say you have a web app that displays a user profile via the following URL: 

https://example.com/profile.php/?user=john.doe

To fetch data for the current user john.doe, the following query will be executed: 

SQL
SELECT * FROM users WHERE username = 'john.doe'

In an attempt to perform an SQL injection, a hacker can set the value for the user parameter to something like this: 

https://example.com/profile.php/?user=john.doe’ OR 2=2;--

For the above HTTP request, the following SQL query will be executed: 

SQL
SELECT * FROM users WHERE username = 'john.doe' OR 2=2;--'

The above SQL query will always return true since 2=2 is always true, and with the OR keyword, if one side is true, the entire expression will return true. Therefore, the above query will return all rows in the “users” table. This is a typical example of an SQL injection. 

With a vulnerability like the one demonstrated above, a hacker can inject malicious code and perform even more serious operations on your database. You can learn more about what SQL injection is, the various kinds of SQL injections, and recommended best practices in this detailed post

Laravel SQL Injection

Laravel is a free open-source PHP framework. It follows the MVC design pattern and has built-in tools for performing tasks like user authentication, routing, and database operations. 

With the help of the Eloquent ORM, you can build a small Laravel app that reads and writes data to an SQL database without writing a single raw SQL query. That is, you don’t need to write queries like “SELECT * FROM table” to read data from SQL. However, Laravel supports raw SQL query, as your desired task may require raw queries in some cases. 

Now let’s look at some examples of Laravel SQL injection and possible ways to prevent attacks. 

Example 1: Use of RawMethods

RawMethods are Laravel’s neat way of letting developers use raw queries in only specific parts of a database query. Some examples of Laravel’s RawMethods include selectRaw, whereRaw, and orderByRaw. RawMethods, however, are vulnerable to SQL injection, which the official documentation states in the following sentence: “Remember, Laravel can not guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.” 

To demonstrate SQL injection in the whereRaw RawMethod, let’s take a look at the following code: 

PHP
DB::table('posts')
    ->select('postTitle', 'postBody')
    ->whereRaw('id =' . $id)->first(); 

The code sample above should return a single row from the posts table. Or nothing if no post exists with the id specified. However, this code has a third unintended behavior. 

The value for id is defined by user input. Let’s look at what happens when a user enters the following value: 

https://example.com/post/11 AND 1=1

The HTTP request above will lead to the execution of the following SQL query: 

SQL
SELECT postTitle, postBody FROM posts WHERE id = 11 AND 1=1

The application will return the row with id 11 as expected. This is because 1=1 is always true. However, say a hacker changes 1=1 to something that’s always false, for example:

SQL
SELECT postTitle, postBody FROM posts WHERE id = 11 AND 1=2

This will cause the application to return zero rows or crash. This behavior shows the existence of SQL injection vulnerability in the whereRaw part of our initial query. 

Prevention

We recommend anyone using Laravel to avoid raw queries as much as they can.

Doing so, they can enjoy some of the security features already built in to the framework. But if you must use raw queries, you should ensure you do server-side validation of user inputs. 

One way to fix the vulnerability in our example is to validate that the value of id is an integer. You can do so with the following code:

PHP
$validator = Validator::make(['id' => $id], [
    'id' => 'required|numeric'
]);

if ($validator->fails()) {
    abort(404);
}else {
    //Run query
}

Another fix is to rewrite the initial query using a parameterized query. 

PHP
DB::table('posts')
    ->select('postTitle', 'postBody')
    ->whereRaw('id = ?', $id)->first();

We introduced a ? as a placeholder for the value of id and provided the actual value for id as a second parameter for whereRaw

Example 2: Use of DB::statement

If all you want to do is run a query, Laravel has the DB::statement method for that. It accepts raw SQL query as a parameter, but it isn’t completely covered by Laravel’s built-in security features. To demonstrate how the DB:statement works, let’s take a look at the following code: 

PHP
DB::statement("UPDATE users SET password=".$newPassword. "  WHERE username =" . $username);

The above code should change the password for a specific user by updating a correct row in the users table. However, in a situation where a user inputs “Anybody OR 1=1” as the username and “123456” as the password, a different outcome arises. 

The following query will be executed: 

SQL
UPDATE users SET password="123456 " WHERE username ="Anyone" OR 1=1

The above query will set the password for all users to “123456”. With this type of exploit, a hacker can gain access to multiple user accounts on your website. 

Prevention

User input validation can help in this case too. In addition, you can use ? as a placeholder for user inputs, then supply the actual values as the second parameter of the DB::statement method as implemented below: 

PHP
DB::statement("UPDATE users SET password=?  WHERE username =?", [$password, $username]);

Example 3: Error Messages

Applications built with Laravel may display sensitive information such as database queries during unhandled exceptions. 

DB::statement("SELECT * FROM users WHERE id=".$id );

The above code will display the following error message in the user’s browser when an invalid id (non-integer value) is entered: 

sql-injection-prevention-laravel-img-3 image

As seen in this screenshot, the error page displays sensitive details about the associated SQL query. This kind of information helps a hacker understand the logic underneath your application and exposes you to potential attacks.

Prevention

Test your app for this kind of unhandled exception before pushing to production. There’s also a way to completely turn off this type of error reporting in Laravel. We recommend that you turn error reporting off in production. You can still find helpful details about your app crashes and errors in the Laravel log files. 

To turn in-page error reporting off, open the .env file and change the value for APP_DEBUG from true to false. The final code should look like this: 

APP_DEBUG=false
Ready to Test Your App

To Close Up

In summary, SQL injection is, unfortunately, a thing in Laravel. But validation of user inputs and parameterized queries can help reduce the risk of SQL injection. 

The security of your Laravel application is a continuous process. And we can’t exhaust all the possible vulnerabilities and solutions in a single post. So be sure to always follow best practices and keep your code and tools up to date. 

This post was written by Pius Aboyi. Pius is a mobile and web developer with over 4 years of experience building for the Android platform. He writes code in Java, Kotlin, and PHP. He loves writing about tech and creating how-to tutorials for developers.


StackHawk  |  March 2, 2021