在execute函数中使用psycopg2查询参数是最安全的,并且当参数用作文本时也很容易使用。在cursor.mogrify("select * from foo where bar = %s", ('example',))
# yields "select * from foo where bar = 'example'"
(注意cursor.mogrify()的行为类似于execute,但只显示格式化的SQL而不实际执行它)
但是,当您希望参数是表、模式或其他标识符时,要做到这一点有点困难。您可以使用AsIs包装参数,但这仍然为SQL注入敞开大门。在
^{pr2}$
看起来psycopg2的新开发(>;=2.7)将有一个标识符类,您可以在其中包装参数,希望是安全的。如果它还没有发布,或者你还没有,这里有一种方法来创建你自己的类。我将在下面给出一些片段,但您也可以看到my gist。在import re
import psycopg2.extensions
class NotSqlIdentifierError(Exception):
pass
valid_pattern = r'^[a-zA-Z_][a-zA-Z0-9_\$]*$'
class QuotedIdentifier(object):
def __init__(self, obj_str):
self.obj_str = obj_str
def getquoted(self):
if re.match(valid_pattern, self.obj_str):
return self.obj_str
else:
raise NotSqlIdentifierError(repr(self.obj_str))
psycopg2.extensions.register_adapter(QuotedIdentifier, lambda x: x)
如果您已经有一个psycopg2游标实例,可以通过以下方式测试/使用它:# Test that a valid identifier formats into string
cursor.mogrify('select %s from foo;', (QuotedIdentifier('bar'),))
# returns 'select bar from foo;'
# Test formatting both an identifier and a literal
cursor.mogrify(
'select * from foo where %s = %s;',
(
QuotedIdentifier('bar'),
'example'
)
)
# returns "select * from foo where bar = 'example';"
# Test that a non-valid identifier fails with exception
cursor.mogrify('select %s from foo;', (QuotedIdentifier('* from dummy; drop table students; '),))
"""Returns following:
-
NotSqlIdentifierError Traceback (most recent call last)
in ()
> 1 cur.mogrify('select %s from foo;', (QuotedIdentifier('* from dummy; drop table students; '),))
in getquoted(self)
18 return self.obj_str
19 else:
-> 20 raise NotSqlIdentifierError(repr(self.obj_str))
21
22 psycopg2.extensions.register_adapter(QuotedIdentifier, lambda x: x)
NotSqlIdentifierError: '* from dummy; drop table students; '
"""
有关自定义类包装SQL参数的机制的更多信息,请参阅文档中的this section。在