Django SQL Injection

SQL injection attacks remain one of the most common and dangerous security threats to web applications. Fortunately, Django provides robust protection against these attacks through its Object-Relational Mapping (ORM) system. In this guide, we'll explore what SQL injection is, why it's dangerous, how Django prevents it, and best practices to ensure your Django applications remain secure.

What is SQL Injection?

SQL injection is a code injection technique where malicious SQL statements are inserted into entry fields in an application, which are then executed by the database. If successful, these attacks can:

Read sensitive data from the database
Modify database data (insert, update, delete)
Execute administration operations on the database
Recover the content of a given file on the DBMS file system
In some cases, issue commands to the operating system

How SQL Injection Works

Let's look at a vulnerable example not using Django's ORM:

python
# VULNERABLE CODE - DO NOT USE
def get_user_by_id(request):
    user_id = request.GET.get('id')
    query = f"SELECT * FROM users WHERE id = {user_id}"
    with connection.cursor() as cursor:
        cursor.execute(query)
        user = cursor.fetchone()
    return user

If a malicious user sends a request with id=1; DROP TABLE users;, the resulting query would be:

sql
SELECT * FROM users WHERE id = 1; DROP TABLE users;

This would select the user with ID 1 and then delete the entire users table!

How Django Protects Against SQL Injection

Django's ORM automatically parameterizes queries, separating the SQL code from the data being inserted. This prevents attackers from injecting malicious SQL code.

1. Using QuerySets

python
# SAFE: Django ORM method
def get_user_by_id(request):
    user_id = request.GET.get('id')
    user = User.objects.get(id=user_id)
    return user

In this example, Django parameterizes the query, so even if user_id contains malicious SQL, it will be treated as data, not executable code.

2. Using Raw Queries Safely

Sometimes you need raw SQL. Django provides safe ways to do this:

python
# SAFE: Using params argument
from django.db import connection

def get_user_by_id(request):
    user_id = request.GET.get('id')
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM auth_user WHERE id = %s", [user_id])
        user = cursor.fetchone()
    return user

Here, the %s placeholder ensures the user_id value is properly escaped.

Common Vulnerable Patterns to Avoid

1. String Formatting in Raw SQL

python
# VULNERABLE CODE - DO NOT USE
def search_products(request):
    query = request.GET.get('q', '')
    raw_sql = f"SELECT * FROM products WHERE name LIKE '%{query}%'"
    return Product.objects.raw(raw_sql)

2. Extra Method with Unsafe Parameters

python
# VULNERABLE CODE - DO NOT USE
def filter_by_status(request):
    status = request.GET.get('status')
    return User.objects.extra(where=[f"status='{status}'"])

Secure Alternatives

1. Using Q Objects for Complex Queries

python
from django.db.models import Q

def search_products(request):
    query = request.GET.get('q', '')
    return Product.objects.filter(Q(name__icontains=query) | Q(description__icontains=query))

2. Using Proper Parameterization with Raw SQL

python
from django.db import connection

def get_complex_report(date_from, date_to, category):
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT product_id, SUM(quantity) as total_sold
            FROM sales
            WHERE sale_date BETWEEN %s AND %s
            AND category = %s
            GROUP BY product_id
            ORDER BY total_sold DESC
        """, [date_from, date_to, category])
        return cursor.fetchall()

Real-World Example: Building a Secure Search Feature

Let's build a secure product search feature that allows for complex filtering:

python
# models.py
from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    description = models.TextField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    category = models.CharField(max_length=50)
    in_stock = models.BooleanField(default=True)

python
# views.py
from django.db.models import Q
from django.shortcuts import render
from .models import Product

def advanced_search(request):
    products = Product.objects.all()
    
    # Get search parameters
    query = request.GET.get('q', '')
    min_price = request.GET.get('min_price')
    max_price = request.GET.get('max_price')
    category = request.GET.get('category')
    in_stock_only = request.GET.get('in_stock') == 'on'
    
    # Apply filters based on parameters
    if query:
        products = products.filter(
            Q(name__icontains=query) | Q(description__icontains=query)
        )
    
    if min_price:
        products = products.filter(price__gte=min_price)
    
    if max_price:
        products = products.filter(price__lte=max_price)
    
    if category:
        products = products.filter(category=category)
    
    if in_stock_only:
        products = products.filter(in_stock=True)
    
    return render(request, 'products/search.html', {
        'products': products,
        'query': query,
        'min_price': min_price,
        'max_price': max_price,
        'category': category,
        'in_stock_only': in_stock_only,
    })

This implementation safely handles all user inputs and builds a secure query using Django's ORM, preventing any possibility of SQL injection.

When You Might Need Raw SQL

Sometimes you need raw SQL for complex queries that are difficult to express with the ORM. Here's how to do it safely:

python
# Complex aggregation with raw SQL
from django.db import connection
from collections import namedtuple

def get_sales_report(year, quarter):
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                p.category,
                SUM(s.quantity * p.price) as revenue,
                COUNT(DISTINCT s.customer_id) as unique_customers
            FROM 
                sales s
            JOIN 
                products p ON s.product_id = p.id
            WHERE 
                EXTRACT(YEAR FROM s.sale_date) = %s
                AND EXTRACT(QUARTER FROM s.sale_date) = %s
            GROUP BY 
                p.category
            ORDER BY 
                revenue DESC
        """, [year, quarter])
        
        # Convert to named tuples for easy access
        Result = namedtuple('Result', [col[0] for col in cursor.description])
        return [Result(*row) for row in cursor.fetchall()]

Best Practices for SQL Injection Prevention in Django

Use Django's ORM whenever possible
- QuerySets automatically handle parameterization
Parameterize all raw SQL queries
- Always use placeholders (%s) and pass parameters separately
Avoid extra() and raw() methods when possible
- If needed, ensure all inputs are properly parameterized
Never use string formatting (f-strings, .format()) to build SQL queries
- This bypasses Django's protection mechanisms
Use database-specific escaping only as a last resort
- django.db.connection.ops.quote_name() for table/column names
Validate and sanitize all user inputs
- Use Django forms and validators for input validation
Apply the principle of least privilege to database users
- Create different database users with limited permissions for your Django application

Summary

SQL injection remains a critical security vulnerability, but Django's ORM provides excellent protection by default. By understanding how SQL injection works and following Django's recommended practices:

Use the ORM whenever possible
Properly parameterize any raw SQL queries
Never build dynamic SQL with string concatenation or formatting
Validate all user inputs

You can ensure your Django applications remain secure against SQL injection attacks. Remember that security is a continuous process, and staying informed about the latest security practices is essential for maintaining robust applications.

Additional Resources

Exercises

Identify and fix SQL injection vulnerabilities in the following code:

python
def search_users(request):
    name = request.GET.get('name', '')
    query = "SELECT id, username FROM auth_user WHERE first_name LIKE '%" + name + "%'"
    return User.objects.raw(query)

Write a secure function that finds all products in a given price range and category, ordered by popularity (number of sales).
Create a secure Django view that allows users to filter blog posts by author, date range, and tags, without risking SQL injection.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

What is SQL Injection?​

How SQL Injection Works​

How Django Protects Against SQL Injection​

1. Using QuerySets​

2. Using Raw Queries Safely​

Common Vulnerable Patterns to Avoid​

1. String Formatting in Raw SQL​

2. Extra Method with Unsafe Parameters​

Secure Alternatives​

1. Using Q Objects for Complex Queries​

2. Using Proper Parameterization with Raw SQL​

Real-World Example: Building a Secure Search Feature​

When You Might Need Raw SQL​

Best Practices for SQL Injection Prevention in Django​

Summary​

Additional Resources​

Exercises​