PyMySQL - Escaping identifiers

Question

I'm using PyMySQL, and as I write queries I need to escape certain identifiers that are user-provided (table names, etc.). For example, here's a bad example showing what I mean:

sql = "SELECT * FROM {}".format(table_name)
cursor.execute(sql)

In this case table_name is user-provided, so the above code is clearly vulnerable to a SQL injection attack.

I first tried to do something like this:

sql = "SELECT * FROM %s"
cursor.execute(sql, (table_name,))

It seems that the above code does not work when escaping identifiers, it only works with values. This isn't really surprising because I've seen similar behavior in other MySQL libraries in other languages.

I've used other libraries, such as mysql in Node.js, that have a function for escaping identifiers. I can't see anything in the PyMySQL documentation that indicates similar functionality. I found this StackOverflow question asking a similar question, but the answer was something along the lines of "You shouldn't need to do that." Well I do need to do that! ;)

What are my options for escaping identifiers when using MySQL in Python? Thanks!

Answer 1

The python driver simulates query parameters, but it follows similar rules. A placeholder like %s will be replaced by a quoted string, with the string escaped so any literal apostrophe characters will have a \ inserted.

sql = "SELECT * FROM mytable WHERE name = %s"
cursor.execute(sql, (myname,))

Will result in the SQL:

SELECT * FROM mytable WHERE name = 'O\'Reilly'

The backslash is necessary to prevent SQL injection.

But it's not useful for table names, because the single-quotes are for string or date literals, not for identifiers.

You could do it this way:

sql = "SELECT * FROM `{}`".format(table_name)

The back-ticks delimit an identifier so you can use reserved words or whitespace or other special characters in your table name.

It'd be up to you to make sure the value of table_name doesn't include any literal back-tick characters, or else insert backslashes if you do have a literal back-tick in the table name.