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.

Looks like it may not be possible. According to the mysql-python docs: Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc. — dgel
– dgel, Commented Apr 9, 2012 at 17:26

Rory Hunter · Accepted Answer · 2012-04-09 17:30:40Z

6

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.

answered Apr 9, 2012 at 17:30

Rory Hunter

3,4901 gold badge16 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Prevent MySQL-Python from inserting quotes around database name parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related