logo

Database

SQL injection - Code

Need

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

Context

• Usage of Python 3 for developing applications and scripts

• Usage of Django for building web applications in Python

Description

1. Non compliant code

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.

2. Steps

• Replace the dynamic SQL statement with a parameterized query.

• Instead of directly inserting the user input into the SQL query, use placeholders.

• Pass the user input as arguments to the execute method, which will handle escaping special characters to prevent SQL injection.

• Ensure that data validation is performed before the user input is processed.

3. 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.

On this page