Overview
Python has a lot of great ways to format strings dynamically. Python also has some great ways to prevent SQL injections. This guide covers string formatting using both the % operator and f-strings. It also covers parameterized queries (Also known as prepared statements) and how to safely use them when interfacing with SQL queries to prevent SQL Injection attacks.
For the string formatting overview, we'll use an example of a store logging information on customer purchases.
String formatting with % Operator
The % operator is considered an "older" method to format strings when printing information. Here is an example:
customer = "Noah"
item = "coffee"
quantity = 1
price = 3.455
print_statement = "%s purchased %i %s for $%.2f."
print(print_statement % (customer, quantity, item, price))
This will print:
Noah purchased 1 coffee for $3.46.
First, the print_statement is prepared with various placeholders referencing their expected datatypes ( %i for an integer, %s for a string, and %f for a float). Then that statement can be printed when provided with the corresponding variables in order. This is relatively straightforward, but there's a better way to print dynamic data.
Python f-strings
Python 3.6 introduced f-strings. F-strings are another great way to handle logs and print statements,
Let's build on the last example:
customer = "Noah"
item = "coffee"
quantity = 2
price = 3.455
print(f"{customer} purchased {quantity} {item}s for ${quantity * price}.")
This will print:
Noah purchased 2 coffees for $6.91.
These f-strings are easier to implement and read, and they can interpret embedded Python expressions. While this is great for printing and logging information, it does not guarantee security against SQL Injection attacks. This is because f-strings cannot simply be used to make prepared statements.
Parameterized Queries
A parameterized query (also known as a prepared statement with variable binding) is an SQL query that contains a placeholder instead of the actual values provided by the user. These values are then supplied at execution time. Let's look at a code snippet from the patched SQL Injection Lesson: Part 1
query = "SELECT * FROM users WHERE username = %s AND password= %s;"
cursor.execute(query, (username, password))
Here, we can see this query uses a prepared statement. The query can be made using these string placeholders with the % operator. When the cursor.execute() function is given this string and the tuple that contains the username and password, then it can fill those string placeholders safely.
If we try to do this with f-strings, it will not work. An f-string needs a value immediately. The following code snippet will give a syntax error.
query = "SELECT * FROM users WHERE username = {} AND password= {};"
cursor.execute(query, (username, password))
The other option we can try when using f-strings is to put this query directly into the cursor.execute() function. That would look like this:
cursor.execute(f"SELECT * FROM users WHERE username = {username} AND password= {password}")This does not result in a syntax error. Unfortunately, this is also not a parameterized statement. The user input can still be manipulated, and an SQL Injection attack can still happen. This is why we suggest avoiding f-strings and instead using parameterized queries with the % operator when using user-supplied data to make an SQL query.