Prevent MySQL-Python from inserting quotes around database name parameter

Question

I'm working on a project that requires me to programmatically create MySQL users from a django app. I can create the 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'))

That works perfectly. The problem is when I try 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 does the string substitution, it places single quotes around the database name, which is syntactically incorrect:

DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''dbname'.* to 'username'@'%'' at line 1")

How do I prevent the single quotes from being added around the %s for database name? I know that I could simply do the string substitution in Python and fix this, but that could potentially cause a SQL injection vulnerability.

Answer 1

Sometimes placeholders won't work (as you've found out), so you'll have to use string concatenation. Be careful - validate the string, make sure it's only composed of the characters you expect (don't just look for characters you don't expect), and you should be OK. Also get another developer to check your code, and comment it to make sure no-one else thinks you ought to be using placeholders.