Security Tip: Scoping orWhere Can Be Disastrous!

Today's security tip is a fun little one that can easily sneak in to your eloquent queries, if you're not paying attention.

Consider this database query:

SELECT *
FROM users
WHERE team_id = ?
  AND ( name LIKE ? OR name LIKE ? )

Looks fairly straightforward: Find users in the current team whose name contains one of two options.

One way to code this would be:

$teamId = 1;
$names = ['Baggins', 'Took'];

$nested = User::query()
    ->where('team_id', $teamId)
    ->where(function ($query) use ($names) {
        foreach ($names as $name) {
            $query->orWhere('name', 'LIKE', "%{$name}%");
        }
    })
    ->get();

Slightly messy, but does the job, and finds the following records:

• Frodo Baggins
• Peregrin Took

And ignores the following users:

• Samwise Gamgee (name doesn't match)
• Meriadoc Brandybuck (name doesn't match)
• Bilbo Baggins (team 2)
• Fredegar Bolger (team 3)

So far so good, right?

The next logical step is to extract those queries into scopes on the User model, so they can be reused easily:

 $scoped = User::query()
    ->team($teamId)
    ->nameEither(['Baggins', 'Took'])
    ->get();

#[Scope]
protected function team(Builder $query, int $teamId)
{
    $query->where('team_id', $teamId);
}

#[Scope]
protected function nameEither(Builder $query, array $names)
{
    foreach ($names as $name) {
        $query->orWhere('name', 'LIKE', "%{$name}%");
    }
}

Code looks good, right?

Here are the users it returns:

• Bilbo Baggins
• Frodo Baggins
• Samwise Gamgee
• Meriadoc Brandybuck
• Peregrin Took

Huh, what???

Why are they all being returned?

Including Bilbo, from a different team?

Something weird is going on here. Let's take a look at the raw queries Laravel generated:

// Original version
select * from "users" where "team_id" = ? and ("name" LIKE ? or "name" LIKE ?)

// Scoped version
select * from "users" where "team_id" = ? or ("name" LIKE ? or "name" LIKE ?)

Where'd that OR come from?

It turns out, when Laravel sees an orWhere at the start of a scope, it'll inject that outside the brackets, applying it to the bigger query.

Which means, this query actually finds anyone who is on the specified team (team 1), or anyone else in the database with a matching name - like Bilbo... 😱

Luckily, the fix is pretty simple, just wrap the orWhere inside a where:

#[Scope]
protected function nameEither(Builder $query, array $names)
{
    $query->where(function ($query) use ($names) {
        foreach ($names as $name) {
            $query->orWhere('name', 'LIKE', "%{$name}%");
        }
    });
}

The resulting query is:

select * from "users" where "admin" = ? and ("name" LIKE ? or "name" LIKE ?)

And the results are back to just Frodo and Pippin. 😅

While this is a pretty simple example, consider the implications of this occurring in a multi-tenant environment, or an app that deals with PII (Personally Identifiable Information) and PHI (Protected Health Information), where user privacy is so important. It quickly turns very scary.

My Recommendation

I find this one fascinating, as this behaviour is both predictable and unexpected - depending on how you understand the code. It's code like this where you need to be extra careful - don't assume the code works in a specific way, but instead be paranoid and add some layers of protection.

When it comes to using scopes and OR clauses, it's always a good idea to wrap them inside a where(). Be extra sure that OR only applies to that specific pair, and won't end up applying to everything.

If you found this security tip useful, subscribe to get weekly Security Tips straight to your inbox. Upgrade to a premium subscription for exclusive monthly In Depth articles, or drop a coin in the tip jar to show your support.

When was the last time you had a penetration test? Book a Laravel Security Audit and Penetration Test, or a budget-friendly Security Review!

You can also connect with me on Bluesky, or other socials, and check out Practical Laravel Security, my interactive course designed to boost your Laravel security skills.