python - 如何在 Python 中转义 SQLite 表/列名的字符串？

Question

在 SQLite 查询中使用变量值的标准方法是“问号样式”，如下所示:

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',)]

但是，这仅适用于将值替换为查询。用于表名或列名时失败:

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

sqlite3 模块和 PEP 249 都不是提及用于转义名称或值的函数。大概这是为了阻止用户使用字符串组合他们的查询，但这让我不知所措。

什么函数或技术最适合在 SQLite 中为列或表使用变量名？我强烈希望能够在没有任何其他依赖项的情况下做到这一点，因为我将在自己的包装器中使用它。

我寻找但找不到对 SQLite 语法相关部分的清晰完整的描述，用于编写我自己的函数。我想确保这适用于 SQLite 允许的任何标识符，因此试错解决方案对我来说太不确定了。

SQLite uses " to quote identifiers但我不确定仅仅逃避它们就足够了。 PHP 的 sqlite_escape_string函数的文档表明某些二进制数据可能也需要转义，但这可能是 PHP 库的一个怪癖。

Answer 1

将任何字符串转换为 SQLite 标识符:

• 确保字符串可以编码为 UTF-8。
• 确保字符串不包含任何 NUL 字符。
• 将所有 " 替换为 ""。
• 将整个内容用双引号括起来。

实现

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

给定一个字符串单个参数，它将转义并正确引用它或引发异常。第二个参数可用于指定在 the codecs module 中注册的任何错误处理程序。 .内置的是:

• '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)

这不会检查保留标识符，因此如果您尝试创建一个新的 SQLITE_MASTER 表，它不会阻止您。

示例用法

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

观察和引用

• SQLite 标识符为 TEXT ，而不是二进制。
  • SQLITE_MASTER schema in the FAQ
  • 当我给 Python 2 SQLite API 提供无法解码为文本的字节时，它对我大喊大叫。
  • Python 3 SQLite API 要求查询是 str，而不是 bytes。

• SQLite 标识符使用双引号引起来。
  • SQL as Understood by SQLite

• SQLite 标识符中的双引号被转义为两个双引号。

• SQLite 标识符保留大小写，但它们对 ASCII 字母不区分大小写。可以启用 unicode-aware 不区分大小写。
  • SQLite FAQ Question #18

• SQLite 不支持 NUL character在字符串或标识符中。
  • SQLite Ticket 57c971fc74

• sqlite3 可以处理任何其他 unicode 字符串，只要它可以正确编码为 UTF-8。无效的字符串可能会导致 Python 3.0 和 Python 3.1.2 或更高版本之间的崩溃。 Python 2 接受了这些无效字符串，但这被认为是一个错误。
  • Python Issue #12569
  • Modules/_sqlite/cursor.c
  • 我测试了一堆。