Navigate by Range

Django Interview Questions 21-25 (Query Optimization & Security)

Hello! In this lesson, we're moving into two of the most critical areas for a professional Django developer: performance and security.

First, we'll cover the most important database optimization you can make: fixing the "N+1 query problem" using `select_related` and `prefetch_related`. Then, we'll spend the rest of the lesson on security, covering CSRF attacks, the "big picture" of securing an app, the difference between authentication and authorization, and how Django keeps user passwords safe. These are essential, non-negotiable topics for a full-stack role.

21. Explain select_related vs prefetch_related with examples.

Both `select_related` and `prefetch_related` are powerful performance tools designed to solve the "N+1 query problem".

The N+1 Problem: Imagine you fetch 100 blog posts. Then, in your template, you loop through them and print each author's name (`post.author.name`). Django will hit the database 101 times: 1 query to get the 100 posts, and then N (100) more queries, one for each post, to fetch the author. This is extremely slow.

Analogy: The Inefficient Shopper

N+1 Problem: You go to the grocery store with a 10-item list. You get a cart (1 query), then go to the cereal aisle, get cereal (1 query), walk back to the cart, go to the milk aisle, get milk (1 query), and so on. 11 trips in total.
`select_related` (SQL JOIN): You know you need cereal and its matching milk. You go to the cereal aisle and join it with the milk aisle, getting both in one single, efficient trip.
`prefetch_related` (Separate Query): You get all 10 items from your list in one trip. Then, you hand a second list to a store assistant (e.g., "get the milk, eggs, and butter"). They make one separate, efficient trip and bring them all back. Total: 2 trips.

Feature	`select_related`	`prefetch_related`
SQL Method	SQL JOIN	Separate `IN` query
Number of Queries	One (can be large)	Two (or more) small queries
Use For	`ForeignKey`, `OneToOneField`	`ManyToManyField`, Reverse `ForeignKey`

Code Example: Assume `Book` has a `ForeignKey` to `Author`. An `Author` can write many `Books`.

# 1. The N+1 Problem (BAD):
# (Assuming 2 books in the database)

print("--- N+1 Query ---")
books = Book.objects.all()
for book in books:
    print(book.author.name) # DB Hit!
# Output:
# --- N+1 Query ---
# (Query 1: Fetches all Books)
# (Query 2: Fetches Author for Book 1)
# Author One
# (Query 3: Fetches Author for Book 2)
# Author Two
# Total: 3 Queries

# 2. select_related (GOOD for ForeignKey):

print("--- select_related ---")
books = Book.objects.select_related('author').all()
for book in books:
    print(book.author.name) # No DB Hit!
# Output:
# --- select_related ---
# (Query 1: Fetches all Books AND Authors with a JOIN)
# Author One
# Author Two
# Total: 1 Query

# 3. prefetch_related (GOOD for reverse relation):

print("--- prefetch_related ---")
authors = Author.objects.prefetch_related('book_set').all()
for author in authors:
    print(f"{author.name}'s books:")
    # .all() here does NOT hit the DB
    for book in author.book_set.all():
        print(f"  - {book.title}")
# Output:
# --- prefetch_related ---
# (Query 1: Fetches all Authors)
# (Query 2: Fetches all Books WHERE author_id IN (1, 2))
# Author One's books:
#   - Book Alpha
# Author Two's books:
#   - Book Beta
# Total: 2 Queries

22. What is CSRF and how does Django prevent CSRF attacks?

CSRF (Cross-Site Request Forgery) is a major web vulnerability. It's an attack that tricks a logged-in user into performing an action they didn't intend to.

Analogy: The Malicious Postcard

1. You are logged into your bank at `bank.com`. Your browser has a valid session cookie.
2. You visit a malicious site, `evil.com`.
3. `evil.com` has a hidden form (like an invisible postcard) that looks like this:
`<form action="https://bank.com/transfer" method="POST">`
`<input name="amount" value="10000">`
`<input name="to_account" value="attacker">`
A script on `evil.com` automatically submits this form without you knowing.
4. Your browser, trying to be helpful, sees a `POST` request to `bank.com` and automatically attaches your login cookie.
5. `bank.com` receives a valid, authenticated request to transfer $10,000, and it has no way of knowing you didn't click the "submit" button.

How Django Prevents It (The "Secret Token" Defense)

Django's `CsrfViewMiddleware` implements a secret token system to ensure only forms from your site can submit data.

1. When Django renders a form on your legitimate site, it adds a hidden input using the {% csrf_token %} tag.
2. This renders as: `<input type="hidden" name="csrfmiddlewaretoken" value="REALLY_LONG_SECRET_VALUE">`
3. When you submit this form, it sends both your session cookie and this secret `csrfmiddlewaretoken`.
4. The middleware checks that the token from the form matches a token associated with your session.
The Fix: The attacker on `evil.com` cannot know or guess this secret token. Their form will be missing it, so the middleware will see the mismatch and block the request with a 403 Forbidden error.

<form method="post">
    {% csrf_token %}
    
    {{ form.as_p }}
    <button type="submit">Submit</button>
</form>

23. How do you secure a Django application? (OWASP, Django security middleware)

Django is "secure by default" but still requires correct configuration. Securing an app is about layered defense.

Analogy: You don't just buy one big lock for your house (a firewall). You also lock your windows (XSS protection), use a deadbolt (CSRF), and have an alarm system (logging/monitoring).

Here is a checklist based on the OWASP Top 10 (Open Web Application Security Project):

1. Prevent Injection (e.g., SQL Injection):
- Solution: Use the Django ORM. It automatically parameterizes all queries, making SQL injection impossible.
- DON'T ever do this: `User.objects.raw(f"SELECT * FROM users WHERE name = {username})`.
2. Prevent Cross-Site Scripting (XSS):
- Solution: Django's template engine auto-escapes all variables by default.
- {{ user.comment }} will safely render `<script>alert(1)</script>` as plain text. Never use {{ ...|safe }} on user input.
3. Prevent CSRF:
- Solution: Always use the {% csrf_token %} tag in your forms and ensure the `CsrfViewMiddleware` is enabled.
4. Use the Security Middleware:
- Solution: Enable `django.middleware.security.SecurityMiddleware`. In production, set:
- `SECURE_SSL_REDIRECT = True`: Enforces HTTPS.
- `SECURE_HSTS_SECONDS`: Tells browsers to *only* use HTTPS.
- `X_FRAME_OPTIONS = 'DENY'`: Prevents clickjacking attacks.
5. Production Settings:
- Solution: NEVER run with `DEBUG = True`. This leaks all your settings and tracebacks.
- Use a strong, random `SECRET_KEY` and keep it out of source control (use environment variables).

24. Explain authentication vs authorization in Django.

This is a fundamental security concept. Both are handled by Django, but they answer two very different questions.

Analogy: A Corporate Building

Authentication (AuthN): This is "Who are you?" It's the ID badge reader at the front door. You present your credentials (username/password) to prove your identity.
Authorization (AuthZ): This is "What are you allowed to do?" This happensafter you are inside. Your badge (identity) is checked against a list to see if you can open the "Server Room&"quot; or the "CEO's Office."

Concept	Authentication (AuthN)	Authorization (AuthZ)
The Question	Who are you?	What can you do?
Django System	`django.contrib.auth` (User model, `authenticate()`, `login()`)	Permissions Framework (Groups, `user.has_perm()`)
Example Check	`if request.user.is_authenticated:`	`if request.user.is_staff:` or `if user.has_perm('app.add_post')`
CBV Mixin	`LoginRequiredMixin`	`PermissionRequiredMixin`

25. How does Django securely store passwords?

Django does not store passwords. It stores hashes of passwords. This is a one-way process designed to make it impossible to recover the original password, even if the database is stolen.

Analogy: The Meat Grinder

You put a password ("my_secret_pass123") into a hashing function (the meat grinder).
A unique, jumbled string comes out (the hash, or "ground meat").
It is computationally impossible to reverse this process-you can't put the ground meat back in the grinder and get the original password.

How Django's Hashing Works:

Django uses a robust, industry-standard method:

1. Algorithm: It uses a "Key Derivation Function" by default, PBKDF2 with a SHA256 hash.
2. Salt: For every new user, Django generates a unique, random string called a "salt." This salt is combined with the password *before* hashing. This prevents "Rainbow Table" attacks (where attackers have pre-computed hashes for common passwords).
3. Iterations (Stretching): Django runs the hashing function hundreds of thousands of times (e.g., 390,000 iterations). This makes the process "slow" on purpose, which makes it extremely expensive and time-consuming for an attacker to try and brute-force.

A stored password hash in Django's database looks like this:

pbkdf2_sha256$390000$aBcD1eFgH2iJ$kLmN3oPqR4sT5uV6w...

This string contains the algorithm (pbkdf2_sha256), the iterations (390000), the salt (aBcD1e...), and the final hash (kLmN3o...).

How Login Works:
Django never un-hashes the password.

1. User submits their password ("my_secret_pass123").
2. Django retrieves the stored hash string.
3. It extracts the salt and iteration count from the string.
4. It runs the user's submitted password through the *same* process (using the *same* salt and *same* 390,000 iterations).
5. It compares the two final hashes. If they match, the password is correct.

Check your understanding

2 questions · no sign-up · misses join your review queue, on this device only

0/2 answered

1.When do you use prefetch_related rather than select_related?

When the relation is ForeignKey or OneToOneWhen the relation is ManyToMany or a reverse ForeignKeyWhen the table has an indexWhen the queryset is small

2.What is the signature of an N+1 query problem?

One slow query with a full table scanA query returning too many columnsOne query for the list, then one additional query per row when a related field is accessedA deadlock between two transactions

0 / 74 ranges

Progress is stored in this browser only - no sign-up, nothing sent anywhere.

Go deeper

The same topic at architecture level, in the system design curriculum.

Database indexes and query performance

The N+1 problem is a query-count problem; this covers the indexing side of it.

Security in system design

CSRF, auth and the rest of the surface, at architecture level.

Previous Next

Hello! In this lesson, we're moving into two of the most critical areas for a professional Django developer: performance and security.

21. Explain select_related vs prefetch_related with examples.

Both `select_related` and `prefetch_related` are powerful performance tools designed to solve the "N+1 query problem".

Analogy: The Inefficient Shopper

N+1 Problem: You go to the grocery store with a 10-item list. You get a cart (1 query), then go to the cereal aisle, get cereal (1 query), walk back to the cart, go to the milk aisle, get milk (1 query), and so on. 11 trips in total.
`select_related` (SQL JOIN): You know you need cereal and its matching milk. You go to the cereal aisle and join it with the milk aisle, getting both in one single, efficient trip.
`prefetch_related` (Separate Query): You get all 10 items from your list in one trip. Then, you hand a second list to a store assistant (e.g., "get the milk, eggs, and butter"). They make one separate, efficient trip and bring them all back. Total: 2 trips.

Feature	`select_related`	`prefetch_related`
SQL Method	SQL JOIN	Separate `IN` query
Number of Queries	One (can be large)	Two (or more) small queries
Use For	`ForeignKey`, `OneToOneField`	`ManyToManyField`, Reverse `ForeignKey`

Code Example: Assume `Book` has a `ForeignKey` to `Author`. An `Author` can write many `Books`.

# 1. The N+1 Problem (BAD):
# (Assuming 2 books in the database)

print("--- N+1 Query ---")
books = Book.objects.all()
for book in books:
    print(book.author.name) # DB Hit!
# Output:
# --- N+1 Query ---
# (Query 1: Fetches all Books)
# (Query 2: Fetches Author for Book 1)
# Author One
# (Query 3: Fetches Author for Book 2)
# Author Two
# Total: 3 Queries

# 2. select_related (GOOD for ForeignKey):

print("--- select_related ---")
books = Book.objects.select_related('author').all()
for book in books:
    print(book.author.name) # No DB Hit!
# Output:
# --- select_related ---
# (Query 1: Fetches all Books AND Authors with a JOIN)
# Author One
# Author Two
# Total: 1 Query

# 3. prefetch_related (GOOD for reverse relation):

print("--- prefetch_related ---")
authors = Author.objects.prefetch_related('book_set').all()
for author in authors:
    print(f"{author.name}'s books:")
    # .all() here does NOT hit the DB
    for book in author.book_set.all():
        print(f"  - {book.title}")
# Output:
# --- prefetch_related ---
# (Query 1: Fetches all Authors)
# (Query 2: Fetches all Books WHERE author_id IN (1, 2))
# Author One's books:
#   - Book Alpha
# Author Two's books:
#   - Book Beta
# Total: 2 Queries

22. What is CSRF and how does Django prevent CSRF attacks?

CSRF (Cross-Site Request Forgery) is a major web vulnerability. It's an attack that tricks a logged-in user into performing an action they didn't intend to.

Analogy: The Malicious Postcard

1. You are logged into your bank at `bank.com`. Your browser has a valid session cookie.
2. You visit a malicious site, `evil.com`.
3. `evil.com` has a hidden form (like an invisible postcard) that looks like this:
`<form action="https://bank.com/transfer" method="POST">`
`<input name="amount" value="10000">`
`<input name="to_account" value="attacker">`
A script on `evil.com` automatically submits this form without you knowing.
4. Your browser, trying to be helpful, sees a `POST` request to `bank.com` and automatically attaches your login cookie.
5. `bank.com` receives a valid, authenticated request to transfer $10,000, and it has no way of knowing you didn't click the "submit" button.

How Django Prevents It (The "Secret Token" Defense)

Django's `CsrfViewMiddleware` implements a secret token system to ensure only forms from your site can submit data.

1. When Django renders a form on your legitimate site, it adds a hidden input using the {% csrf_token %} tag.
2. This renders as: `<input type="hidden" name="csrfmiddlewaretoken" value="REALLY_LONG_SECRET_VALUE">`
3. When you submit this form, it sends both your session cookie and this secret `csrfmiddlewaretoken`.
4. The middleware checks that the token from the form matches a token associated with your session.
The Fix: The attacker on `evil.com` cannot know or guess this secret token. Their form will be missing it, so the middleware will see the mismatch and block the request with a 403 Forbidden error.

<form method="post">
    {% csrf_token %}
    
    {{ form.as_p }}
    <button type="submit">Submit</button>
</form>

23. How do you secure a Django application? (OWASP, Django security middleware)

Django is "secure by default" but still requires correct configuration. Securing an app is about layered defense.

Analogy: You don't just buy one big lock for your house (a firewall). You also lock your windows (XSS protection), use a deadbolt (CSRF), and have an alarm system (logging/monitoring).

Here is a checklist based on the OWASP Top 10 (Open Web Application Security Project):

1. Prevent Injection (e.g., SQL Injection):
- Solution: Use the Django ORM. It automatically parameterizes all queries, making SQL injection impossible.
- DON'T ever do this: `User.objects.raw(f"SELECT * FROM users WHERE name = {username})`.
2. Prevent Cross-Site Scripting (XSS):
- Solution: Django's template engine auto-escapes all variables by default.
- {{ user.comment }} will safely render `<script>alert(1)</script>` as plain text. Never use {{ ...|safe }} on user input.
3. Prevent CSRF:
- Solution: Always use the {% csrf_token %} tag in your forms and ensure the `CsrfViewMiddleware` is enabled.
4. Use the Security Middleware:
- Solution: Enable `django.middleware.security.SecurityMiddleware`. In production, set:
- `SECURE_SSL_REDIRECT = True`: Enforces HTTPS.
- `SECURE_HSTS_SECONDS`: Tells browsers to *only* use HTTPS.
- `X_FRAME_OPTIONS = 'DENY'`: Prevents clickjacking attacks.
5. Production Settings:
- Solution: NEVER run with `DEBUG = True`. This leaks all your settings and tracebacks.
- Use a strong, random `SECRET_KEY` and keep it out of source control (use environment variables).

24. Explain authentication vs authorization in Django.

This is a fundamental security concept. Both are handled by Django, but they answer two very different questions.

Analogy: A Corporate Building

Authentication (AuthN): This is "Who are you?" It's the ID badge reader at the front door. You present your credentials (username/password) to prove your identity.
Authorization (AuthZ): This is "What are you allowed to do?" This happensafter you are inside. Your badge (identity) is checked against a list to see if you can open the "Server Room&"quot; or the "CEO's Office."

Concept	Authentication (AuthN)	Authorization (AuthZ)
The Question	Who are you?	What can you do?
Django System	`django.contrib.auth` (User model, `authenticate()`, `login()`)	Permissions Framework (Groups, `user.has_perm()`)
Example Check	`if request.user.is_authenticated:`	`if request.user.is_staff:` or `if user.has_perm('app.add_post')`
CBV Mixin	`LoginRequiredMixin`	`PermissionRequiredMixin`

25. How does Django securely store passwords?

Django does not store passwords. It stores hashes of passwords. This is a one-way process designed to make it impossible to recover the original password, even if the database is stolen.

Analogy: The Meat Grinder

You put a password ("my_secret_pass123") into a hashing function (the meat grinder).
A unique, jumbled string comes out (the hash, or "ground meat").
It is computationally impossible to reverse this process-you can't put the ground meat back in the grinder and get the original password.

How Django's Hashing Works:

Django uses a robust, industry-standard method:

1. Algorithm: It uses a "Key Derivation Function" by default, PBKDF2 with a SHA256 hash.
2. Salt: For every new user, Django generates a unique, random string called a "salt." This salt is combined with the password *before* hashing. This prevents "Rainbow Table" attacks (where attackers have pre-computed hashes for common passwords).
3. Iterations (Stretching): Django runs the hashing function hundreds of thousands of times (e.g., 390,000 iterations). This makes the process "slow" on purpose, which makes it extremely expensive and time-consuming for an attacker to try and brute-force.

A stored password hash in Django's database looks like this:

pbkdf2_sha256$390000$aBcD1eFgH2iJ$kLmN3oPqR4sT5uV6w...

This string contains the algorithm (pbkdf2_sha256), the iterations (390000), the salt (aBcD1e...), and the final hash (kLmN3o...).

How Login Works:
Django never un-hashes the password.

1. User submits their password ("my_secret_pass123").
2. Django retrieves the stored hash string.
3. It extracts the salt and iteration count from the string.
4. It runs the user's submitted password through the *same* process (using the *same* salt and *same* 390,000 iterations).
5. It compares the two final hashes. If they match, the password is correct.