sqlite3 double quote behavior

[Roel Schroeven]

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 <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."
What I missed at first is the case where you really want to use an 
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