Python mysql cursor.execute() have issues?

Question

I have some trouble getting data from my MySQL db using Python3.4.

This is my code :

connection = pymysql.connect(host='localhost',
                             user='root',
                             password=MY_PASSWD,
                             db=MY_DB,
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    # it doens't works works
    table_name = 'id_pw'
    key_name = 'url'
    key_value = 'naver.com'
    sql = "SELECT * FROM %s WHERE %s=\'%s\'"
    cursor.execute(sql, (table_name, key_name, key_value))

    # it works.
    sql = "SELECT * FROM id_pw WHERE url=\'naver.com\'"
    cursor.execute(sql)

Could you tell me what is wrong? I have no idea what I missed.

score 2 · Accepted Answer · answered Feb 24 '16 at 04:04

You cannot parameterize table or column names, use string formatting for that and query parameters for everything else:

sql = "SELECT * FROM {table} WHERE {column} = %s".format(table=table_name, column=key_name)
cursor.execute(sql, (key_value, ))

Note that you also don't need the quotes around the placeholder - the database driver would handle that automagically.

And, as another note, make sure you either trust where the table_name and key_name are coming from, or validate/escape them. Make sure your code is not vulnerable to SQL injections because of the string formatting we are introducing.

Python mysql cursor.execute() have issues?

1 Answers1