Python MySQL参数查询动态表名称 [英] Python MySQL parameter queries for dynamic table names

问题描述

我想使用我的python程序查询MySQL表.但是，我要查询的表是其名称作为变量(参数)传递的表.

I want to query MySQL tables using my python program. However, the tables that I want to query are the ones whose names are passed in as variables (parameters).

我尝试过这样的事情:

readTable_query = """SELECT * FROM %s"""
readTable_cursor.execute(readTable_query, (table_name))

此问题是动态生成的SQL查询将表名放在引号中，即，不是生成诸如"SELECT * FROM products"之类的查询，而是生成诸如"SELECT * FROM'products'"之类的查询.这导致我的程序失败并引发错误.有解决方法吗?

The problem with this is that the dynamically generated SQL query is putting the table name in quotes, i.e. instead of generating a query like "SELECT * FROM products", its generating a query like "SELECT * FROM 'products'". This is causing my program to fail and throws errors. Is there a workaround?

我知道我可以连接查询，但这会导致SQL注入的安全风险.

I know I could concatenate the query but that would lead to a security risk of SQL injection.

完整代码供参考:

import MySQLdb

table_name = 'products'

db_connection = MySQLdb.connect(host,user,passwd,db)
readTable_cursor = db_connection.cursor() 
readTable_query = """SELECT * FROM %s"""
readTable_cursor.execute(readTable_query, (table_name))

推荐答案

根据

According to http://dev.mysql.com/doc/refman/5.0/en/identifiers.html , valid table names consist of the following characters. [0-9a-zA-Z_$] So, it shouldn't be hard to write your own validator:

import re
table_name_validator = re.compile(r'^[0-9a-zA-Z_\$]+$')
if not table_name_validator.match(table_name):
    raise ValueError('Hey!  No SQL injecting allowed!')

这篇关于Python MySQL参数查询动态表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！