Python MySQLdb 变量作为表名 [英] Python MySQLdb variables as table names

问题描述

我的 python 中有一个语法错误，它阻止 MySQLdb 插入到我的数据库中.SQL 插入如下.

I have a syntax error in my python which which stops MySQLdb from inserting into my database. The SQL insert is below.

cursor.execute("INSERT INTO %s (description, url) VALUES (%s, %s);", (table_name.encode("utf-8"), key.encode("utf-8"), data[key].encode("utf-8"))) 

我在堆栈跟踪中收到以下错误.

I get the following error in my stack trace.

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your 
SQL syntax; check the manual that corresponds to your MariaDB server 
version for the right syntax to use near ''four' (description, url) VALUES ('', 'http://imgur.com/a/V8sdH')' at line 1")

我非常感谢您的帮助，因为我无法解决这个问题.

I would really appreciate assistance as I cannot figure this out.

使用以下行修复它:

cursor.execute("INSERT INTO " + table_name + " (description, url) VALUES (%s, %s);", (key.encode("utf-8"), data[key].encode("utf-8")))

不是最复杂的，但我希望用它作为一个起点.

Not the most sophisticated, but I hope to use it as a jumping off point.

推荐答案

看起来这是你的 SQL 语句:

It looks like this is your SQL statement:

cursor.execute("INSERT INTO %s (description, url) VALUES (%s, %s);", (table_name.encode("utf-8"), key.encode("utf-8"), data[key].encode("utf-8")))

IIRC，表的名称不能参数化(因为它被错误引用).您需要以其他方式将其注入字符串(最好是安全的 - 通过检查请求的表名是否与列入白名单的一组表名匹配)...例如:

IIRC, the name of the table is not able to be parameterized (because it gets quoted improperly). You'll need to inject that into the string some other way (preferably safely -- by checking that the table name requested matches a whitelisted set of table names)... e.g.:

_TABLE_NAME_WHITELIST = frozenset(['four'])

...
if table_name not in _TABLE_NAME_WHITELIST:
    raise Exception('Probably better to define a specific exception for this...')

cursor.execute("INSERT INTO {table_name} (description, url) VALUES (%s, %s);".format(table_name=table_name),
    (table_name.encode("utf-8"),
     key.encode("utf-8"),
     data[key].encode("utf-8")))

这篇关于Python MySQLdb 变量作为表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！