Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID:	0008e59eecedf3c723191d1a499dd58108153edf
Page Name:	The infamous DQS misfeature
Date:	2019-08-01 10:32:11
Original User:	sandro
Next	d437e32b54d06a0b73c126c37c618ee1eb1d8d47

Content

back

Introduction

DQS simply is the achronym for Double-Quoted Strings.
All previous versions of SQLite3 were affected by an obnoxius 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 masqued 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 stongly reccommed to always explicitly set -DSQLITE_DQS=3
this will enforce strict ISO SQL quoting, and will definitely eradicate the extavagant DQS misfeaure.

back