Preventing SQL injection when using cursors in Python

When writing statements and using cursor.execute in Python, it is important to be aware of potential security issues, especially when dealing with data from outside sources. This lesson will cover an example of SQL injection and show you how to prevent it when working with external data like user input or API responses.

SQL injection example

SQL injection is a security issue that occurs when an attacker inserts malicious SQL statements into a legitimately executed statement. Let's take a look at the example below:

guest_name = "Brittany"
email = "brittany@yahoo.com"


cursor.execute(f"INSERT INTO guests (guest_name, email) VALUES ({guest_name}, {email});")

In this case, we are using an f-string to insert the guest_name and email variables directly into the SQL statement. This may seem like a natural approach, but it is actually what you don't want to do. Let's take a look at why:

email = "brittany@yahoo.com'); DELETE FROM guests; SELECT('Dummy"

The email variable now contains additional SQL statements, which means that the database may interpret this as three separate statements:

1. Insert brittany@yahoo.com into the guests table.
2. Delete all rows from the guests table.
3. Run a SELECT statement.

The problem with this is that user input or API data could contain any malicious information, and if the database interprets it as multiple statements, it can cause serious security issues.

Preventing SQL injection with placeholders

To prevent SQL injection, we need to avoid using f-strings and other methods of directly embedding variables into SQL statements. Instead, we can use placeholders and pass a tuple or a list as a second argument to execute.

Here's a modified version of the example above, using placeholders:

guest_name = "Brittany"
email = "brittany@yahoo.com"


cursor.execute("INSERT INTO guests (guest_name, email) VALUES (%s, %s);", (guest_name, email))

In this case, we are using %s as placeholders for the guest_name and email variables. The second argument to execute is a tuple containing the variables in the order that they appear in the placeholders.

The cursor will take care of inserting the variables into the statement and sending it to the database, preventing SQL injection by ensuring that malicious SQL statements cannot be sent as variables.

Retrieving data from the database

Now that you know how to safely insert data into the database, let's take a look at how you can retrieve data from the database using Python, without relying on the command line tool.

cursor.execute("SELECT * FROM guests;")
results = cursor.fetchall()


for row in results:
    print(row)

This will execute a SELECT statement on the guests table and store the results in a results variable. We can then iterate through the rows in results, printing out each row.

By following these best practices for preventing SQL injection when using cursors in Python, you can ensure that your database interactions are secure and no malicious statements are executed.