Enforce additional WHERE on all queries.

We have a product SaaS let's say eCommerce. All user created shops and products are stored in single database. There is client_id key on every table so that we know which shop it is. Every API request has client_id in it. So we take care of it.

now we want to make so that 3dp developers create modules for our solution. But they can intentionally or not forget to apply client_id to the query and get everything.

Is there a way that we could parse every query before it is executed and add client_id there so that we could be sure select * never happen?

elite123 replied 3 years ago

Good question - you could possibly do something with scopes, although you may have to do this for all models...

thomastkim replied 3 years ago

Do you not want select * to happen on every query or do you want a where clause to happen on every query? Those two are very different things.

Assuming you want to add an additional where clause to all queries, you can do something like this:

Model.php (whatever model you have)

public static function boot()
    static::addGlobalScope(new ClientScope);

Then, create the ClientScope class.


namespace App;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\ScopeInterface;

use Request;

class ClientScope implements ScopeInterface {

    public function apply(Builder $builder, Model $model)
        // This is the part where you add the where clause.
        // I have no idea how you are passing the client_id though.
        // This example just assumes that you are passing an "id"
        // as a route parameter, but you may need to adjust accordingly.
        $builder->where('client_id', '=', Request::route('id'));

    public function remove(Builder $builder, Model $model) {
        // Not necessary
Serhioromano replied 3 years ago

By SELECT * I meant do you want a where clause to happen on every query.

How about this

DB::select("SELECT * FROM users");

Will it result in SELECT * FROM users WHERE client_id = 1?

I mean what if not model was used but direct SQL query? I have to assume that some individual will try to get all information on purpose. SO I have to have a way to protect. possible?

Math replied 11 months ago

I have the exact same problem right now. We currently decided to use only Models to access DB from our application. Did you find another solution?

Sign in to participate in this thread!

We'd like to thank these amazing companies for supporting us