在Python的psycopg2中指定要执行的参数时,如下所示:
When specifying a parameter to execute() in psycopg2 in Python, like this:
cursor.execute('SELECT * FROM %s', ("my_table", ))我收到此错误:
psycopg2.ProgrammingError: syntax error at or near "'my_table'" LINE 1: SELECT * FROM 'my_table'我在做什么错?看起来psycopg2在查询中添加了单引号,而这些单引号导致了语法错误。
What am I doing wrong? It looks like psycopg2 is adding single quotes to the query, and those single quotes are causing the syntax error.
如果我不使用参数,它将正常工作:
If I don't use a parameter, it works correctly:
cursor.execute('SELECT * FROM my_table')推荐答案
我相信这样的参数化语句应与 values 一起使用,而不是与表名一起使用(或SQL关键字等)。因此,您基本上对此感到不走运。
I believe that parametrized statements like this are meant to be used with values and not table names (or SQL keywords, etc.). So you're basically out of luck with this.
但是,请不要担心,因为该机制是为了防止SQL注入,而且您通常知道要使用哪个表可以在编写代码时访问,因此几乎没有机会注入恶意代码。
However, do not worry, as this mechanism is meant to prevent SQL injection, and you normally know what table you want to access at code-writing time, so there is little chance somebody may inject malicious code. Just go ahead and write the table in the string.
如果出于某些(可能是错误的)原因,将表名保持参数化,则如下:
If, for some (possibly perverse) reason you keep the table name parametric like that:
例如:
cursor.execute( 'SELECT * FROM %s where %s = %s' % ("my_table", "colum_name", "%s"), #1 ("'some;perverse'string;--drop table foobar")) #2#1 :此时将用另一个'%s'替换第三个%s,以允许psycopg2 #2 进行后续处理:这是正确的字符串由psycopg2引用并放置在原始strin中而不是第三个'%s' g
#1: Let the third %s be replaced with another '%s' at this time, to allow later processing by psycopg2 #2: This is the string that will be properly quoted by psycopg2 and placed instead of that third '%s' in the original string
更多推荐
具有SQL查询参数的psycopg2 cursor.execute()导致语法错误
发布评论