Recently I set up GitHub sponsors, if you my content helps you, you can consider a one-time or monthly sponsorship.

Unsafe SQL functions in Laravel

I recently learned that not all query builder functionality in Laravel is "safe". This means that user input shouldn't be passed directly to it, as it might expose your application to SQL injection vulnerabilities.

This is where the ad would go. Instead though, I'd like to point you towards my GitHub Sponsors page. If you're a regular reader and my content is helping you, you can consider a one-time or monthly sponsorship. If you're a company looking for dedicated ad placements on this blog or my newsletter, you can email me at brendt@stitcher.io

The past few days it became clear that there is little community knowledge about these unsafe functions. Many developers assume, as did I, that the Laravel query builder completely prevents SQL injection attacks.

This blog post aims to raise awareness about what's safe, and what's not.

# An SQL injection vulnerability?

Let's start by mentioning that this vulnerability has been fixed as of Laravel 5.8.11. While technically we could call this a "vulnerability", Laravel developers should know that they also play a role in preventing these kinds of issues.

Let's examine the issue.

Laravel has the ability to manually specify which columns to select on a query. It also offers the shorthand notation to query JSON data:

<hljs type>Blog</hljs>::<hljs prop>query</hljs>()
    -><hljs prop>addSelect</hljs>('<hljs green>title</hljs>-><hljs blue>en</hljs>');
<hljs keyword>SELECT</hljs> <hljs prop>json_extract</hljs>(`<hljs green>title</hljs>`, '$."<hljs blue>en</hljs>"') <hljs keyword>FROM</hljs> blogs;

Instead of manually writing json_extract, we can use the simplified -> syntax, which Laravel will convert to the correct SQL statement.

Be careful though: Laravel won't do any escaping during this conversion. Consider the following example:

<hljs type>Blog</hljs>::<hljs prop>query</hljs>()
    -><hljs prop>addSelect</hljs>('<hljs green>title</hljs>-><hljs blue>en</hljs><hljs red>'#</hljs>');

By inserting '# in our input, we can manually close the json_extract function, and ignore the rest of the query:

<hljs keyword>SELECT</hljs> <hljs prop>json_extract</hljs>(`<hljs green>title</hljs>`, '$."<hljs blue>en</hljs><hljs red>'#</hljs><hljs textgrey>"') FROM blogs;</hljs>

This query will fail because of syntax errors, but what about the next one?

<hljs keyword>SELECT</hljs> <hljs prop>json_extract</hljs>(
    `<hljs green>title</hljs>`, 
    '$."<hljs blue>en</hljs><hljs red>"')) 
FROM blogs RIGHT OUTER JOIN users ON users.id <> null
#</hljs>
    <hljs textgrey>"') FROM blogs;</hljs>

We're adding an outer join on the users table. Essentially selecting all data in it.

For reference, this is the URL encoded version of the malicious code:

%22%27%29%29+FROM+blogs+RIGHT+OUTER+JOIN+users+ON+users.id+%3C%3E+null%23

Say we have the following endpoint in our application, to query blog posts from a public API:

Route::get('/posts', function (Request $request) {
    $fields = $request->get('fields', []);

    $users = Blog::query()->addSelect($fields)->get();

    return response()->json($users);
});

Consumers of this API might only be interested in a few fields, that's why we added a fields filter. Something similar to sparse fieldsets from the JSON api spec.

The endpoint can now be used like this:

/blog?fields[]=url&fields[]=title

Now we insert our malicious code instead:

/blog?fields[]=%22%27%29%29+FROM+blogs+RIGHT+OUTER+JOIN+users+ON+users.id+%3C%3E+null%23

It will be added to the query. And by returning the query result as JSON, we'll see the full contents of the users table.

Blog::query()->addSelect([
    '%22%27%29%29+FROM+blogs+RIGHT+OUTER+JOIN+users+ON+users.id+%3C%3E+null%23'
])->get();

Two things need to be in place for this attack to be possible:

# Prevention?

As mentioned before, this particular vulnerability has been fixed as of Laravel 5.8.11. It's always good to keep up to date with the latest Laravel version.

More importantly though, developers should never allow user input directly to specify columns, without a whitelist. In our previous example, you could prevent this attack by only allowing certain fields to be requested, this would prevent the issue completely.

Next, one of our widely-used packages, spatie/laravel-querybuilder, opened up addSelect by design. This meant that websites using our package, were vulnerable to the underlying issue. We immediately fixed it and Freek wrote about it in depth. If you're using our package and unable to update to the latest Laravel version, you should immediately update the package.

Finally, the Laravel docs have also been updated to warn developers not to pass user input directly to columns when using the query builder.