How would someone SQL introduce this?

Question

I was told that the following is not safe:

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

Why is '%s' bad? How does someone actually do SQL injection here?

Answer 1

Imagine if self.date "'; DROP TABLE exchange_rates --" . Then you do:

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

and boom, you want. You need to avoid ' , so the value of self.date will be entirely contained in the string, and not executed as a query.

Answer 2

The problem is that you use string formatting when you need to pass values ​​separately from the query.

For example, compare:

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

Using a string formatting method, someone can insert a value ; (edit: in particular, close the quote with ' , and then add a semicolon), and then try adding an additional query after that and it will be executed that way. By passing the value separately, you guarantee that the data is processed only as data and not executed as a request.

An additional advantage in this case is that if self.date is a python or datetime date, it will be automatically formatted accordingly for your database when it is submitted. If you try to add self.date to the query string directly, you will need to use date formatting to ensure that it will be displayed exactly as expected in the database.