Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
sqlite3 double quote behavior
390 views
Skip to first unread message
John K. Parejko
Dec 12, 2022, 7:47:20 PM12/12/22
to
Asking here before I file an improvement request issue on the python GitHub:
sqlite has a known misfeature with double-quoted strings, whereby they will be interpreted as string literals if they don’t match a valid identifier [1]. The note in the sqlite docs describe a way to disable this misfeature at compile time or by calling an `sqlite3_db_config` C-function, but I don’t see any way to do that in the python sqlite library [2].
Am I missing a way to manage this setting, or is it not available within python? This would be very useful to enable, so that python’s sqlite library will treat queries more like standard sql, instead of this particular version of MySQL. I was just burned by this, where some tests I’d written against an sqlite database did not fail in the way that they “should” have, because of this double-quoted string issue.
It doesn’t look like `sqlite3_db_config` is used within the python sqlite3 codebase at all, so this might not be a trivial change? I only see two references to it in the cpython github.
Thank you in advance for any suggestions,
John
1: https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted
2: https://docs.python.org/3/library/sqlite3.html
sqlite has a known misfeature with double-quoted strings, whereby they will be interpreted as string literals if they don’t match a valid identifier [1]. The note in the sqlite docs describe a way to disable this misfeature at compile time or by calling an `sqlite3_db_config` C-function, but I don’t see any way to do that in the python sqlite library [2].
Am I missing a way to manage this setting, or is it not available within python? This would be very useful to enable, so that python’s sqlite library will treat queries more like standard sql, instead of this particular version of MySQL. I was just burned by this, where some tests I’d written against an sqlite database did not fail in the way that they “should” have, because of this double-quoted string issue.
It doesn’t look like `sqlite3_db_config` is used within the python sqlite3 codebase at all, so this might not be a trivial change? I only see two references to it in the cpython github.
Thank you in advance for any suggestions,
John
1: https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted
2: https://docs.python.org/3/library/sqlite3.html
Lars Liedtke
Dec 13, 2022, 3:08:28 AM12/13/22
to
Hey,
this might be not the answer you are searching for at all, and it is only a mitigation. But as far as I know, sqlalchemy (and other ORMs) do that for you. I am mention sqlalchemy, because it has got a query builder as well. So you don't have to change your DB-Layer to full ORM, but you could let it build the queries for you.
Of course, I know that this would mean a dependency and additional complexity. I just could not leave it unmentioned ;-)
Cheers
Lars
Lars Liedtke
Software Entwickler
[Tel.] +49 721 98993-
[Fax] +49 721 98993-
[E-Mail] l...@solute.de<mailto:l...@solute.de>
solute GmbH
Zeppelinstraße 15
76185 Karlsruhe
Germany
[Logo Solute]
Marken der solute GmbH | brands of solute GmbH
[Marken]
[Advertising Partner]
Geschäftsführer | Managing Director: Dr. Thilo Gans, Bernd Vermaaten
Webseite | www.solute.de <http://www.solute.de/>
Sitz | Registered Office: Karlsruhe
Registergericht | Register Court: Amtsgericht Mannheim
Registernummer | Register No.: HRB 110579
USt-ID | VAT ID: DE234663798
Informationen zum Datenschutz | Information about privacy policy
https://www.solute.de/ger/datenschutz/grundsaetze-der-datenverarbeitung.php
Am 13.12.22 um 01:41 schrieb John K. Parejko:
this might be not the answer you are searching for at all, and it is only a mitigation. But as far as I know, sqlalchemy (and other ORMs) do that for you. I am mention sqlalchemy, because it has got a query builder as well. So you don't have to change your DB-Layer to full ORM, but you could let it build the queries for you.
Of course, I know that this would mean a dependency and additional complexity. I just could not leave it unmentioned ;-)
Cheers
Lars
Lars Liedtke
Software Entwickler
[Tel.] +49 721 98993-
[Fax] +49 721 98993-
[E-Mail] l...@solute.de<mailto:l...@solute.de>
solute GmbH
Zeppelinstraße 15
76185 Karlsruhe
Germany
[Logo Solute]
Marken der solute GmbH | brands of solute GmbH
[Marken]
[Advertising Partner]
Geschäftsführer | Managing Director: Dr. Thilo Gans, Bernd Vermaaten
Webseite | www.solute.de <http://www.solute.de/>
Sitz | Registered Office: Karlsruhe
Registergericht | Register Court: Amtsgericht Mannheim
Registernummer | Register No.: HRB 110579
USt-ID | VAT ID: DE234663798
Informationen zum Datenschutz | Information about privacy policy
https://www.solute.de/ger/datenschutz/grundsaetze-der-datenverarbeitung.php
Am 13.12.22 um 01:41 schrieb John K. Parejko:
Roel Schroeven
Dec 13, 2022, 3:51:33 AM12/13/22
to
Op 13/12/2022 om 1:41 schreef John K. Parejko:
can side-step the issue by using parametrized queries, i.e. instead of
cur.execute('SELECT name, location FROM persons WHERE name = "John
Doe"')
do
cur.execute('SELECT name, location FROM persons WHERE name = ?',
('John Doe',))
--
"Life ain't no fairy tale
Just give me another ale
And I'll drink to Rock 'n Roll"
-- Barkeep (The Scabs)
> Asking here before I file an improvement request issue on the python GitHub:
>
> sqlite has a known misfeature with double-quoted strings, whereby they will be interpreted as string literals if they don’t match a valid identifier [1]. The note in the sqlite docs describe a way to disable this misfeature at compile time or by calling an `sqlite3_db_config` C-function, but I don’t see any way to do that in the python sqlite library [2].
>
> Am I missing a way to manage this setting, or is it not available within python? This would be very useful to enable, so that python’s sqlite library will treat queries more like standard sql, instead of this particular version of MySQL. I was just burned by this, where some tests I’d written against an sqlite database did not fail in the way that they “should” have, because of this double-quoted string issue.
>
> It doesn’t look like `sqlite3_db_config` is used within the python sqlite3 codebase at all, so this might not be a trivial change? I only see two references to it in the cpython github.
>
Like Lars Liedtke this is not an exact answer to your question, but you
>
> sqlite has a known misfeature with double-quoted strings, whereby they will be interpreted as string literals if they don’t match a valid identifier [1]. The note in the sqlite docs describe a way to disable this misfeature at compile time or by calling an `sqlite3_db_config` C-function, but I don’t see any way to do that in the python sqlite library [2].
>
> Am I missing a way to manage this setting, or is it not available within python? This would be very useful to enable, so that python’s sqlite library will treat queries more like standard sql, instead of this particular version of MySQL. I was just burned by this, where some tests I’d written against an sqlite database did not fail in the way that they “should” have, because of this double-quoted string issue.
>
> It doesn’t look like `sqlite3_db_config` is used within the python sqlite3 codebase at all, so this might not be a trivial change? I only see two references to it in the cpython github.
>
can side-step the issue by using parametrized queries, i.e. instead of
cur.execute('SELECT name, location FROM persons WHERE name = "John
Doe"')
do
cur.execute('SELECT name, location FROM persons WHERE name = ?',
('John Doe',))
--
"Life ain't no fairy tale
Just give me another ale
And I'll drink to Rock 'n Roll"
-- Barkeep (The Scabs)
Chris Angelico
Dec 13, 2022, 4:10:32 AM12/13/22
to
On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net> wrote:
> Like Lars Liedtke this is not an exact answer to your question, but you
> can side-step the issue by using parametrized queries, i.e. instead of
>
> cur.execute('SELECT name, location FROM persons WHERE name = "John
> Doe"')
>
> do
>
> cur.execute('SELECT name, location FROM persons WHERE name = ?',
> ('John Doe',))
>
That's the wrong behaviour though. According to the SQL standard, the
> Like Lars Liedtke this is not an exact answer to your question, but you
> can side-step the issue by using parametrized queries, i.e. instead of
>
> cur.execute('SELECT name, location FROM persons WHERE name = "John
> Doe"')
>
> do
>
> cur.execute('SELECT name, location FROM persons WHERE name = ?',
> ('John Doe',))
>
second query should be equivalent to this:
cur.execute("SELECT name, location FROM persons WHERE name = 'John Doe'")
PostgreSQL will handle it accordingly. The question is how to get
SQLite3 to also do so.
I don't use SQLite3 much so I'm not really one to judge, but maybe it
would be worth exposing the sqlite3_db_config() function to Python?
Yes, it would be more than a trivial change, but it should be
reasonably straight-forward. In order to be useful, it would probably
also need an associated IntEnum for all those lovely opaque numbers
that define the verbs.
ChrisA
Thomas Passin
Dec 13, 2022, 8:29:38 AM12/13/22
to
On 12/13/2022 4:09 AM, Chris Angelico wrote:
> On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net> wrote:
>> Like Lars Liedtke this is not an exact answer to your question, but you
>> can side-step the issue by using parametrized queries, i.e. instead of
>>
>> cur.execute('SELECT name, location FROM persons WHERE name = "John
>> Doe"')
>>
>> do
>>
>> cur.execute('SELECT name, location FROM persons WHERE name = ?',
>> ('John Doe',))
>>
>
> That's the wrong behaviour though. According to the SQL standard, the
> second query should be equivalent to this:
>
> cur.execute("SELECT name, location FROM persons WHERE name = 'John Doe'")
>
> What the OP wanted was like your first query, and proper DBMSes like
> PostgreSQL will handle it accordingly. The question is how to get
> SQLite3 to also do so.
From reading the SQLite3 documentation on this issue (not from personal
> On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net> wrote:
>> Like Lars Liedtke this is not an exact answer to your question, but you
>> can side-step the issue by using parametrized queries, i.e. instead of
>>
>> cur.execute('SELECT name, location FROM persons WHERE name = "John
>> Doe"')
>>
>> do
>>
>> cur.execute('SELECT name, location FROM persons WHERE name = ?',
>> ('John Doe',))
>>
>
> That's the wrong behaviour though. According to the SQL standard, the
> second query should be equivalent to this:
>
> cur.execute("SELECT name, location FROM persons WHERE name = 'John Doe'")
>
> What the OP wanted was like your first query, and proper DBMSes like
> PostgreSQL will handle it accordingly. The question is how to get
> SQLite3 to also do so.
experience), in fact the second form is actually what one wants, even if
SQLite3 will usually handle the first form correctly. The rule is "Use
single quotes for string values and double quotes for database names
such as schema, table and column names; for backwards compatibility
SQLite will accept double quotes for string values, but you may get a
surprise if the string value looks like a database name."
Chris Angelico
Dec 13, 2022, 9:01:09 AM12/13/22
to
On Wed, 14 Dec 2022 at 00:30, Thomas Passin <li...@tompassin.net> wrote:
>
> On 12/13/2022 4:09 AM, Chris Angelico wrote:
> > On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net> wrote:
> >> Like Lars Liedtke this is not an exact answer to your question, but you
> >> can side-step the issue by using parametrized queries, i.e. instead of
> >>
> >> cur.execute('SELECT name, location FROM persons WHERE name = "John
> >> Doe"')
> >>
> >> do
> >>
> >> cur.execute('SELECT name, location FROM persons WHERE name = ?',
> >> ('John Doe',))
> >>
> >
> > That's the wrong behaviour though. According to the SQL standard, the
> > second query should be equivalent to this:
> >
> > cur.execute("SELECT name, location FROM persons WHERE name = 'John Doe'")
> >
> > What the OP wanted was like your first query, and proper DBMSes like
> > PostgreSQL will handle it accordingly. The question is how to get
> > SQLite3 to also do so.
>
> From reading the SQLite3 documentation on this issue (not from personal
> experience), in fact the second form is actually what one wants, even if
> SQLite3 will usually handle the first form correctly.
No, the two have distinct semantics. BOTH are valid, they just mean
>
> On 12/13/2022 4:09 AM, Chris Angelico wrote:
> > On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net> wrote:
> >> Like Lars Liedtke this is not an exact answer to your question, but you
> >> can side-step the issue by using parametrized queries, i.e. instead of
> >>
> >> cur.execute('SELECT name, location FROM persons WHERE name = "John
> >> Doe"')
> >>
> >> do
> >>
> >> cur.execute('SELECT name, location FROM persons WHERE name = ?',
> >> ('John Doe',))
> >>
> >
> > That's the wrong behaviour though. According to the SQL standard, the
> > second query should be equivalent to this:
> >
> > cur.execute("SELECT name, location FROM persons WHERE name = 'John Doe'")
> >
> > What the OP wanted was like your first query, and proper DBMSes like
> > PostgreSQL will handle it accordingly. The question is how to get
> > SQLite3 to also do so.
>
> From reading the SQLite3 documentation on this issue (not from personal
> experience), in fact the second form is actually what one wants, even if
> SQLite3 will usually handle the first form correctly.
different things.
ChrisA
Roel Schroeven
Dec 13, 2022, 9:16:06 AM12/13/22
to
Op 13/12/2022 om 14:23 schreef Thomas Passin:
> On 12/13/2022 4:09 AM, Chris Angelico wrote:
>> On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net>
>> wrote:
>>> Like Lars Liedtke this is not an exact answer to your question, but you
>>> can side-step the issue by using parametrized queries, i.e. instead of
>>>
>>> cur.execute('SELECT name, location FROM persons WHERE name =
>>> "John
>>> Doe"')
>>>
>>> do
>>>
>>> cur.execute('SELECT name, location FROM persons WHERE name = ?',
>>> ('John Doe',))
>>>
>>
>> That's the wrong behaviour though. According to the SQL standard, the
>> second query should be equivalent to this:
>>
>> cur.execute("SELECT name, location FROM persons WHERE name = 'John
>> Doe'")
>>
>> What the OP wanted was like your first query, and proper DBMSes like
>> PostgreSQL will handle it accordingly. The question is how to get
>> SQLite3 to also do so.
>
> From reading the SQLite3 documentation on this issue (not from
> personal experience), in fact the second form is actually what one
> wants, even if SQLite3 will usually handle the first form correctly.
>> On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <ro...@roelschroeven.net>
>> wrote:
>>> Like Lars Liedtke this is not an exact answer to your question, but you
>>> can side-step the issue by using parametrized queries, i.e. instead of
>>>
>>> cur.execute('SELECT name, location FROM persons WHERE name =
>>> "John
>>> Doe"')
>>>
>>> do
>>>
>>> cur.execute('SELECT name, location FROM persons WHERE name = ?',
>>> ('John Doe',))
>>>
>>
>> That's the wrong behaviour though. According to the SQL standard, the
>> second query should be equivalent to this:
>>
>> cur.execute("SELECT name, location FROM persons WHERE name = 'John
>> Doe'")
>>
>> What the OP wanted was like your first query, and proper DBMSes like
>> PostgreSQL will handle it accordingly. The question is how to get
>> SQLite3 to also do so.
>
> From reading the SQLite3 documentation on this issue (not from
> personal experience), in fact the second form is actually what one
> wants, even if SQLite3 will usually handle the first form correctly.
> The rule is "Use single quotes for string values and double quotes for
> database names such as schema, table and column names; for backwards
> compatibility SQLite will accept double quotes for string values, but
> you may get a surprise if the string value looks like a database name."
What I missed at first is the case where you really want to use an
> database names such as schema, table and column names; for backwards
> compatibility SQLite will accept double quotes for string values, but
> you may get a surprise if the string value looks like a database name."
identifier, not a string. Then you use double quotes, and would like to
get an error ("unknown identifier" or something like that) in case of a
typo, instead of the database engine silently presuming your
wrongly-spelled identifier is a string. That case can't be solved with
parametrized queries, and does really require the ability to enable more
strict behavior.
+1 to expose the sqlite3_db_config() function, or maybe just a special
case for this specific option.
--
"Honest criticism is hard to take, particularly from a relative, a friend,
an acquaintance, or a stranger."
-- Franklin P. Jones
Roel Schroeven
Dec 13, 2022, 9:19:43 AM12/13/22
to
Op 13/12/2022 om 15:15 schreef Roel Schroeven:
this behavior. That would make it possible to customize from any client.
>
> +1 to expose the sqlite3_db_config() function, or maybe just a special
> case for this specific option.
>
Actually I'm surprised SQLite doesn't have a PRAGMA command to customize
> +1 to expose the sqlite3_db_config() function, or maybe just a special
> case for this specific option.
>
this behavior. That would make it possible to customize from any client.
Roel Schroeven
Dec 13, 2022, 1:59:01 PM12/13/22
to
Stefan Ram schreef op 13/12/2022 om 8:42:
> allowed to contain special characters.
Or that are equal SQL keywords, which can be a reason to double-quote
them. SQL engines sometimes add new keywords; explicitly marking string
literals as string literals prevents future conflicts and confusion.
Perhaps it's a better idea to use [identifier] or `identifier` instead
though (I just learned about those on
https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
used in MS Access and SQL Server, `` is used in MySQL) but both work in
SQLite. That should prevent any ambiguity and confusion, if it doesn't
bother you too much that it's not standard SQL.
--
"I love science, and it pains me to think that to so many are terrified
of the subject or feel that choosing science means you cannot also
choose compassion, or the arts, or be awed by nature. Science is not
meant to cure us of mystery, but to reinvent and reinvigorate it."
-- Robert Sapolsky
> "John K. Parejko" <pare...@gmail.com> writes:
> >I was just burned by this, where some tests I’d written
> >against an sqlite database did not fail in the way that they
> >“should” have, because of this double-quoted string issue.
>
> In standard SQL, double quotes denote identifiers that are
> >I was just burned by this, where some tests I’d written
> >against an sqlite database did not fail in the way that they
> >“should” have, because of this double-quoted string issue.
>
> allowed to contain special characters.
Or that are equal SQL keywords, which can be a reason to double-quote
them. SQL engines sometimes add new keywords; explicitly marking string
literals as string literals prevents future conflicts and confusion.
Perhaps it's a better idea to use [identifier] or `identifier` instead
though (I just learned about those on
https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
used in MS Access and SQL Server, `` is used in MySQL) but both work in
SQLite. That should prevent any ambiguity and confusion, if it doesn't
bother you too much that it's not standard SQL.
--
"I love science, and it pains me to think that to so many are terrified
of the subject or feel that choosing science means you cannot also
choose compassion, or the arts, or be awed by nature. Science is not
meant to cure us of mystery, but to reinvent and reinvigorate it."
-- Robert Sapolsky
Chris Angelico
Dec 13, 2022, 2:02:18 PM12/13/22
to
On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
>
> Stefan Ram schreef op 13/12/2022 om 8:42:
> > "John K. Parejko" <pare...@gmail.com> writes:
>
> Stefan Ram schreef op 13/12/2022 om 8:42:
> > "John K. Parejko" <pare...@gmail.com> writes:
> > >I was just burned by this, where some tests I’d written
> > >against an sqlite database did not fail in the way that they
> > >“should” have, because of this double-quoted string issue.
> >
> > >against an sqlite database did not fail in the way that they
> > >“should” have, because of this double-quoted string issue.
> >
> > In standard SQL, double quotes denote identifiers that are
> > allowed to contain special characters.
> Or that are equal SQL keywords, which can be a reason to double-quote
> them. SQL engines sometimes add new keywords; explicitly marking string
> literals as string literals prevents future conflicts and confusion.
>
> Perhaps it's a better idea to use [identifier] or `identifier` instead
> though (I just learned about those on
> https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> used in MS Access and SQL Server, `` is used in MySQL) but both work in
> SQLite. That should prevent any ambiguity and confusion, if it doesn't
> bother you too much that it's not standard SQL.
>
Why not just use "identifier" which is standard SQL?
> > allowed to contain special characters.
> Or that are equal SQL keywords, which can be a reason to double-quote
> them. SQL engines sometimes add new keywords; explicitly marking string
> literals as string literals prevents future conflicts and confusion.
>
> Perhaps it's a better idea to use [identifier] or `identifier` instead
> though (I just learned about those on
> https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> used in MS Access and SQL Server, `` is used in MySQL) but both work in
> SQLite. That should prevent any ambiguity and confusion, if it doesn't
> bother you too much that it's not standard SQL.
>
ChrisA
Roel Schroeven
Dec 13, 2022, 4:18:45 PM12/13/22
to
Chris Angelico schreef op 13/12/2022 om 20:01:
produce an unknown identifier error, alerting you immediately to your typo.
If you accidentally type "identifire", SQLite will silently treat it as
a string literal instead of an identifier, causing more difficult to
diagnose problems.
--
"In the old days, writers used to sit in front of a typewriter and stare out of
the window. Nowadays, because of the marvels of convergent technology, the thing
you type on and the window you stare out of are now the same thing.”
-- Douglas Adams
> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
> >
> > Stefan Ram schreef op 13/12/2022 om 8:42:
> > > "John K. Parejko" <pare...@gmail.com> writes:
> >
> > Stefan Ram schreef op 13/12/2022 om 8:42:
> > > "John K. Parejko" <pare...@gmail.com> writes:
> > > >I was just burned by this, where some tests I’d written
> > > >against an sqlite database did not fail in the way that they
> > > >“should” have, because of this double-quoted string issue.
> > >
> > > >against an sqlite database did not fail in the way that they
> > > >“should” have, because of this double-quoted string issue.
> > >
> > > In standard SQL, double quotes denote identifiers that are
> > > allowed to contain special characters.
> > Or that are equal SQL keywords, which can be a reason to double-quote
> > them. SQL engines sometimes add new keywords; explicitly marking string
> > literals as string literals prevents future conflicts and confusion.
> >
> > Perhaps it's a better idea to use [identifier] or `identifier` instead
> > though (I just learned about those on
> > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > bother you too much that it's not standard SQL.
> >
>
> Why not just use "identifier" which is standard SQL?
If you accidentally type [identifire] or `identifire`, SQLite will
> > > allowed to contain special characters.
> > Or that are equal SQL keywords, which can be a reason to double-quote
> > them. SQL engines sometimes add new keywords; explicitly marking string
> > literals as string literals prevents future conflicts and confusion.
> >
> > Perhaps it's a better idea to use [identifier] or `identifier` instead
> > though (I just learned about those on
> > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > bother you too much that it's not standard SQL.
> >
>
> Why not just use "identifier" which is standard SQL?
produce an unknown identifier error, alerting you immediately to your typo.
If you accidentally type "identifire", SQLite will silently treat it as
a string literal instead of an identifier, causing more difficult to
diagnose problems.
--
"In the old days, writers used to sit in front of a typewriter and stare out of
the window. Nowadays, because of the marvels of convergent technology, the thing
you type on and the window you stare out of are now the same thing.”
-- Douglas Adams
Roel Schroeven
Dec 13, 2022, 4:36:51 PM12/13/22
to
Roel Schroeven schreef op 13/12/2022 om 22:18:
-- Preparation:
sqlite> create table foo ("columna" text, "columnb" text);
sqlite> insert into foo values ("xyzzy", "xyzzy");
-- Variant with "":
sqlite> select count(*) from foo where "columna" = "colummb";
0
Not at all at first sight clear why there seem to be no matching rows,
if you even notice straightaway that the result is not correct.
-- Variant with []:
sqlite> select count(*) from foo where [columna] = [colummb];
Error: no such column: colummb
Immediately clear that there is a problem, and what the problem is.
> Chris Angelico schreef op 13/12/2022 om 20:01:
> > > Perhaps it's a better idea to use [identifier] or `identifier` instead
> > > though (I just learned about those on
> > > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > > bother you too much that it's not standard SQL.
> > >
> >
> > Why not just use "identifier" which is standard SQL?
>
> If you accidentally type [identifire] or `identifire`, SQLite will
> produce an unknown identifier error, alerting you immediately to your typo.
> If you accidentally type "identifire", SQLite will silently treat it as
> a string literal instead of an identifier, causing more difficult to
> diagnose problems.
Example:
> > > though (I just learned about those on
> > > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > > bother you too much that it's not standard SQL.
> > >
> >
> > Why not just use "identifier" which is standard SQL?
>
> If you accidentally type [identifire] or `identifire`, SQLite will
> produce an unknown identifier error, alerting you immediately to your typo.
> If you accidentally type "identifire", SQLite will silently treat it as
> a string literal instead of an identifier, causing more difficult to
> diagnose problems.
-- Preparation:
sqlite> create table foo ("columna" text, "columnb" text);
sqlite> insert into foo values ("xyzzy", "xyzzy");
-- Variant with "":
sqlite> select count(*) from foo where "columna" = "colummb";
0
Not at all at first sight clear why there seem to be no matching rows,
if you even notice straightaway that the result is not correct.
-- Variant with []:
sqlite> select count(*) from foo where [columna] = [colummb];
Error: no such column: colummb
Immediately clear that there is a problem, and what the problem is.
Roel Schroeven
Dec 13, 2022, 4:45:03 PM12/13/22
to
Roel Schroeven schreef op 13/12/2022 om 22:36:
there instead of double quotes. It's a bad habit from using MySQL for
too long I guess.
> sqlite> insert into foo values ("xyzzy", "xyzzy");
SQLite accepts it like that, but I really should have used single quotes
there instead of double quotes. It's a bad habit from using MySQL for
too long I guess.
Chris Angelico
Dec 13, 2022, 4:58:50 PM12/13/22
to
On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <ro...@roelschroeven.net> wrote:
>
> Chris Angelico schreef op 13/12/2022 om 20:01:
>
> Chris Angelico schreef op 13/12/2022 om 20:01:
> > On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
> > >
> > > Stefan Ram schreef op 13/12/2022 om 8:42:
> > > > "John K. Parejko" <pare...@gmail.com> writes:
> > >
> > > Stefan Ram schreef op 13/12/2022 om 8:42:
> > > > "John K. Parejko" <pare...@gmail.com> writes:
> > > > >I was just burned by this, where some tests I’d written
> > > > >against an sqlite database did not fail in the way that they
> > > > >“should” have, because of this double-quoted string issue.
> > > >
> > > > >against an sqlite database did not fail in the way that they
> > > > >“should” have, because of this double-quoted string issue.
> > > >
> > > > In standard SQL, double quotes denote identifiers that are
> > > > allowed to contain special characters.
> > > Or that are equal SQL keywords, which can be a reason to double-quote
> > > them. SQL engines sometimes add new keywords; explicitly marking string
> > > literals as string literals prevents future conflicts and confusion.
> > >
> > > > allowed to contain special characters.
> > > Or that are equal SQL keywords, which can be a reason to double-quote
> > > them. SQL engines sometimes add new keywords; explicitly marking string
> > > literals as string literals prevents future conflicts and confusion.
> > >
> > > Perhaps it's a better idea to use [identifier] or `identifier` instead
> > > though (I just learned about those on
> > > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > > bother you too much that it's not standard SQL.
> > >
> >
> > Why not just use "identifier" which is standard SQL?
>
> If you accidentally type [identifire] or `identifire`, SQLite will
> produce an unknown identifier error, alerting you immediately to your typo.
> If you accidentally type "identifire", SQLite will silently treat it as
> a string literal instead of an identifier, causing more difficult to
> diagnose problems.
>
Okay, so..... exactly the same as if you use standard double quotes,
> > > though (I just learned about those on
> > > https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
> > > used in MS Access and SQL Server, `` is used in MySQL) but both work in
> > > SQLite. That should prevent any ambiguity and confusion, if it doesn't
> > > bother you too much that it's not standard SQL.
> > >
> >
> > Why not just use "identifier" which is standard SQL?
>
> If you accidentally type [identifire] or `identifire`, SQLite will
> produce an unknown identifier error, alerting you immediately to your typo.
> If you accidentally type "identifire", SQLite will silently treat it as
> a string literal instead of an identifier, causing more difficult to
> diagnose problems.
>
but change the configuration option. So the options are: make
everything worse for everyone by exacerbating the problem of
non-standard identifier quoting, or get this API so SQLite can be
configured, like the OP actually asked for.
Yeah. Let's not do the wrong thing.
ChrisA
Roel Schroeven
Dec 13, 2022, 6:34:51 PM12/13/22
to
Chris Angelico schreef op 13/12/2022 om 22:58:
other. Having the option to change SQLite's behavior is clearly the
better solution if/when that happens (and is released and available in
our development environments), but that doesn't mean there's no value in
having a workaround here and now.
--
"Most quotes are misattributed"
-- Einstein
> Okay, so..... exactly the same as if you use standard double quotes,
> but change the configuration option. So the options are: make
> everything worse for everyone by exacerbating the problem of
> non-standard identifier quoting, or get this API so SQLite can be
> configured, like the OP actually asked for.
>
I'm not advocating for one above the other, I think they complement each
> but change the configuration option. So the options are: make
> everything worse for everyone by exacerbating the problem of
> non-standard identifier quoting, or get this API so SQLite can be
> configured, like the OP actually asked for.
>
other. Having the option to change SQLite's behavior is clearly the
better solution if/when that happens (and is released and available in
our development environments), but that doesn't mean there's no value in
having a workaround here and now.
--
"Most quotes are misattributed"
-- Einstein
John K. Parejko
Dec 15, 2022, 2:19:19 PM12/15/22
to
Thanks for the discussion. I’m aware that SQLite has several different options for identifier quoting, but they’re not cross-compatible with other SQL, whereas double quotes are (modulo this strange SQLite behavior).
Is anyone here familiar with the python sqlite3 implementation? I wonder how hard it would be to raise up the `sqlite3_db_config` generically, or have a specific function to set just the DQS_DDL and DQS_DML settings? It looks like everything interesting is in `Modules/_sqlite/module.c`, but I’m not familiar with the cpython internals.
John
> On 13Dec 2022, at 13:58, Chris Angelico <ros...@gmail.com> wrote:
>
> On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>
>> Chris Angelico schreef op 13/12/2022 om 20:01:
>>> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>>>
>>>> Stefan Ram schreef op 13/12/2022 om 8:42:
>>>>> "John K. Parejko" <pare...@gmail.com> writes:
> https://mail.python.org/mailman/listinfo/python-list
Is anyone here familiar with the python sqlite3 implementation? I wonder how hard it would be to raise up the `sqlite3_db_config` generically, or have a specific function to set just the DQS_DDL and DQS_DML settings? It looks like everything interesting is in `Modules/_sqlite/module.c`, but I’m not familiar with the cpython internals.
John
> On 13Dec 2022, at 13:58, Chris Angelico <ros...@gmail.com> wrote:
>
> On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>
>> Chris Angelico schreef op 13/12/2022 om 20:01:
>>> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>>>
>>>> Stefan Ram schreef op 13/12/2022 om 8:42:
>>>>> "John K. Parejko" <pare...@gmail.com> writes:
>>>>>> I was just burned by this, where some tests I’d written
>>>>>> against an sqlite database did not fail in the way that they
>>>>>> “should” have, because of this double-quoted string issue.
>>>>>
>>>>>> against an sqlite database did not fail in the way that they
>>>>>> “should” have, because of this double-quoted string issue.
>>>>>
>>>>> In standard SQL, double quotes denote identifiers that are
>>>>> allowed to contain special characters.
>>>> Or that are equal SQL keywords, which can be a reason to double-quote
>>>> them. SQL engines sometimes add new keywords; explicitly marking string
>>>> literals as string literals prevents future conflicts and confusion.
>>>>
>>>> Perhaps it's a better idea to use [identifier] or `identifier` instead
>>>> though (I just learned about those on
>>>> https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
>>>> used in MS Access and SQL Server, `` is used in MySQL) but both work in
>>>> SQLite. That should prevent any ambiguity and confusion, if it doesn't
>>>> bother you too much that it's not standard SQL.
>>>>
>>>
>>> Why not just use "identifier" which is standard SQL?
>>
>> If you accidentally type [identifire] or `identifire`, SQLite will
>> produce an unknown identifier error, alerting you immediately to your typo.
>> If you accidentally type "identifire", SQLite will silently treat it as
>> a string literal instead of an identifier, causing more difficult to
>> diagnose problems.
>>
>
>>>>> allowed to contain special characters.
>>>> Or that are equal SQL keywords, which can be a reason to double-quote
>>>> them. SQL engines sometimes add new keywords; explicitly marking string
>>>> literals as string literals prevents future conflicts and confusion.
>>>>
>>>> Perhaps it's a better idea to use [identifier] or `identifier` instead
>>>> though (I just learned about those on
>>>> https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
>>>> used in MS Access and SQL Server, `` is used in MySQL) but both work in
>>>> SQLite. That should prevent any ambiguity and confusion, if it doesn't
>>>> bother you too much that it's not standard SQL.
>>>>
>>>
>>> Why not just use "identifier" which is standard SQL?
>>
>> If you accidentally type [identifire] or `identifire`, SQLite will
>> produce an unknown identifier error, alerting you immediately to your typo.
>> If you accidentally type "identifire", SQLite will silently treat it as
>> a string literal instead of an identifier, causing more difficult to
>> diagnose problems.
>>
>
> Okay, so..... exactly the same as if you use standard double quotes,
> but change the configuration option. So the options are: make
> everything worse for everyone by exacerbating the problem of
> non-standard identifier quoting, or get this API so SQLite can be
> configured, like the OP actually asked for.
>
> but change the configuration option. So the options are: make
> everything worse for everyone by exacerbating the problem of
> non-standard identifier quoting, or get this API so SQLite can be
> configured, like the OP actually asked for.
>
> Yeah. Let's not do the wrong thing.
>
> ChrisA
> --
>
> ChrisA
> https://mail.python.org/mailman/listinfo/python-list
Thomas Passin
Dec 15, 2022, 3:14:23 PM12/15/22
to
There is a Python adapter for SQLITE called "APSW". It has a config()
function. I looked in the codebase and it defines the two configuration
constants needed to turn off the double quote behavior (see
https://sqlite.org/quirks.html). These constants are
SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML.
This makes me think that the double-quote behavior can be turned off by
Python code, though I haven't tried it.
From the APSW docs (see
https://rogerbinns.github.io/apsw/tips.html#about-python-apsw-and-sqlite-versions):
"APSW wraps the SQLite C API. That means when SQLite adds new constant
or API, then so does APSW. You can think of APSW as the Python
expression of SQLite’s C API. You can lookup SQLite APIs to find which
APSW functions and attributes call them."
On 12/15/2022 2:18 PM, John K. Parejko wrote:
> Thanks for the discussion. I’m aware that SQLite has several different options for identifier quoting, but they’re not cross-compatible with other SQL, whereas double quotes are (modulo this strange SQLite behavior).
>
> Is anyone here familiar with the python sqlite3 implementation? I wonder how hard it would be to raise up the `sqlite3_db_config` generically, or have a specific function to set just the DQS_DDL and DQS_DML settings? It looks like everything interesting is in `Modules/_sqlite/module.c`, but I’m not familiar with the cpython internals.
>
> John
>
>> On 13Dec 2022, at 13:58, Chris Angelico <ros...@gmail.com> wrote:
>>
>> On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>>
>>> Chris Angelico schreef op 13/12/2022 om 20:01:
>>>> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>>>>
>>>>> Stefan Ram schreef op 13/12/2022 om 8:42:
>>>>>> "John K. Parejko" <pare...@gmail.com> writes:
function. I looked in the codebase and it defines the two configuration
constants needed to turn off the double quote behavior (see
https://sqlite.org/quirks.html). These constants are
SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML.
This makes me think that the double-quote behavior can be turned off by
Python code, though I haven't tried it.
From the APSW docs (see
https://rogerbinns.github.io/apsw/tips.html#about-python-apsw-and-sqlite-versions):
"APSW wraps the SQLite C API. That means when SQLite adds new constant
or API, then so does APSW. You can think of APSW as the Python
expression of SQLite’s C API. You can lookup SQLite APIs to find which
APSW functions and attributes call them."
On 12/15/2022 2:18 PM, John K. Parejko wrote:
> Thanks for the discussion. I’m aware that SQLite has several different options for identifier quoting, but they’re not cross-compatible with other SQL, whereas double quotes are (modulo this strange SQLite behavior).
>
> Is anyone here familiar with the python sqlite3 implementation? I wonder how hard it would be to raise up the `sqlite3_db_config` generically, or have a specific function to set just the DQS_DDL and DQS_DML settings? It looks like everything interesting is in `Modules/_sqlite/module.c`, but I’m not familiar with the cpython internals.
>
> John
>
>> On 13Dec 2022, at 13:58, Chris Angelico <ros...@gmail.com> wrote:
>>
>> On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>>
>>> Chris Angelico schreef op 13/12/2022 om 20:01:
>>>> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <ro...@roelschroeven.net> wrote:
>>>>>
>>>>> Stefan Ram schreef op 13/12/2022 om 8:42:
>>>>>> "John K. Parejko" <pare...@gmail.com> writes:
>>>>>>> I was just burned by this, where some tests I’d written
>>>>>>> against an sqlite database did not fail in the way that they
>>>>>>> “should” have, because of this double-quoted string issue.
>>>>>>
>>>>>>> against an sqlite database did not fail in the way that they
>>>>>>> “should” have, because of this double-quoted string issue.
>>>>>>
0 new messages