CODEWITHSUNDEEP
pythonmysqlsql-injectionpymysql
dsw88
dsw88

Reputation: 4592

PyMySQL - Escaping identifiers

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!

Upvotes: 3

Views: 2769

Answers (2)

Some dude
Some dude

Reputation: 597

Extending the answer from @Bill Karwin, you will need to escape backticks yourself, because pymysql doesn't do it natively, not even its escape_string() method.

However, contrary to what Bill said, in order to escape possible backticks in the table name, you will need to double them, and not use a \. From mysql's documentation:

Identifier quote characters can be included within an identifier if you quote the identifier. If the character to be included within the identifier is the same as that used to quote the identifier itself, then you need to double the character.

Which basically means that in order to escape a backtick you need a double backtick. This is what I've resorted to in the end:

table = 'table`; drop table users; -- '
sane_table = pymysql.escape_string(table).strip('`').replace('`', '``')
cursor.execute(f"SELECT * FROM `{sane_table}`"))

Which will correctly escape the table name and report the proper error:

pymysql.err.ProgrammingError: (1103, "Incorrect table name 'table`; drop table users; -- '")

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562330

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.

Upvotes: 3

Related Questions