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