Double quotes accepted unexpectedly for literal value escape in SQLite

[mvorisek]

Description

discovered in doctrine/dbal#6325 (comment)

Currently PHP pdo_sqlite (and sqlite3) extensions accept SQL queries /w format that is natively not supported by SQLite degrading our testing quality as such SQL queries cannot be executed outside PHP.

PHP repro: https://3v4l.org/lfZSq

SQLite/native repro: https://dbfiddle.uk/D0hu63uV (reproducible also on https://sqlite.org/fiddle/)

Current behaviour:

" (double quote) accepted for literal value escape

Expected behaviour:

SQLite does not accept " (double quote) natively for literal value escape, I would expect the same from PHP by default, ie. fail with SQLite parse error if some query like select "-1" is executed.

PHP Version

any

Operating System

any

[SakiTakamachi]

If I remember correctly, it is officially supported (albeit deprecated)

https://www.sqlite.org/lang_keywords.html

[mvorisek]

Citing from https://www.sqlite.org/lang_keywords.html

If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

probably because of this paragraph.

As shown in the dbfiddle.uk and sqlite.org/fiddle/ repros, the "historical mode" is not always used.

Can this "historical mode" be disabled in current PHP releases thru some flag?

For master (if classified as a feature request), I would expect this "historical mode" to be disabled by default. Suppressing missing columns errors by assuming string literals is very dangerous and unwanted.

[mvorisek]

based on https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted it can be configured like:

sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, (void*)0);
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, (void*)0);

man: https://www.sqlite.org/c3ref/c_dbconfig_defensive.html

As of SQLite 3.41.0 (2023-02-21) SQLITE_DBCONFIG_DQS_DDL and SQLTIE_DBCONFIG_DQS_DML are disabled by default in the CLI.

this also explains why the SQLite/native repro does not suffer with this issue

[SakiTakamachi]

If php accepts this proposed change, it is expected that it will probably break many user environments.

This is definitely a BC Break, and an RFC is required to prohibit it across the board.

Another option is to allow the user to select this, such as using pdo's setAttribute. I don't think an RFC is needed if the default value is the existing behavior.

[SakiTakamachi]

IMHO, while SQLite supports this feature, php should also be able to take advantage of this. So instead of banning it outright, I think it would be better to have options.