如何在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

如果您非常确定需要动态指定列名，则应该使用能够安全地指定列名的库（并抱怨错误的地方）。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中是完全合法的标识符）