4

I execute the following code in Python

cursor.execute('SHOW DATABASES;')
ans_dblist = cursor.fetchall()


for db_name in ans_dblist:
  cursor.execute('SHOW TABLES FROM %s;', (db_name[0],))
  ans_tbl = cursor.fetchall()
  print ans_tbl

And I get this error:

Traceback (most recent call last):
  File "./mysqlcon.py", line 12, in <module>
    cursor.execute('SHOW TABLES FROM %s;', (db_name[0],))
  File "/usr/lib/python2.6/site-packages/mysql/connector/cursor.py", line 507, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 722, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 640, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''information_schema'' at line 1

Why %s is replaced with quotes? SQL query will find base 'information schema' instead information schema (without quotes).

2 Answers2

6

The fact that MySQLPython uses the standard string format marker ("%") for variables placeholders in queries can make things confusing.

The queries placeholder in python's db-api are for values used in where clauses and insert and update statements, and are properly santized / escaped / quoted by the db-api to avoid SQL injections etc. They are not supposed to be used for table or field names.

So, what you want here is to build your query using string formatting:

sql =  'SHOW TABLES FROM %s;' % (db_name[0],)
cursor.execute(sql)

Since db_name[0] comes from a trusted source, there's no security issue here.

bruno desthuilliers
  • 75,974
  • 6
  • 88
  • 118
1

I use method format:

cursor.execute("SHOW TABLES FROM {0}".format(db_name[0]))

And it worked! :)