SQL injection protection via ORM in Django - Mini Project: Build & Apply

SQL Injection Protection via ORM in Django

📖 Scenario: You are building a simple Django app to manage a list of books in a library. Users can search for books by title. To keep the app safe, you want to protect it from SQL injection attacks by using Django's ORM properly.

🎯 Goal: Build a Django model for books, set up a search query using Django ORM to safely filter books by title, and complete the view to return the filtered results.

📋 What You'll Learn

Create a Django model named Book with fields title (string) and author (string).

Create a variable search_term with the exact value 'Django'.

Use Django ORM's filter() method with title__icontains=search_term to get matching books.

Complete the view function to return the filtered books queryset.

💡 Why This Matters

🌍 Real World

Web applications often need to query databases based on user input. Using ORM methods like Django's filter protects against SQL injection, a common security risk.

💼 Career

Understanding how to safely query databases using ORM is essential for backend developers to build secure web applications.

1

Create the Book model

Create a Django model called Book with two fields: title and author, both as models.CharField with max_length=100.

Django

# Your code here

Hint

Use models.CharField(max_length=100) for both fields inside the Book class.

2

Set the search term variable

Create a variable called search_term and set it exactly to the string 'Django'.

Django

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)

# Your code here

Hint

Assign the string 'Django' to the variable search_term.

3

Filter books using ORM safely

Use Django ORM's Book.objects.filter() with title__icontains=search_term to create a variable called filtered_books that holds the filtered queryset.

Django

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)

search_term = 'Django'

# Your code here

Hint

Use Book.objects.filter(title__icontains=search_term) to get books with titles containing the search term.

4

Complete the view function

Define a Django view function called search_books that takes request as a parameter and returns the filtered_books queryset.

Django

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)

search_term = 'Django'

filtered_books = Book.objects.filter(title__icontains=search_term)

# Your code here

Hint

Define a function search_books that returns the filtered_books queryset.

Practice

1. Which of the following best explains how Django ORM protects against SQL injection?

easy

A. It automatically escapes user inputs when building queries.

B. It disables all user inputs by default.

C. It requires manual escaping of inputs in queries.

D. It converts all queries to raw SQL strings.

Solution

1. Step 1: Understand Django ORM query building

   Django ORM builds SQL queries by safely escaping user inputs automatically.

2. Step 2: Compare options with ORM behavior

   Only automatic escaping matches Django ORM's protection against SQL injection.

3. Final Answer:

   It automatically escapes user inputs when building queries. -> Option A

4. Quick Check:

   Django ORM auto-escapes inputs = C [OK]

Hint: Remember: ORM escapes inputs automatically to prevent injection [OK]

Common Mistakes:

• Thinking ORM disables inputs
• Believing manual escaping is needed
• Assuming ORM uses raw SQL strings

2. Which Django ORM method is safe to use for filtering records with user input?

easy

A. Model.objects.raw(f"SELECT * FROM table WHERE name = '{user_input}'")

B. Model.objects.filter("name = " + user_input)

C. Model.objects.filter(name=user_input)

D. Model.objects.execute_sql("SELECT * FROM table WHERE name = " + user_input)

Solution

1. Step 1: Identify safe ORM filtering syntax

   Model.objects.filter(name=user_input) uses ORM's parameter binding and escapes input safely.

2. Step 2: Analyze other options for unsafe practices

   Options A, B, and C build raw SQL strings or invalid syntax, risking injection.

3. Final Answer:

   Model.objects.filter(name=user_input) -> Option C

4. Quick Check:

   Use filter() with keyword args for safe queries = D [OK]

Hint: Use filter() with keyword arguments, not raw SQL strings [OK]

Common Mistakes:

• Using raw SQL with string concatenation
• Passing raw SQL strings to filter()
• Ignoring ORM's parameter binding

3. What will be the output of this Django ORM query if user_input = "Robert'); DROP TABLE users;--"?

users = User.objects.filter(username=user_input)
print(users.query)

medium

A. A raw SQL query that deletes the users table

B. An empty query with no filtering

C. A syntax error due to unescaped quotes

D. A safe SQL query with escaped input preventing injection

Solution

1. Step 1: Understand ORM query with dangerous input

   ORM escapes dangerous characters in user_input to prevent SQL injection.

2. Step 2: Analyze printed query behavior

   Printed query shows safe SQL with escaped input, not raw injection or errors.

3. Final Answer:

   A safe SQL query with escaped input preventing injection -> Option D

4. Quick Check:

   ORM escapes dangerous input = B [OK]

Hint: ORM escapes dangerous input, so injection won't happen [OK]

Common Mistakes:

• Assuming raw SQL runs as is
• Expecting syntax errors from quotes
• Thinking ORM ignores dangerous input

4. Identify the error in this Django ORM code that tries to prevent SQL injection:

query = "SELECT * FROM users WHERE username = '%s'" % user_input
users = User.objects.raw(query)

medium

A. The raw() method automatically escapes inputs, so no error.

B. Using raw SQL with string formatting allows SQL injection.

C. The filter() method should be used instead of raw().

D. The query string is missing parameter placeholders.

Solution

1. Step 1: Analyze string formatting with user input

   Using % formatting inserts user_input directly, risking SQL injection.

2. Step 2: Understand raw() method behavior

   raw() executes raw SQL without escaping, so injection risk remains.

3. Final Answer:

   Using raw SQL with string formatting allows SQL injection. -> Option B

4. Quick Check:

   String formatting + raw() = injection risk = A [OK]

Hint: Never build raw SQL with string formatting; use ORM methods [OK]

Common Mistakes:

• Assuming raw() escapes inputs
• Using raw SQL instead of filter()
• Ignoring injection risk in string formatting

5. You want to safely filter users by email domain using Django ORM. Which approach correctly prevents SQL injection?

user_domain = request.GET.get('domain')
# Which code is safe?
A) User.objects.filter(email__endswith=user_domain)
B) User.objects.raw(f"SELECT * FROM users WHERE email LIKE '%{user_domain}'")
C) User.objects.filter(email__endswith='%' + user_domain)
D) User.objects.raw("SELECT * FROM users WHERE email LIKE '%" + user_domain + "'")

hard

A. User.objects.filter(email__endswith=user_domain)

B. User.objects.raw(f"SELECT * FROM users WHERE email LIKE '%{user_domain}'")

C. User.objects.filter(email__endswith='%' + user_domain)

D. User.objects.raw("SELECT * FROM users WHERE email LIKE '%" + user_domain + "'")

Solution

1. Step 1: Identify safe ORM filtering for email domain

   Using filter() with email__endswith=user_domain safely escapes input and builds query.

2. Step 2: Analyze raw() and string concatenation risks

   Options B and D use raw SQL with string interpolation, risking injection. User.objects.filter(email__endswith='%' + user_domain) incorrectly adds '%' in Python string, not ORM pattern.

3. Final Answer:

   User.objects.filter(email__endswith=user_domain) -> Option A

4. Quick Check:

   Use ORM filter with lookup for safe input handling = A [OK]

Hint: Use ORM lookups like __endswith, avoid raw SQL with user input [OK]

Common Mistakes:

• Using raw SQL with user input directly
• Adding SQL wildcards in Python strings
• Ignoring ORM's safe query building