Prevent MySQL-Python from inserting quotes around the database name parameter

Question

I am working on a project that requires me to programmatically create MySQL users from a django application. I can create users just fine:

from django.db import connection, transaction cursor = connection.cursor() cursor.execute("CREATE USER % s@ '%'", 'username') cursor.execute("SET PASSWORD FOR % s@ '%' = PASSWORD(%s)", ('username', 'pass')) 

This works great. The problem is that I am trying to grant permissions. The database name is also determined programmatically:

 cursor.execute("GRANT SELECT ON %s.* TO % s@ '%'", ('dbname', 'username')) 

This results in a mysql error, because when it performs string expansion, it places single quotes around the database name, which is syntactically incorrect:

DatabaseError: (1064, "You have an error in the SQL syntax, check the manual that matches the version of your MySQL server for the correct syntax to use next to '' dbname '. * To' username '@'% '' on line 1")

How to prevent the addition of single quotes around the %s name for the database name? I know that I can just do the string substitution in Python and fix it, but this could potentially cause SQL injection vulnerability.

Answer 1

Sometimes placeholders won't work (as you found out), so you'll have to use string concatenation. Be careful - check the string, make sure that it consists only of the characters you expect (don't just look for characters you don't expect), and you should be fine. Also ask another developer to check your code and comment on it to make sure no one thinks you should use placeholders.