SQLite queries generated by Eloquent with non existing columns in WHERE clause should generate an error/exception but don't

[tasso85]

When using SQLite as database for Laravel framework, queries are generated using double quotes for identifiers, which can have unexpected results when you query for non existing columns.

For example, an Eloquent query like this

Company::where("not_existing", "whatever");

gets converted to

select * from "companies" where "not_existing" = 'whatever';

The problem is that, as stated at here:

[...] in an effort to be compatible with MySQL 3.x (which was one of the most widely used RDBMSes when SQLite was first being designed) SQLite will also interpret a double-quotes string as string literal if it does not match any valid identifier.

So, the above query, instead of generating an error as I would have expected, simply returns no result; similarly, a query like

select * from "companies" where "not_existing" = 'not_existing';

will return all rows from the table.

Steps To Reproduce

1. Create a test table in a migration, for example

Schema::create("companies", function (Blueprint $table) {
	$table->id("id")->primary();
	$table->string("name");
	$table->timestamps();
});

2. Seed the database

DB::table('companies')->insert(['name' => 'test']);

3. Create the matching model

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Company extends Model {
	...
}

4. Try this query in Artisan tinker

Company::where("not_existing", "whatever")->first();

It'll return null instead of generating an error/exception, whereas

Company::where("not_existing", "not_existing")->first();

will return the seeded record.

Possible solutions

I do understand that this is not directly tied to Laravel framework itself, and that, from what I understand, it could be resolved by recompiling PHP with some specific flags; however, it seems that in the standard PHP versions you download from repository, those flags were not used.

Given the above, I think that the framework could/should give a mean to its users to work around this problem.

From what I have seen, the possible solutions might be:

1. change (or, give a way to easily change) the quoting mechanism used: based on what I can see here, SQLite can accept also [] or backticks to quote identifiers; or
2. give a way to always add the table name as a prefix, even when not manually specified: in that case, I have tested that the query gives an error as expected.

Maybe is there a way to easily do one of the above that I missed? I am indeed quite new to the framework.

[Sophist-UK]

The problem is that, as stated at here:

[...] in an effort to be compatible with MySQL 3.x (which was one of the most widely used RDBMSes when SQLite was first being designed) SQLite will also interpret a double-quotes string as string literal if it does not match any valid identifier.

I do understand that this is not directly tied to Laravel framework itself, and that, from what I understand, it could be resolved by recompiling PHP with some specific flags; however, it seems that in the standard PHP versions you download from repository, those flags were not used.

Because MySQL 3.x is so old and likely that anyone still using it is also using an old PHP, IMO we should be asking the PHP folks to change the default flags so that this compatibility is now turned off.

[tasso85]

That could be an idea, didn't think of it; since this would be a BC break, I think it should go through an RFC, which I do not know if anyone can open.

[Sophist-UK]

It might need a deprecation notice in one (or possibly more) release(s) followed by the change itself.