CODEWITHSUNDEEP
pythonmysqlsql-injection
Evert
Evert

Reputation: 99571

Is this python/mysql query susceptible to SQL injection

I'm currently reviewing someone's code, and I ran into the following Python line:

db.query('''SELECT foo FROM bar WHERE id = %r''' % id)

This goes against my common sense, because I would usually opt-in to use prepared statements, or at the very least use the database system's native string escaping function.

However, I am still curious how this could be exploited, given that:

  1. The 'id' value is a string or number that's provided by an end-user/pentester
  2. This is MySQL
  3. The connection is explicitly set to use UTF8.

Upvotes: 4

Views: 363

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562398

Python drivers for MySQL don't support real prepared statements. They all do some form of string-interpolation. The trick is to get Python to do the string-interpolation with proper escaping.

See a demonstration of doing it unsafely: How do PyMySQL prevent user from sql injection attack?

The conventional solution to simulate parameters is the following:

sql = "SELECT foo FROM bar WHERE id = %s"
cursor.execute(sql, (id,))

See https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

The only ways I know to overcome escaping (when it is done correctly) are:

  • Exploit GBK or SJIS or similar character sets, where an escaped quote becomes part of a multi-byte character. By ensuring to set names utf8, you should be safe from this issue.
  • Change the sql_mode to break the escaping, like enable NO_BACKSLASH_ESCAPES or ANSI_QUOTES. You should set sql_mode at the start of your session, similar to how you set names. This will ensure it isn't using a globally changed sql_mode that causes a problem.

See also Is "mysqli_real_escape_string" enough to avoid SQL injection or other SQL attacks?

Upvotes: 1

Related Questions