如何在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允许的任何标识符都有效，所以试错解决方案对我来说太不确定了。

SQLiteuses ^{} to quote identifiers但是我不确定仅仅转义它们就足够了。PHP的^{}函数的文档表明，某些二进制数据可能也需要转义，但这可能是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 ^{} 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标识符是^{}，而不是二进制的。
  • ^{} schema in the FAQ
  • python2sqliteapi在我给它的字节数时对我大喊大叫，它不能作为文本解码。
  • Python 3 SQLite API要求查询是strs，而不是bytes。

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

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

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

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

• sqlite3可以处理任何其他unicode字符串，只要它可以正确编码为UTF-8。无效字符串可能会导致Python3.0和Python3.1.2之间或其附近的崩溃。Python 2接受这些无效字符串，但这被认为是一个bug。
  • Python Issue #12569
  • Modules/_sqlite/cursor.c
  • 我试了很多。

Answer 2

psycopg2文档明确建议使用普通python%或{}格式替换表名和列名（或其他动态语法位），然后使用参数机制将值替换到查询中。

我不同意每个人说的“永远不要使用动态表/列名，如果需要的话，你做的是错误的”。我每天都写程序来自动处理数据库的事情，而且我一直都在做。我们有很多数据库和很多表，但是它们都是基于重复的模式构建的，所以处理它们的通用代码非常有用。每次手工编写查询将更容易出错，也更危险。

归根结底，“安全”是什么意思。传统的看法是，使用普通的python字符串操作将值放入查询中不是“安全的”。这是因为如果你这样做，会有各种各样的事情出错，而这些数据通常来自用户，不在你的控制范围内。您需要一种100%可靠的方法来正确转义这些值，这样用户就不能在数据值中插入SQL并让数据库执行它。所以图书馆的作者做这项工作；你永远不应该做。

但是，如果您正在编写通用的帮助程序代码来操作数据库中的内容，那么这些注意事项就不那么适用了。您隐式地让任何可以调用此类代码的人访问数据库中的所有内容；这就是helper代码的要点。所以现在的安全问题是确保用户生成的数据永远不能在这样的代码中使用。这是编码中的一般安全问题，与盲目使用用户输入字符串的问题相同。这与在查询中插入值是一个不同的问题，因为在这里您希望能够安全地处理用户输入数据。

所以我的建议是：做任何你想动态组合查询的事情。使用普通的python字符串模板来在表和列名中进行sub，粘在where子句和连接上，所有好的（和可怕的调试）东西。但是，请确保您知道这些代码所涉及的任何值都必须来自您而不是您的用户。然后使用SQLite的参数替换功能将用户输入值作为值安全地插入到查询中。

[1]如果（就像我编写的许多代码一样）您的用户是的人，他们无论如何都可以完全访问数据库，并且代码是为了简化他们的工作，那么这种考虑并不真正适用；您可能是在对用户指定的表进行组合查询。但是您仍然应该使用SQLite的参数替换来避免不可避免的真正值，该值最终包含引号或百分号。

Answer 3

如果您非常确定需要动态指定列名，则应该使用能够安全地指定列名的库（并抱怨错误的地方）。SQLAlchemy在这方面非常擅长。

>>> import sqlalchemy
>>> from sqlalchemy import *
>>> metadata = MetaData()
>>> dynamic_column = "cow"
>>> foo_table = Table('foo', metadata,
...     Column(dynamic_column, Integer))
>>> 

foo_table现在表示带有动态模式的表，但是您只能在实际数据库连接的上下文中使用它（以便sqlalchemy知道方言，以及如何处理生成的sql）。

>>> metadata.bind = create_engine('sqlite:///:memory:', echo=True)

然后可以发出CREATE TABLE ...。使用echo=True，sqlalchemy将log生成的sql，但通常，sqlalchemy会特意让生成的sql不受您的控制（以免您考虑将其用于邪恶的目的）。

>>> foo_table.create()
2011-06-28 21:54:54,040 INFO sqlalchemy.engine.base.Engine.0x...2f4c 
CREATE TABLE foo (
    cow INTEGER
)
2011-06-28 21:54:54,040 INFO sqlalchemy.engine.base.Engine.0x...2f4c ()
2011-06-28 21:54:54,041 INFO sqlalchemy.engine.base.Engine.0x...2f4c COMMIT
>>> 

是的，sqlalchemy将处理任何需要特殊处理的列名，比如列名是sql保留字时

>>> dynamic_column = "order"
>>> metadata = MetaData()
>>> foo_table = Table('foo', metadata,
...     Column(dynamic_column, Integer))
>>> metadata.bind = create_engine('sqlite:///:memory:', echo=True)
>>> foo_table.create()
2011-06-28 22:00:56,267 INFO sqlalchemy.engine.base.Engine.0x...aa8c 
CREATE TABLE foo (
    "order" INTEGER
)
2011-06-28 22:00:56,267 INFO sqlalchemy.engine.base.Engine.0x...aa8c ()
2011-06-28 22:00:56,268 INFO sqlalchemy.engine.base.Engine.0x...aa8c COMMIT
>>> 

也能让你免于可能的恶行：

>>> dynamic_column = "); drop table users; -- the evil bobby tables!"
>>> metadata = MetaData()
>>> foo_table = Table('foo', metadata,
...     Column(dynamic_column, Integer))
>>> metadata.bind = create_engine('sqlite:///:memory:', echo=True)
>>> foo_table.create()
2011-06-28 22:04:22,051 INFO sqlalchemy.engine.base.Engine.0x...05ec 
CREATE TABLE foo (
    "); drop table users; -- the evil bobby tables!" INTEGER
)
2011-06-28 22:04:22,051 INFO sqlalchemy.engine.base.Engine.0x...05ec ()
2011-06-28 22:04:22,051 INFO sqlalchemy.engine.base.Engine.0x...05ec COMMIT
>>> 

（显然，有些奇怪的东西在sqlite中是完全合法的标识符）