D 2019-08-01T12:01:02.471
L The\sinfamous\sDQS\smisfeature
P 0008e59eecedf3c723191d1a499dd58108153edf
U sandro
W 5505
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</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 misfeature</b></i> 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 Constants</b></i>)<br>
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 = 'something';</b></i></li>
<li><b>SQL names</b> (aka <i><b>SQL identifiers</b></i>)<br>
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 character</i></b> 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 Double-quoted.</li></ul>
<b>Example</b>: <i><b>SELECT "a", "b", "c" FROM "x";</b></i></li>
</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, <b>-DSQLITE_DQS=<i>n</i></b></li>
<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 misfeature</b>.</li>
</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 -DSQLITE_DQS=0</th></tr>
<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
</verbatim></td>
<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
</verbatim></td>
</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>
</td></tr>
</table>
<br><br>
<hr><br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</a>
Z 8857dc85c971ea518fb3ece47eded83e