sqlite3 double quote behavior

[Thomas Passin]

On 12/13/2022 4:09 AM, Chris Angelico wrote:
> On Tue, 13 Dec 2022 at 19:52, Roel Schroeven <roel at 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."

> 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