CODEWITHSUNDEEP
pythonmysqlmysql-python
user3422637
user3422637

Reputation: 4239

Python MySQL parameter queries for dynamic table names

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).

I tried something like this:

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

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?

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

Full code for reference:

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

Upvotes: 2

Views: 2495

Answers (1)

mgilson
mgilson

Reputation: 310069

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!')

Upvotes: 4

Related Questions