Back to 5.0.0-doc main page

Introduction

DQS simply is the acronym for Double-Quoted Strings.
All previous versions of SQLite3 were affected by an obnoxious DQS misfeature that has been finally resolved starting from version 3.29.0 (released on 2019-07-10).

What the ISO SQL standards dictates

The ISO SQL standard has two different kinds of text strings:

String Literals (aka Text Constants)
They are always interpreted as values, and must necessarily be enclosed within a pair of Single Quotes.
Example: SELECT * FROM x WHERE name = 'something';
SQL names (aka SQL identifiers)
They are intended to identify Tables, Columns, Triggers and alike, and must optionally enclosed within a pair or Double Quotes.
More precisely:
- SQL names containing any forbidden character must be always masked by enclosing the intended name within Double Quotes.
- any other SQL name (not containing forbidden characters) does not strictly requires Double-quoting, but can be optionally Double-quoted.
Example: SELECT "a", "b", "c" FROM "x";

What was effectively supported by SQLite

In an attempt to mimic the non-standard behavior of MySQL 3.x SQLite was always very tolerant about Single- and Double-Quoting.
Even in the case of incorrect quoting SQLite was often able to guess the intended scope of any string by analyzing its context.
It clearly was a potentially error prone approach, but it helped many sloppy or messy users to write their SQL queries with few complications.
Very simply said, it was a misfeature badly encouraging many developers into the bad habit of misusing double- and single-quoting.

The most recent approach of SQLite to ISO SQL quoting

In recent times the developers of SQLite finally recognized that departing from the ISO SQL standard wasn't good at all.
And consequently they decided that starting from version 3.29.0 SQLite should finally be able to correctly enforce strict ISO SQL quoting.
Unhappily such a radical change can have a very heavy impact on already existing applications, so a moderate approach was adopted:

a new build-time option has been introduce, -DSQLITE_DQS=n
The default assumption is -DSQLITE_DQS=3
this practically corresponds to the old traditional behavior (i.e. supporting a very permissive and tolerant quoting interpretation).
But the developers of SQLite strongly recommend to always explicitly set -DSQLITE_DQS=3
this will enforce strict ISO SQL quoting, and will definitely eradicate the extravagant DQS misfeature.

A practical comparative test

Tested on SQLite 3.25.3	Tested on SQLite 3.29.0 - compiled with -DSQLITE_DQS=0
CREATE TABLE "test" ( "id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL); ----------------------------- ok INSERT INTO "test" ("id", "name") VALUES (123, "one"); ----------------------------- ok SELECT "id" FROM "test" WHERE "name" = "one"; ----------------------------- 123 INSERT INTO test (id, name) VALUES (123456, 'two'); ----------------------------- ok SELECT id FROM test WHERE name = 'two'; ----------------------------- 123456	CREATE TABLE "test" ( "id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL); ----------------------------- ok INSERT INTO "test" ("id", "name") VALUES (123, "one"); ----------------------------- no such column: one SELECT "id" FROM "test" WHERE "name" = "one"; ----------------------------- no such column: one INSERT INTO test (id, name) VALUES (123456, 'two'); ----------------------------- ok SELECT id FROM test WHERE name = 'two'; ----------------------------- 123456

Tested on SQLite 3.25.3

Tested on SQLite 3.29.0 - compiled with -DSQLITE_DQS=0

CREATE TABLE "test" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT NOT NULL);
-----------------------------
ok

INSERT INTO "test" ("id", "name") VALUES (123, "one");
-----------------------------
ok

SELECT "id" FROM "test" WHERE "name" = "one";
-----------------------------
123

INSERT INTO test (id, name) VALUES (123456, 'two');
-----------------------------
ok

SELECT id FROM test WHERE name = 'two';
-----------------------------
123456

CREATE TABLE "test" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT NOT NULL);
-----------------------------
ok

INSERT INTO "test" ("id", "name") VALUES (123, "one");
-----------------------------
no such column: one

SELECT "id" FROM "test" WHERE "name" = "one";
-----------------------------
no such column: one

INSERT INTO test (id, name) VALUES (123456, 'two');
-----------------------------
ok

SELECT id FROM test WHERE name = 'two';
-----------------------------
123456

Anecdotal evidence

The code of libspatialite itself had very little problems in nicely adapting to he most recent -DSQLITE_DQS=0 requirements of SQLite 3.29.0.
There were just a couple of badly quoted SQL Statements requiring to be fixed.

But properly restoring the test coverage was a nasty surprise.
There were plenty of misquoted SQL Statements (about some thousands), and patiently nursing all them was a lengthy and boring task.

Be warned: switching to the more stringent requirements of the most recent versions of SQLite
can easily become a real nightmare, most notably if you usually adopted a sloppy SQL quoting style.

Important notice for Windows users

All future binary packages for Windows distributed by SpatiaLite itself will be always built by specifying the -DSQLITE_DQS=0 option.

Be prepared.

Back to 5.0.0-doc main page