Laravel Question posted in
The official Laravel Documentation can be found here.

How to get SQLite error in PHP/Laravel when quoted column does not exist

Related to my previous question, I found out that due to an error I made, Laravel generates a wrong SQL query:

select * from "companies" where "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' and "companies"."deleted_at" is null and "company_id" = '9c54986f-8284-4da9-b826-c7a723de279b'

The problem here is that company_id does not exist in companies; however, the query does not generate an error when run, it just returns no result.

I suppose the problem here is that "company_id" is treated as a literal instead of a column reference; if I remove the quotes I get a proper error:

Error: in prepare, no such column: company_id (1)

I also get a proper error if I add the table prefix to the column name:

sqlite> select * from "companies" where "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' and "companies"."deleted_at" is null and "companies"."compa
ny_id" = '9c54986f-8284-4da9-b826-c7a723de279b';
Error: in prepare, no such column: companies.company_id (1)

Is there a way to solve this problem by acting on Laravel’s or SQLite’s configuration? I cannot alter how the queries are generated, as they are generated by the framework itself.

The fragment and "company_id" = '9c54986f-8284-4da9-b826-c7a723de279b' is generated by a global scope implemented like this:

abstract readonly class UnlessAuthorizedScope implements Scope {
    public function __construct(
        private   string  $modelField,
        protected ?string $authorizingPermission,
        private   string  $userField,
    ) {}

    public function apply(Builder $builder, Model $model): void {
        if (Auth::hasUser()) {
            $user = Auth::user();

            if (
                !$this->authorizingPermission
                || !$user?->can($this->authorizingPermission)
            ) {
                $builder->where(
                    $this->modelField,
                    $user?->{$this->userField}
                );
            }
        }
    }
}

which is then implemented in:

readonly class CurrentCompanyScope extends UnlessAuthorizedScope {
    public function __construct(?string $authorizingPermission = null, ?string $modelField = null) {
        parent::__construct(
            $modelField ?? "company_id",
            $authorizingPermission,
            "company_id"
        );
    }
}

and finally used as:

class Company extends Model {
    protected static function booted(): void {
        parent::booted();
        static::addGlobalScope(new CurrentCompanyScope(
            CompanyPermission::ViewAll->value,
            // the error was here, instead of specifying "id", I kept the default "company_id" value
        ));
    }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have been able to fix this problem by modifying the implementation of my base global scope:

    abstract readonly class UnlessAuthorizedScope implements Scope {
    public function __construct(
        private   string  $modelField,
        protected ?string $authorizingPermission,
        private   string  $userField,
    ) {}

    public function apply(Builder $builder, Model $model): void {
        if (Auth::hasUser()) {
            $user = Auth::user();

            if (
                !$this->authorizingPermission
                || !$user?->can($this->authorizingPermission)
            ) {
                $builder->where(
                    $model->getTable().".".$this->modelField, // changed here
                    $user?->{$this->userField}
                );
            }
        }
    }
}

    by adding an explicit table prefix to the field name, it was kept in the query generated by the framework, which then became:

    select * from "companies" where "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' and "companies"."deleted_at" is null and "companies"."compa
ny_id" = '9c54986f-8284-4da9-b826-c7a723de279b'

    and this new query generated a proper exception, which was caught by the framework and displayed on the page.


  2. 0

    What I think is happening here is that "company_id" is being interpreted as a string literal, rather than a column. Keep in mind that SQLite accepts string literals as either singly or doubly quoted. Apparently, SQLite’s heuristic for interpreting doubly quoted strings is:

    • First check if the string can be mapped to some database identifier (such as a column)
    • That failing, then treat the string as a string literal

    Here is your query again, formatted:

    SELECT *
FROM "companies"
WHERE
    "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' AND
    "companies"."deleted_at" IS NULL AND
    "company_id" = '9c54986f-8284-4da9-b826-c7a723de279b';

    As the company_id column does not exist, this query appears to be getting interpreted as:

    SELECT *
FROM "companies"
WHERE
    "companies"."id" = '9c54986f-8284-4da9-b826-c7a723de279b' AND
    "companies"."deleted_at" IS NULL AND
    'company_id' = '9c54986f-8284-4da9-b826-c7a723de279b';

    Of course, the string comparison in the last term of the WHERE clause will never be true, hence no records are being returned.

    There is no need for your columns to be in double quotes, so don’t use them, to avoid this sort of problem.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search