How would someone SQL inject this?

Question

I have been told the following is insecure:

cursor.execute("""SELECT currency FROM exchange_rates WHERE date='%s'"""%(self.date))

Why exactly is the '%s' bad? How would someone actually do a SQL injection here?

Answer 1

Imagine if self.date is "'; DROP TABLE exchange_rates --". Then you'll execute:

SELECT currency FROM exchange_rates WHERE date=''; DROP TABLE exchange_rates -- '

and boom, you're hosed. You have to escape the ' so the value of self.date will be completely contained in the string, not executed as a query.

Answer 2

The problem is that you're using string formatting when you should be passing values separately from the query.

For instance, compare:

cursor.execute("SELECT currency FROM exchange_rates WHERE date=?", self.date)

With the string formatting method, someone can put a ; into the value (edit: specifically, close the quote with a ' and then add the semicolon) and then try to inject an additional query after that, and it will be executed just like that. By passing the value separately, you ensure that data is treated only as data and is not executed as a query.

An added benefit in this case is that if self.date is a python date or datetime object, that will be automatically formatted in the appropriate fashion for your database when it is sent. If you try to add self.date to the query string directly, you'll have to use date formatting to ensure it's output exactly as the database expects it to be.