sqlite3 double quote behavior

[Lars Liedtke]

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]        lal at solute.de<mailto:lal at 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:

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