How do you escape strings for SQLite table/column

Question

The standard approach for using variable values in SQLite queries is the "question mark style", like this:

import sqlite3
with sqlite3.connect(":memory:") as connection:
    connection.execute("CREATE TABLE foo(bar)")
    connection.execute("INSERT INTO foo(bar) VALUES (?)", ("cow",))

    print(list(connection.execute("SELECT * from foo")))
    # prints [(u'cow',)]

However, this only works for substituting values into queries. It fails when used for table or column names:

import sqlite3
with sqlite3.connect(":memory:") as connection:
    connection.execute("CREATE TABLE foo(?)", ("bar",))
    # raises sqlite3.OperationalError: near "?": syntax error

Neither the sqlite3 module nor PEP 249 mention a function for escaping names or values. Presumably this is to discourage users from assembling their queries with strings, but it leaves me at a loss.

What function or technique is most appropriate for using variable names for columns or tables in SQLite? I'd would strongly prefer to do able to do this without any other dependencies, since I'll be using it in my own wrapper.

I looked for but couldn't find a clear and complete description of the relevant part of SQLite's syntax, to use to write my own function. I want to be sure this will work for any identifier permitted by SQLite, so a trial-and-error solution is too uncertain for me.

SQLite uses " to quote identifiers but I'm not sure that just escaping them is sufficient. PHP's sqlite_escape_string function's documentation suggests that certain binary data may need to be escaped as well, but that may be a quirk of the PHP library.

Answer 1

Placeholders are only for values. Column and table names are structural, and are akin to variable names; you can't use placeholders to fill them in.

You have three options:

1. Appropriately escape/quote the column name everywhere you use it. This is fragile and dangerous.
2. Use an ORM like SQLAlchemy, which will take care of escaping/quoting for you.
3. Ideally, just don't have dynamic column names. Tables and columns are for structure; anything dynamic is data and should be in the table rather than part of it.

Answer 2

From the sqlite faq, question 24 (the formulation of the question of course does not give a clue that the answer may be useful to your question):

SQL uses double-quotes around identifiers (column or table names) that contains special characters or which are keywords. So double-quotes are a way of escaping identifier names.

If the name itself contains double quotes, escape that double quote with another one.