首页 / 问题 / 正文

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

57 投票
8 回答
24025 浏览
提问于 2025-04-16 20:29

在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使用"来引用标识符，但我不确定仅仅转义它们是否足够。PHP的sqlite_escape_string函数的文档建议某些二进制数据也可能需要转义，但这可能只是PHP库的一个特性。

数据库 查询 语法 函数 sqlite 变量 标识符 转义

8 个回答

17

如果你非常确定需要动态指定列名，那就应该使用一个可以安全处理这个问题的库（并且会对错误的地方发出警告）。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会记录生成的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中，一些奇怪的东西也是合法的标识符）

回答于 2025-04-16 由 Python大师
分享 举报
31

psycopg2的文档明确建议使用普通的Python格式化方法（比如%或{}）来替换表名和列名（或者其他动态语法），然后再用参数机制将值插入到查询中。

我不同意那些说“永远不要使用动态表名/列名，如果需要这样做就是错了”的观点。我每天都在写程序来自动化数据库的操作，我经常这样做。我们有很多数据库和很多表，但它们都是基于重复的模式构建的，所以处理这些的通用代码是非常有用的。每次手动写查询会更容易出错，也更危险。

这归结于“安全”的定义。传统观点认为，使用普通的Python字符串操作将值放入查询中是不“安全”的。这是因为如果这样做，可能会出现各种问题，而这些数据通常来自用户，无法控制。你需要一种100%可靠的方法来正确处理这些值，以防用户在数据中注入SQL代码并让数据库执行它。因此，库的开发者会处理这个问题；你不应该这样做。

然而，如果你在编写通用的辅助代码来操作数据库中的内容，那么这些考虑就不那么适用了。你隐含地给了任何可以调用这些代码的人访问数据库的权限；这就是辅助代码的目的。所以现在的安全问题是确保用户生成的数据永远不能在这样的代码中使用。这是编程中的一个普遍安全问题，和盲目地exec用户输入的字符串是同一个问题。这与将值插入查询是不同的，因为在那种情况下，你希望能够安全地处理用户输入的数据。

所以我的建议是：随意动态组装你的查询。使用普通的Python字符串模板来替换表名和列名，添加条件和连接等所有好（但调试起来很麻烦）的东西。但要确保你知道，任何这样的代码所处理的值必须来自你，而不是你的用户[1]。然后你可以使用SQLite的参数替换功能来安全地将用户输入的值插入到查询中。

[1] 如果（就像我写的很多代码一样）你的用户本身就是拥有数据库完全访问权限的人，而代码是为了简化他们的工作，那么这个考虑就不太适用了；你可能确实是在组装用户指定的表的查询。但你仍然应该使用SQLite的参数替换，以避免最终出现包含引号或百分号的真实值。

回答于 2025-04-16 由 Python大师
分享 举报
34

要把任何字符串转换成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_MASTER 模式在常见问题中
    • 当我给Python 2的SQLite API传递它无法解码为文本的字节时，它会报错。
    • Python 3的SQLite API要求查询必须是 str 类型，而不是 bytes。
  • 在SQLite标识符中的双引号会被转义为两个双引号。
  • SQLite的标识符保留大小写，但对ASCII字母不区分大小写。可以启用对Unicode的大小写不敏感。
  • sqlite3可以处理任何其他的Unicode字符串，只要它能够正确编码为UTF-8。无效的字符串可能会导致Python 3.0和3.1.2之间的崩溃。Python 2接受这些无效字符串，但这被认为是一个bug。
回答于 2025-04-16 由 Python大师
分享 举报

撰写回答