74

What is the safest way to run queries on MySQL? I am aware of the dangers involved with MySQL and SQL injection.

However, I do not know how I should run my queries to prevent injection on the variables to which other users (webclients) can manipulate. I used to write my own escape function, but apparently this is "not-done".

What should I use and how should I use it to query and do inserts safely on a MySQL database through python without risking MySQL injection?

Alex Waygood
  • 6,304
  • 3
  • 24
  • 46
Lucas Kauffman
  • 6,789
  • 15
  • 60
  • 86

2 Answers2

103

To avoid injections, use execute with %s in place of each variable, then pass the value via a list or tuple as the second parameter of execute. Here is an example from the documentation:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

Note that this is using a comma, not % (which would be a direct string substitution, not escaped). Don't do this:

c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""" % (max_price,))

In addition, you must not use single quotes around the position holder ('%s') if the parameter is a string as the driver provides these.

coyot
  • 418
  • 1
  • 3
  • 12
Bruno
  • 119,590
  • 31
  • 270
  • 376
  • 4
    Use `%s` too (see the `max_price` example above). – Bruno Oct 28 '11 at 13:22
  • 2
    why is there a comma behind max_price ? What does this mean ? Sorry if my questions seem noobish, but I'm quite new to python :) – Lucas Kauffman Oct 30 '11 at 00:31
  • 4
    The coma after `max_price` is the notation for a 1-element tuple: http://docs.python.org/tutorial/datastructures.html#tuples-and-sequences – Bruno Oct 30 '11 at 10:34
  • You said `Note that this is using a comma, not % (which would be a direct string substitution, not escaped). Don't do this` Are you sure? Because I used comma and it escapes string – Hussain May 23 '14 at 13:40
  • 1
    @HussainTamboli, yes, that's exactly what I said: comma is the correct way of using the parameter placeholders (it does all the escaping necessary), `%` doesn't escape the parameters. – Bruno May 23 '14 at 14:21
  • @Bruno Oh! I get it. So I am doing it the right way. Pardon me by the way. I misunderstood it. – Hussain May 23 '14 at 14:39
  • Python converts values in tuple to a data type that MySQL understands and adds the required quotes. – ns15 Apr 03 '17 at 07:29
  • And when it is a table name? it seems to me that `%s` introduces single quotes like `'breakfast'` and not like `\`breakfast\`` – lucidbrot Aug 24 '19 at 21:22
  • 1
    @lucidbrot Table names are not parameters. If you need to build a query dynamically with table names coming from variables, you need to sanitize those variables manually before placing them in the query string (not via parameter placeholders). For example, only allow table names that match `[a-z0-9_]+`. – Bruno Aug 25 '19 at 08:58
  • Yes, it works. But, why does it protect from SQL injection? – RicHincapie Aug 08 '20 at 20:59
  • 1
    @RicarHincapie It protects against SQL injections because it treats `%s` as a parameter that has nothing to do with string substitution in the query itself: either the driver will escape values properly, or the DB protocol itself will take that into consideration. – Bruno Aug 09 '20 at 14:57
  • 1
    Just to clarify, "you don't need single quotes" really should be "you must not use single quotes". I was converting a query and left the quotes in and got an SQL ProgrammingError ("You have an error in your SQL syntax..."). Removing the quotes caused it to work fine. – coyot Oct 19 '21 at 17:08
  • @coyot You're right! If you want a few SO points, feel free to edit, I'll approve it. – Bruno Oct 20 '21 at 11:00
88

As an expansion of Bruno's answer, your MySQL client library may support any of several different formats for specifying named parameters. From PEP 249 (DB-API), you could write your queries like:

'qmark'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = ?", (lumberjack,))

'numeric'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = :1", (lumberjack,))

'named'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = :jack", {'jack': lumberjack})

'format'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = %s", (lumberjack,))

'pyformat'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = %(jack)s", {'jack': lumberjack})

You can see which your client library supports by looking at the paramstyle module-level variable:

>>> clientlibrary.paramstyle
'pyformat'

Any of the above options should Do The Right Thing with regards to handling your possibly insecure data. As Bruno pointed out, please don't ever try to insert parameters yourself. The commonly-used client libraries are much better at processing data correctly than we mere mortals will ever be.

Kirk Strauser
  • 30,189
  • 5
  • 49
  • 65
  • 3
    Which client libraries support 'named'? PyMySQL and MySQLdb support 'format' and oursql supports 'qmark'. – Martin Burch Apr 10 '15 at 03:11
  • sqlite3 at least supports 'named'. I don't have any MySQL adapters installed to check for 'named' support in them. – Kirk Strauser Apr 10 '15 at 18:41
  • 1
    I know this is an old question, but I am trying to code my webpage properly and don't have much experience in secure SQL. Is using this method described above adequate in preventing SQL Injection, or are there other things I need to do in addition to this? Thanks. – jonesy19 May 22 '18 at 18:58
  • That's just about good enough on its own. I'd highly, highly recommend checking out an ORM like SQLAlchemy and letting it handle the details for you, though. – Kirk Strauser May 23 '18 at 20:31
  • MySQL client library doesn't support all these formattings – JCH77 Apr 06 '22 at 19:26