Python mysql.connector.errors. ％s传递给带引号的SQL查询 [英] Python mysql.connector.errors. %s passed to SQL query with quotes

问题描述

我在Python中执行以下代码

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

我收到此错误:

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

为什么将%s替换为引号? SQL查询将找到基本的信息架构" 而不是信息架构(不带引号).

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

推荐答案

MySQLPython对查询中的变量占位符使用标准字符串格式标记(％")的事实会使事情变得混乱.

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

python db-api中的查询占位符用于where子句以及insert和update语句中使用的值，并且已被db-api正确地修饰/转义/引用以避免SQL注入等.不应将它们用于表名或字段名.

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)

由于db_name[0]来自受信任的来源，所以这里没有安全问题.

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

这篇关于Python mysql.connector.errors. ％s传递给带引号的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！