Is it possible to stop processing sqlite with double quotes as strings?

Question

According to: http://www.sqlite.org/draft/lang_keywords.html

SQLite3 will do what you expect if you do:

select "foo" from bar; 

But if the identifier does not exist:

 select "non-existant" from bar; 

It is returned (for compatibility with older versions, I suppose), to process quoted text as a string.

This causes problems for me, as I dynamically create queries using quoted columns like this, and the latter behavior returns meaningless results, rather than throwing an error.

I am writing python code and using the module that wraps the PEP-249 Python Database API Specification v2.0, so I can put specific hacks into the database where necessary.

Our database database may change (and perhaps at some point it will probably be different for local testing and production), so I want, if possible, to support the SQL standard itself.

Is there a way I can:

• stop / disable this behavior.
• easily / reliably detect that this happened (and, for example, raise my own exception)
• the workaround is somehow (I don't assume that replacing "in sql with a non-standard equivalent is easy to do in a programmatically safe way)

Answer 1

If column names are prefixed with table names or aliases, they cannot be misinterpreted:

 SELECT bar."foo", a."baz" FROM bar, blah AS a 

When you are processing multiple tables, you probably need to use this anyway to avoid column name conflicts.

Answer 2

Do not use quotation marks, use [ and ]

 sqlite> create table blah (name text); sqlite> select "something" from blah; sqlite> select "name" from blah; sqlite> insert into blah values ('hello'); sqlite> select "something" from blah; something sqlite> select "name" from blah; hello sqlite> select [something] from blah; Error: no such column: something sqlite> select [name] from blah; hello sqlite> 

Software cloning attempt:

 import re from itertools import cycle s = 'select "something" from blah;' sel, cols, rest = re.match(r'(select)\s+(.*?)\s+(from\s+.*)', s).groups() cols = re.sub('"', lambda g, I=cycle('[]'): next(I), cols) print ' '.join( (sel, cols, rest) ) # select [something] from blah;