Our laravel-query-builder package exposed a serious security issue: it allowed SQL injection attacks. Laravel Query Builder v1.17.1, which is now available, fixes the vulnerability. If you're using the package, stop reading now and upgrade to the latest version first. For Laravel 5.6, 5.7 and 5.8 you're safe if you use v1.17.1. For Laravel 5.5 you should be on 1.16.1. Any other version is considered unsafe.
The problem #
laravel-query-builder allows you to filter, sort and include eloquent relations based on the request.
Imagine you want to get all users sorted by email:
On the endpoint, you can use our package to build up a query based in URL parameters.
use Spatie\QueryBuilder\QueryBuilder; $users = QueryBuilder::for(User::class)->get(); // all `User`s sorted by email
Under the hood, we'll transform it to this:
And Laravels internal query builder will transform that to the following SQL query:
select * from `users` order by `email` asc
Thatâs all working as intended. However, the problem occurs when somebody enter the following sort query:
Under the hood our package will transform that to this:
You might think that you would get a MySQL error because you're ordering on a non-existing column, but that's not the case. Laravel will convert the code above to this SQL query. The produced query might seem strange on first sight, but itâs 100% valid, and MySQL will execute it.
select * from `users` order by json_unquote(json_extract(`email`, '$.""'))#injectedSQL"')) asc
Laravel will parse the
-> in the URL and replace with those JSON MySQL functions. The
"%27)) part of the query will allow us to break out of the open string and causes the function to be closed early after which we can add any SQL statement we want. In this case, we just added a friendly comment. But somebody with ill intentions can add any malicious code they want there.
In this repository youâll find a test that demonstrates the issue.
Fixing this issue #
We fixed this issue by only allowing alphanumeric characters,
_ anywhere the package handles column names. Here's the relevant commit made by my colleague Alex.
In closing #
You might think that Laravel is to blame because it fails to produce a safe MySQL query. But in my opinion, this isnât Laravels fault. The documentation states you shouldn't clean strings passed as bindings.
The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean strings passed as bindings.
I can imagine that there are apps out there that pass
field_name in a request like
https://mysite.com/some-resource?orderby=field_name to the
orderBy function. Whenever you specify column names in the query, it's your responsibility that you pass a safe string. In my opinion, the Laravel documentation could stress this fact more.
EDIT: meanwhile a warning was added in the laravel docs.
Many thanks to my colleagues Alex and Brent for investigating this and to Jonas Staudenmeir for reporting the original issue. Also thank you Nuno Maduro and Dries Vints for proofreading this blogpost.