1. 首页
  2. 问题
  3. 如何在Python中转义SQLite表/列名称的字符串？
小编典典

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

python

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


阅读 221

收藏
2021-01-20

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

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

  • 'strict'：在编码错误的情况下引发异常
  • 'replace'：用适当的替换标记替换格式错误的数据，例如'?'或'\ufffd'
  • 'ignore'：忽略格式错误的数据，并继续进行，恕不另行通知
  • 'xmlcharrefreplace'：替换为适当的XML字符参考（仅用于编码）
  • 'backslashreplace'：替换为反斜杠转义序列（仅用于编码）

这不会检查保留的标识符，因此，如果您尝试创建一个新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标识符使用双引号引起来。

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

  • SQLite标识符保留大小写，但对ASCII字母不区分大小写。可以启用可识别unicode的大小写。

  • SQLite不支持字符串或标识符中的NUL字符。

  • sqlite3只要可以将其正确编码为UTF-8，就可以处理任何其他unicode字符串。无效的字符串可能会导致Python 3.0和Python 3.1.2或其附近崩溃。Python 2接受了这些无效的字符串，但这被认为是一个错误。

2021-01-20