SQL injection - Code - Python

Need

Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context

1. Usage of Python 3 for developing applications and scripts
2. Usage of Django for building web applications in Python

Description

Insecure Code Example

from django.db import connection

def get_user(username):
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM users WHERE username = '%s'" % username)
        row = cursor.fetchone()
    return row

The above code is a simple function in Django that fetches a user from the database based on the username. The function uses a raw SQL query to get the user from the database. The vulnerability lies in the `execute` method where the SQL query is being formatted with the username directly. This means whatever string is passed as `username` will be directly placed into the SQL query. If the `username` string contains any SQL syntax, it will be executed as part of the query. For example, if the `username` passed was `'; DROP TABLE users;--`, the SQL query would become `SELECT * FROM users WHERE username = ''; DROP TABLE users;--`. This would result in the `users` table being dropped from the database. This is a classic example of an SQL Injection vulnerability because it allows an attacker to inject arbitrary SQL code into the query.

Steps

1. Replace the dynamic SQL statement with a parameterized query.
2. Instead of directly inserting the user input into the SQL query, use placeholders.
3. Pass the user input as arguments to the execute method, which will handle escaping special characters to prevent SQL injection.
4. Ensure that data validation is performed before the user input is processed.

Secure Code Example

from django.db import connection

def get_user(username):
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM users WHERE username = %s", [username])
        row = cursor.fetchone()
    return row

The original code was vulnerable to SQL injection because it used string formatting to include `username` directly into the SQL query. This means that if a user provided a `username` that included SQL commands, those commands would be executed directly on the database, leading to potential data leaks, data corruption, or worse. The fixed code prevents this by using a parameterized query. Instead of including `username` directly in the SQL command, it uses a placeholder (`%s`) and then provides `username` as a separate argument to the `execute` method. This ensures that `username` is properly escaped and can't inject additional SQL commands into the query. This is a simple and effective way to prevent SQL injection attacks. However, it's also important to validate and sanitize all user input as part of a comprehensive security strategy. This code assumes that such validation is performed elsewhere.

References

Last updated

2023/09/18