Is there any safe way to parameterize database names in MySQL queries?

Question

I'm writing a little python script to help me automate the creation of mysql databases and associated accounts for my personal projects. Part of this script is a function that takes the database name as a string, then goes to create the database.

def createDB(dbConn, dbName):
    import MySQLdb
    c = dbConn.cursor()
    query = """CREATE DATABASE %s;""";
    c.execute(query, (dbName,))

This doesn't work because MySQL's CREATE DATABASE asks for the unquoted name of the database, as in

  CREATE DATAbASE test_db

but my code that attempts to safely insert the user provided db name into the query creates:

  CREATE DATABASE 'test_db'

And you get "you have a problem in your MySQL syntax near test".

Even though this is for personal use, I really don't want to just directly insert a user provided string into a query of any kind. Its against my religion. Is there a safe way to insert a user-provided database name into a mySQL query in python (or any language) that will make sure that user input such as test_db; DROP some_other_db; will get rejected or escaped correctly?

Try `conn.escape_string`, as per the second answer here: http://stackoverflow.com/questions/3617052/escape-string-python-for-mysql — Marc B, Jul 11 '11 at 21:15

SingleNegationElimination · Answer 1 · 2011-07-11T22:43:22.897

5

A database name (nor column or table names) are not data values, and thus are not an appropriate use of placeholders. wanting to do this is usually a bad sign; only the DBA should be able to issue a create database, since doing so requires some considerable privileges. Most applications require the DBA to issue the create database, and then take the created database as a parameter to be used in the arguments to dbapi.Connection.

If you are sure you need this, you trust the source of the input, and you have checked the input for invalid characters, you would just do the substitution in python, something like:

def createDB(dbConn, dbName):
    c = dbConn.cursor()
    query = """CREATE DATABASE %s;""" % dbName
    c.execute(query)

edited Jul 11 '11 at 22:43

answered Jul 11 '11 at 21:27

SingleNegationElimination

151,563
33
264
304

Sorry not a very satisfying answer. If only the db admin should do this, and it should only be done through a direct query without any tools, how does phpmyadmin and the dozen of other similar tools work? How can I enter a database name in a webform and have it run some query on the backend to create the db? Seems like they have the same problem and need to validate the user input and safely place it in a query. – Doug T. Jul 11 '11 at 22:36
phpmyadmin assumes it's the DBA that's talking to it. "admin" is right there in the name. They similarly implement their own logic to make sure the db name being passed to them is sane, and that the admin knows what they are doing. – SingleNegationElimination Jul 11 '11 at 22:42
My script assumes the same. My script is run by the admin of mysql running on my development box to make it more convenient for me, the dev, to start a new fun personal project. – Doug T. Jul 11 '11 at 22:51

score 2 · Accepted Answer · answered Jul 12 '11 at 14:19

After some digging it turns out that phpmyadmin uses backticks to quote database, table, and column names. They simply do:

$sql_query = 'CREATE DATABASE ' . PMA_backquote($new_db);

Which would give in the error case above something like

CREATE DATABASE `test_db; DROP some_other_db`;

Of course any backticks in the input string need to be escaped, which according to phpmyadmin's code is done by replacing all single back ticks with double back ticks. I can't find any where that confirms that this is correct.

I also noticed online though that backticks are not standard SQL.

Is there any safe way to parameterize database names in MySQL queries?

2 Answers2

Linked