Can't escape table or column names when importing data model from XML using SQLite

Question

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

I would like to import an entire data model from an XML file using SQLite, and while I know how to programmatically insert/escape values using the ? syntax in queries, this does not work for the names of tables or columns when creating those. I could simply put that in the query string using Python, however this doesn't feel right and I would think there would be a proper way to insert these values so that they are escaped, to prevent SQL injections.

Answer 1

Here's a similar question. It doesn't look like there's any way to do what you're asking through the sqlite module(s). Most people just include the table name literally. If you're really paranoid, you could write a function to see if a table name looks valid, maybe something like:

import string

def isValidTableName(name):
    allowed_chs = string.letters + string.digits + '_'
    return len(name) == len([ch for ch in name if ch in allowed_chs])

Table names are generally limited to letters, numbers, and underscores, so this would be a reasonable way to check for injection. Just don't let anything that doesn't pass through.

Answer 2

I posted a solution that seems to work in the other question. Here's the function:

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("\"", "\"\"") + "\""

It doesn't warn you about reserved identifiers, so you have to worry about that yourself.