在 psycopg2 中将表名作为参数传递-IT科技

摘要：问题描述：我有以下代码，使用 pscyopg2：sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;' data = (dataItems, voyage, dateRangeLower...

问题描述：

我有以下代码，使用 pscyopg2：

sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;'
data = (dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql, data)

输出如下：

select 'waterTemp, airTemp, utctime' from 'ss2012_t02' where utctime > '2012-05-03T17:01:35+00:00'::timestamptz and utctime < '2012-05-01T17:01:35+00:00'::timestamptz order by utctime asc;

当我执行这个时，它会失败 - 这是可以理解的，因为表名周围的引号是非法的。

有没有办法合法地将表名作为参数传递，或者我是否需要执行（明确警告）字符串连接，即：

voyage = 'ss2012_t02'
sql = 'select %s from ' + voyage + ' where utctime > %s and utctime < %s order by utctime asc;'

欢迎提出任何见解。

解决方案 1：

根据官方文档：

如果您需要动态生成 SQL 查询（例如动态选择表名），您可以使用 psycopg2.sql 模块提供的功能。

该sql模块是 psycopg2 版本 2.7 中的新模块。它具有以下语法：

from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {table} values (%s, %s)")
        .format(table=sql.Identifier('my_table')),
    [10, 20])

[更新2017-03-24：AsIs不应用于表示表或字段名称，sql而应使用新模块：https://stackoverflow.com/a/42980069/5285608]

此外，根据 psycopg2 文档：

警告：永远、永远、永远不要使用 Python 字符串连接 ( +) 或字符串参数插值 ( %) 将变量传递给 SQL 查询字符串。即使在枪口下也不行。

解决方案 2：

根据这个答案，你可以这样做：

import psycopg2
from psycopg2.extensions import AsIs

#Create your connection and cursor...

cursor.execute("SELECT * FROM %(table)s", {"table": AsIs("my_awesome_table")})

解决方案 3：

表名不能作为参数传递，但其他所有东西都可以。因此，表名应该在您的应用程序中进行硬编码（不要接受输入或使用程序之外的任何内容作为名称）。您拥有的代码应该可以做到这一点。

如果您有正当理由使用外部表名，请确保不允许用户直接输入。也许可以传递索引来选择表，或者可以通过其他方式查找表名。但是，您谨慎行事是正确的。这很有效，因为周围的表名相对较少。找到一种验证表名的方法，您应该没问题。

可以做类似这样的事情，看看表名是否存在。这是一个参数化版本。只需确保执行此操作并在运行 SQL 代码之前验证输出即可。这个想法的一部分来自这个答案。

SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' and table_name=%s LIMIT 1

解决方案 4：

这是我过去使用过的一种解决方法

query = "INSERT INTO %s (col_1, col_2) VALUES (%%s, %%s)" % table_name
cur.execute(query, (col_1_var, col_2_var))

希望有帮助:)

解决方案 5：

这是对@Antoine Dusséaux 答案的一个小补充。如果您想在 SQL 查询中传递两个（不带引号的）参数，您可以按如下方式执行：-

query = sql.SQL("select {field} from {table} where {pkey} = %s").format(
    field=sql.Identifier('my_name'),
    table=sql.Identifier('some_table'),
    pkey=sql.Identifier('id'))

根据文档，

通常，您应该将查询模板表示为具有 {} 样式占位符的 SQL 实例，并使用 format() 将变量部分合并到其中，所有这些部分都必须是 Composable 子类。您仍然可以在查询中使用 %s 样式占位符并将值传递给 execute()：此类值占位符将不受 format() 的影响

来源：https ://www.psycopg.org/docs/sql.html#module-usage

此外，在编写查询时请记住这一点。

解决方案 6：

我创建了一个小实用程序，用于对带有变量表（...）名称的 SQL 语句进行预处理：

from string import letters
NAMECHARS = frozenset(set(letters).union('.'))

def replace_names(sql, **kwargs):
    """
    Preprocess an SQL statement: securely replace table ... names
    before handing the result over to the database adapter,
    which will take care of the values.

    There will be no quoting of names, because this would make them
    case sensitive; instead it is ensured that no dangerous chars
    are contained.

    >>> replace_names('SELECT * FROM %(table)s WHERE val=%(val)s;',
    ...               table='fozzie')
    'SELECT * FROM fozzie WHERE val=%(val)s;'
    """
    for v in kwargs.values():
        check_name(v)
    dic = SmartDict(kwargs)
    return sql % dic

def check_name(tablename):
    """
    Check the given name for being syntactically valid,
    and usable without quoting
    """
    if not isinstance(tablename, basestring):
        raise TypeError('%r is not a string' % (tablename,))
    invalid = set(tablename).difference(NAMECHARS)
    if invalid:
        raise ValueError('Invalid chars: %s' % (tuple(invalid),))
    for s in tablename.split('.'):
        if not s:
            raise ValueError('Empty segment in %r' % tablename)

class SmartDict(dict):
    def __getitem__(self, key):
        try:
            return dict.__getitem__(self, key)
        except KeyError:
            check_name(key)
            return key.join(('%(', ')s'))

SmartDict 对象返回%(key)s每个未知数key，并将它们保存起来以供值处理。该函数可以检查是否存在任何引号字符，因为现在应该处理所有引号...

解决方案 7：

如果您想将表名作为参数传递，您可以使用这个包装器：

class Literal(str):
    def __conform__(self, quote):
        return self

    @classmethod
    def mro(cls):
        return (object, )

    def getquoted(self):
        return str(self)

用法：cursor.execute("CREATE TABLE %s ...", (Literal(name), ))

解决方案 8：

您可以只使用模块格式作为表名，然后使用常规参数化进行执行：

xlist = (column, table)
sql = 'select {0} from {1} where utctime > %s and utctime < %s order by utctime asc;'.format(xlist)

请记住，如果这暴露给最终用户，除非您为其编写代码，否则您将无法受到 SQL 注入的保护。

解决方案 9：

如果需要传递合格的标识符，例如架构名称+表名：

from psycopg2 import sql

cur.execute(
    sql.SQL(
        "INSERT INTO {table} VALUES (%s, %s)"
    ).format(
        table=sql.Identifier("my_schema", "my_table")
    ),
    [10, 20]
)

# INSERT INTO "my_schema"."my_table" VALUES (10, 20)

请参阅： https: //www.psycopg.org/docs/sql.html#psycopg2.sql.Identifier

解决方案 10：

令人惊讶的是没有人提到这样做：

sql = 'select {} from {} where utctime > {} and utctime < {} order by utctime asc;'.format(dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql)

格式输入的字符串不带引号。