mysql - Python MySQLdb variables as table names

Question

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.

EDIT:

Fixed it with the following line:

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.

Answer 1

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, 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")))