你如何在 Python 中转义 SQLite 表/列名称的字符串? [英] How do you escape strings for SQLite table/column names in Python?

问题描述

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

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

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

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.

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

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.

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

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 使用 " 来引用标识符 但我不确定是否只是转义它们就足够了.PHP 的 sqlite_escape_string 函数的文档表明某些二进制数据也可能需要转义，但这可能是 PHP 库的一个怪癖.

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.

推荐答案

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

To convert any string into a SQLite identifier:

• 确保字符串可以编码为 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("\"", "\"\"") + "\""

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

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':在编码错误的情况下引发异常
• 'replace':用合适的替换标记替换格式错误的数据，例如 '?' 或 '\ufffd'
• 'ignore':忽略格式错误的数据并继续，恕不另行通知
• 'xmlcharrefreplace':替换为适当的 XML 字符引用(仅用于编码)
• 'backslashreplace':用反斜线转义序列替换(仅用于编码)

• '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 表，它不会阻止您.

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

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 常见问题解答中的架构
  • Python 2 SQLite API 在我给它无法解码为文本的字节时对我大喊大叫.
  • Python 3 SQLite API 要求查询是 strs，而不是 bytes.

  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 标识符使用双引号引用.
      • SQLite 理解的 SQL
      • SQLite 标识符中的双引号被转义为两个双引号.
      • SQLite 标识符保留大小写，但它们对 ASCII 字母不区分大小写.可以启用 unicode-aware 大小写不敏感.
        • SQLite FAQ 问题 #18
        • SQLite 不支持字符串或标识符中的 NUL 字符.
          • SQLite 票证 57c971fc74
          • sqlite3 可以处理任何其他 unicode 字符串，只要它可以正确编码为 UTF-8.无效的字符串可能会导致 Python 3.0 和 Python 3.1.2 或其他版本之间的崩溃.Python 2 接受了这些无效字符串，但这被认为是一个错误.
            • Python 问题 #12569
            • 模块/_sqlite/cursor.c
            • 我对其进行了大量测试.
            • 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.

              这篇关于你如何在 Python 中转义 SQLite 表/列名称的字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！