Back to <a main page</a><hr><br>
<h1>Introduction</h1>
<b>DQS</b> simply is the acronym for <b>Double-Quoted Strings</b>.<br>
All previous versions of <b>SQLite3</b> were affected by an obnoxious <i><b>DQS that has been finally resolved starting from version <b>3.29.0</b> (released on <b>2019-07-10</b>).
<h3>What the ISO SQL standards dictates</h3>
The ISO SQL standard has two different kinds of text strings:
<ol>
<li><b>String Literals</b> (aka <i><b>Text
    They are always interpreted as <b>values</b>, and <b>must</b>
    necessarily be enclosed within a pair of <b>Single Quotes<b>.<br>
    <b>Example</b>: <i><b>SELECT * FROM x WHERE name =
<li><b>SQL names</b> (aka <i><b>SQL
    They are intended to identify <b>Tables</b>, <b>Columns</b>,   <b>Triggers</b> and alike, and <b>must</b> optionally enclosed within a pair or <b>Double Quotes</b>.<br>
    More precisely:<ul>
    <li>SQL names containing any <b><i>forbidden must be always masked by enclosing the intended name within Double Quotes.</li>
    <li>any other SQL name (not containing forbidden characters) does not strictly requires Double-quoting, but can be optionally
    <b>Example</b>: <i><b>SELECT "a", "b", "c" FROM
    </li>
</ol>
<h3>What was effectively supported by SQLite</h3>
In an attempt to mimic the non-standard behavior of <b>MySQL 3.x</b> SQLite was always very tolerant about Single- and Double-Quoting.<br>
Even in the case of incorrect quoting SQLite was often able to guess the intended scope of any string by analyzing its context.<br>
It clearly was a potentially error prone approach, but it helped many sloppy or messy users to write their SQL queries with few complications.<br>
Very simply said, it was a <b>misfeature</b> badly encouraging many developers into the bad habit of misusing double- and single-quoting.<br>
<h3>The most recent approach of SQLite to ISO SQL quoting</h3>
In recent times the developers of SQLite finally recognized that departing from the ISO SQL standard wasn't good at all.<br>
And consequently they decided that starting from version <b>3.29.0</b> SQLite should finally be able to correctly enforce strict ISO SQL quoting.<br>
Unhappily such a radical change can have a very heavy impact on already existing applications, so a moderate approach was adopted:
<ul>
<li>a new build-time option has been introduce,
<li>The default assumption is <b>-DSQLITE_DQS=3</b><br>
    this practically corresponds to the old traditional behavior (i.e. supporting a very permissive and tolerant quoting interpretation).</li>
<li>But the developers of SQLite strongly recommend to always explicitly set <b>-DSQLITE_DQS=3</b><br>
    this will enforce strict ISO SQL quoting, and will definitely eradicate the extravagant <b>DQS
</ul>
<h3>A practical comparative test</h3>
<table bgcolor="#e0ffe0" border="1" cellspacing="6" cellpadding="6" width="100%">
<tr><th>Tested on SQLite 3.25.3</th><th>Tested on SQLite 3.29.0 - compiled with
<tr>
<td><verbatim>
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

<td><verbatim>
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

</tr>
</table>
<h3>Anecdotal evidence</h3>
The code of <b>libspatialite</b> itself had very little problems in nicely adapting to he most recent <b>-DSQLITE_DQS=0</b> requirements of SQLite <b>3.29.0</b>.<br>
There were just a couple of badly quoted SQL Statements requiring to be fixed.<br><br>
But properly restoring the test coverage was a nasty surprise.<br>
There were plenty of misquoted SQL Statements (about some thousands), and patiently nursing all them was a lengthy and boring task.<br><br>
<font size="5" color="red">
Be warned: switching to the more stringent requirements of the most recent versions of SQLite<br>
can easily become a real nightmare, most notably if you usually adopted a sloppy SQL quoting style.
</font><br><br>
<table bgcolor="#fff0e0" border="1" cellspacing="6" cellpadding="16">
<tr><td>
<h1>Important notice for Windows users</h1>
All future binary packages for Windows distributed by SpatiaLite itself will be always built by specifying the <b>-DSQLITE_DQS=0</b> option.<br><br>
<h1>Be prepared.</h1>

</table>
<br><br>
<hr><br>
Back to <a main page</a>