CODEWITHSUNDEEP
pythonmysqldictionary
aditya_medhe
aditya_medhe

Reputation: 372

Using dictionary keys as field names in SQL query

I am writing a function to modify the table. It receives a dictionary, with keys as field names to modify and the corresponding value as the new value.

for key, val in kwargs.keys():
    cursor.execute("UPDATE users SET %s = %s WHERE user_id = %s", [key, val, user_id])

This approach seemed fine, but it gives a syntax error on MySQL, and that's because the field first_name is inserted with quotes in the query. So the query is like UPDATE users SET 'first_name' = ...

I tried all approaches to remove the quotes from the query, but nothing seems to work.

Any ideas on how to solve this, and the reasons behind this?

Upvotes: 0

Views: 2144

Answers (2)

Shankar
Shankar

Reputation: 866

Use Python string formatting, and then pass user given values which will be handled by you DB library. This is not prone to SQL Injection unless column name defined in your code. If you are getting column name from user inputted values we are still prone to SQL Injection

I made one more assumption in the sample code, it seems you are updating table multiple times for given key / value pair, so I build a single query to do the same.

columns = kwargs.keys()
update_list = [ "%(key)s = %%(%(key)s)s" % {'key' : key} for key in columns]
kwargs.update(user_id = user_id)
cursor.execute("UPDATE users SET %(update_list)s WHERE user_id = %%(user_id)s" % { 'update_list' : ",\n".join(update_list)}, kwargs)

Upvotes: 1

piglei
piglei

Reputation: 1208

Do the string formation twice, you can still have the benefits of DB driver parameter handling:

for key, val in kwargs.keys():
    cursor.execute("UPDATE users SET %s = %%s WHERE user_id = %%s" % key, [val, user_id])

Upvotes: 0

Related Questions