How do you escape strings for SQLite table/column names in Python?

To convert any string into a SQLite identifier:

• Ensure the string can be encoded as UTF-8.
• Ensure the string does not include any NUL characters.
• Replace all " with "".
• Wrap the entire thing in double quotes.

Implementation

import codecs

def quote_identifier(s, errors="strict"):
    encodable = s.encode("utf-8", errors).decode("utf-8")

    nul_index = encodable.find("\x00")

    if nul_index >= 0:
        error = UnicodeEncodeError("NUL-terminated utf-8", encodable,
                                   nul_index, nul_index + 1, "NUL not allowed")
        error_handler = codecs.lookup_error(errors)
        replacement, _ = error_handler(error)
        encodable = encodable.replace("\x00", replacement)

    return "\"" + encodable.replace("\"", "\"\"") + "\""

Given a string single argument, it will escape and quote it correctly or raise an exception. The second argument can be used to specify any error handler registered in the codecs module. The built-in ones are:

• 'strict': raise an exception in case of an encoding error
• 'replace': replace malformed data with a suitable replacement marker, such as '?' or '\ufffd'
• 'ignore': ignore malformed data and continue without further notice
• 'xmlcharrefreplace': replace with the appropriate XML character reference (for encoding only)
• 'backslashreplace': replace with backslashed escape sequences (for encoding only)

This doesn't check for reserved identifiers, so if you try to create a new SQLITE_MASTER table it won't stop you.

Example Usage

import sqlite3

def test_identifier(identifier):
    "Tests an identifier to ensure it's handled properly."

    with sqlite3.connect(":memory:") as c:
        c.execute("CREATE TABLE " + quote_identifier(identifier) + " (foo)")
        assert identifier == c.execute("SELECT name FROM SQLITE_MASTER").fetchone()[0]

test_identifier("'Héllo?'\\\n\r\t\"Hello!\" -☃") # works
test_identifier("北方话") # works
test_identifier(chr(0x20000)) # works

print(quote_identifier("Fo\x00o!", "replace")) # prints "Fo?o!"
print(quote_identifier("Fo\x00o!", "ignore")) # prints "Foo!"
print(quote_identifier("Fo\x00o!")) # raises UnicodeEncodeError
print(quote_identifier(chr(0xD800))) # raises UnicodeEncodeError

Observations and References

• SQLite identifiers are TEXT, not binary.
  • SQLITE_MASTER schema in the FAQ
  • Python 2 SQLite API yelled at me when I gave it bytes it couldn't decode as text.
  • Python 3 SQLite API requires queries be strs, not bytes.

• SQLite identifiers are quoted using double-quotes.
  • SQL as Understood by SQLite

• Double-quotes in SQLite identifiers are escaped as two double quotes.

• SQLite identifiers preserve case, but they are case-insensitive towards ASCII letters. It is possible to enable unicode-aware case-insensitivity.
  • SQLite FAQ Question #18

• SQLite does not support the NUL character in strings or identifiers.
  • SQLite Ticket 57c971fc74

• sqlite3 can handle any other unicode string as long as it can be properly encoded to UTF-8. Invalid strings could cause crashes between Python 3.0 and Python 3.1.2 or thereabouts. Python 2 accepted these invalid strings, but this is considered a bug.
  • Python Issue #12569
  • Modules/_sqlite/cursor.c
  • I tested it a bunch.

The psycopg2 documentation explicitly recommends using normal python % or {} formatting to substitute in table and column names (or other bits of dynamic syntax), and then using the parameter mechanism to substitute values into the query.

I disagree with everyone who is saying "don't ever use dynamic table/column names, you're doing something wrong if you need to". I write programs to automate stuff with databases every day, and I do it all the time. We have lots of databases with lots of tables, but they are all built on repeated patterns, so generic code to handle them is extremely useful. Hand-writing the queries every time would be far more error prone and dangerous.

It comes down to what "safe" means. The conventional wisdom is that using normal python string manipulation to put values into your queries is not "safe". This is because there are all sorts of things that can go wrong if you do that, and such data very often comes from the user and is not in your control. You need a 100% reliable way of escaping these values properly so that a user cannot inject SQL in a data value and have the database execute it. So the library writers do this job; you never should.

If, however, you're writing generic helper code to operate on things in databases, then these considerations don't apply as much. You are implicitly giving anyone who can call such code access to everything in the database; that's the point of the helper code. So now the safety concern is making sure that user-generated data can never be used in such code. This is a general security issue in coding, and is just the same problem as blindly execing a user-input string. It's a distinct issue from inserting values into your queries, because there you want to be able to safely handle user-input data.

So my recommendation is: do whatever you want to dynamically assemble your queries. Use normal python string templating to sub in table and column names, glue on where clauses and joins, all the good (and horrible to debug) stuff. But make sure you're aware that whatever values such code touches has to come from you, not your users[1]. Then you use SQLite's parameter substitution functionality to safely insert user-input values into your queries as values.

[1] If (as is the case for a lot of the code I write) your users are the people who have full access to databases anyway and the code is to simplify their work, then this consideration doesn't really apply; you probably are assembling queries on user-specified tables. But you should still use SQLite's parameter substitution to save yourself from the inevitable genuine value that eventually contains quotes or percent signs.