SQLite can reject double-quoted strings that hide misspelled columns

A misspelled column should be an error, but SQLite's legacy quoting behavior can turn it into a valid string expression. If a double-quoted name does not resolve to a column, the engine can accept it as a string literal. SQLite 3.29.0, released on 10 July, gives applications controls to disable that fallback. Both controls remain enabled by default for compatibility, while the release recommends switching them off.

The problem is more serious than an unusual spelling convention. SQL that succeeds can return plausible-looking rows while computing the wrong expression. A report selecting a misspelled column can repeat a fixed string for every row. The statement has passed parsing and name resolution, so an application that checks only for execution errors has no indication that the requested column was absent.

I value SQLite's compatibility, particularly when a database file needs to outlive the program that created it. Compatibility becomes harder to defend when it converts a straightforward programming error into accepted SQL. These new controls let an application choose stricter preparation without requiring every existing user of the library to make that choice at once.

How an identifier becomes a value

The 3.29.0 name resolver documents the history directly. Its comment says the fallback originated in an attempt to support the double-quoted strings accepted by MySQL 3.x. The resolver first tries to find the column. Only after failing to find a match does it consider converting a double-quoted token into a string.

Consider an illustrative table with a declared temperature column. The first query uses that column. The second misspells it.

CREATE TABLE readings(temperature INTEGER);
INSERT INTO readings VALUES(21);

SELECT "temperature" FROM readings;
SELECT "temprature" FROM readings;

With the legacy behavior enabled, the second expression becomes the text value temprature. The engine does not infer that the programmer meant a nearby identifier. It accepts the unresolved token as a string. Disabling the fallback makes the unresolved name an error, which is the behavior needed to catch the typo during statement preparation.

The source also emits an SQLITE_WARNING log message when it performs the conversion. A warning and an error have different consequences for the calling application. The warning records that accepted SQL used the legacy interpretation. Turning the option off prevents that interpretation from rescuing the statement. An application need not depend on collecting and interpreting a warning to reject the invalid column reference.

This rule preserves double quotes for identifiers that resolve correctly. The new setting does not prohibit quoted column names. Its purpose is to remove the alternate string interpretation when resolution fails. Strings can use single-quoted SQL literals, leaving double quotes available for names that need quoting.

The distinction also affects how a query changes with its schema. Under the fallback, a token that currently resolves to a column can acquire string semantics if that column is absent in another database. Strict preparation instead exposes the schema mismatch as an error. This follows from the resolver's lookup-before-conversion sequence. It is useful for applications opening databases with differing schemas, because successful preparation then gives a stronger indication that the named column actually exists.

Separate controls for statements and schema

The public header for this release defines SQLITE_DBCONFIG_DQS_DML and SQLITE_DBCONFIG_DQS_DDL for sqlite3_db_config. The DML control covers statements such as SELECT, INSERT, UPDATE and DELETE. Its DDL counterpart covers schema statements such as CREATE TABLE and CREATE INDEX.

An application using the C interface can disable both on an opened connection. The following fragment assumes db is a valid connection and leaves ordinary connection cleanup to its caller.

int rc = sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, 0);
if (rc != SQLITE_OK) return rc;
rc = sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, 0);
if (rc != SQLITE_OK) return rc;

These are connection settings. The configuration implementation maps them to separate flags on the database connection. Setting either flag to zero clears it. A caller can provide the final integer pointer to obtain the resulting setting, while a null pointer omits that output. Changing a flag also expires prepared statements associated with the connection.

Separating DDL and DML gives an application a narrower transition path when its schema creation SQL and ordinary queries have different compatibility requirements. It also means that setting only the DML flag does not assert that newly submitted schema statements follow the same quoting discipline. The two controls describe separate parsing contexts and should be interpreted that way.

Build defaults remain configurable

The release also recommends the compile-time option -DSQLITE_DQS=0. The source sets the default value of SQLITE_DQS to 3 when no value is supplied. Its low bit enables the DML fallback and its other bit enables the DDL fallback. Zero disables both defaults, while values 1 and 2 select only one context.

That is a build default for connections created by the library. The runtime interface remains useful when an application does not control how SQLite was compiled, or when different connections need different policy. A program embedding its own SQLite build can establish the strict default centrally. A program linking another build can request the behavior through the connection API.

The resolver contains a further compatibility qualification. While SQLite is initializing an existing schema, it continues supporting the legacy behavior. A strict connection setting therefore should not be advertised as rewriting historical schema text or proving that an old database contains no such expressions. The implementation deliberately distinguishes loading legacy schema from accepting new statements under the connection's settings.

I prefer the explicit opt-out because it makes the application responsible for the compatibility decision. A new program can reject a misspelled quoted column immediately. An older program can retain its accepted SQL while its quoting is corrected. The library continues opening existing databases without turning this release into an unconditional rejection of historical schema syntax.

The practical improvement is at the point where SQL acquires meaning. An unresolved name no longer has to become a string just because double quotes surround it. With both controls disabled for newly submitted SQL, a missing column remains a preparation error that the caller can handle through its normal error path.