A DRF job-listings endpoint over millions of rows passes ?sort= straight to order_by() and uses SearchFilter with '$description'; what breaks in production and how do you harden it?

seniorshould knowfreq 30%

answer

clients choosing any column path
FieldError is not a 400
ties break pagination
client-written regexes
whitelist, index, tiebreak, cap

basics

~20 s

Raw order_by() lets clients sort by any related or hidden field, by '?' for random order, or crash with FieldError (a 500); $ lets them run costly regexes. Use OrderingFilter with indexed ordering_fields, a unique tiebreaker, and non-regex search.

solid answer

~40 s

Passing ?sort= to order_by() accepts any field path: clients can sort by company__owner__password or an internal score and infer values from row positions, send ? for a random-order sort on every request, or send an unknown name so order_by() raises FieldError, which DRF answers with a 500. Replace it with OrderingFilter and an explicit ordering_fields list of indexed columns. Sorting only by salary_max has ties, so paged results repeat or skip jobs; subclass the filter to append a unique tiebreaker like -pk. The $ prefix makes the database run client-written regular expressions, a denial-of-service risk; use ^, = or @ full-text instead. Default icontains over a long text column scans the table, and DRF does not cap search terms, so override get_search_terms() to limit them.

Passing `?sort=` to `order_by()` accepts any field path: clients can sort by `company__owner__password` or an internal score and infer values from row positions, send `?` for a random-order sort on every request, or send an unknown name so `order_by()` raises `FieldError`, which DRF answers with a 500. Replace it with `OrderingFilter` and an explicit `ordering_fields` list of indexed columns. Sorting only by `salary_max` has ties, so paged results repeat or skip jobs; subclass the filter to append a unique tiebreaker like `-pk`. The `$` prefix makes the database run client-written regular expressions, a denial-of-service risk; use `^`, `=` or `@` full-text instead. Default `icontains` over a long text column scans the table, and DRF does not cap search terms, so override `get_search_terms()` to limit them.

code

python · 19 lines

python

from rest_framework import filters


class StableOrderingFilter(filters.OrderingFilter):
    def get_ordering(self, request, queryset, view):
        ordering = super().get_ordering(request, queryset, view)
        if not ordering:
            return ordering
        ordering = list(ordering)
        if not {"pk", "-pk"} & set(ordering):
            ordering.append("-pk")
        return ordering


class BoundedSearchFilter(filters.SearchFilter):
    max_terms = 5

    def get_search_terms(self, request):
        return super().get_search_terms(request)[: self.max_terms]

go deeper

for a junior

Remember that client-chosen sorting should go through OrderingFilter with a list of allowed fields rather than straight into order_by().

for a middle

Explain why raw order_by() raises FieldError for unknown names, why ties make paginated results unstable, and what each search prefix costs.

for a senior

Diagnose the production symptoms, from 500s to CPU spikes to duplicate rows, and ship the fix: whitelist, indexes, tiebreaker, no regex prefix, term cap.

for a principal

Decide which sort and search capabilities the public API promises at all, since each is an index to maintain and a query shape to protect under load.

Problem 1: a raw order_by() is an open query surface

A hand-rolled view such as Job.objects.order_by(self.request.query_params.get('sort', '-posted_at')) looks harmless because Django escapes identifiers and this is not SQL injection in the classic sense. The problems are elsewhere:

Any field path is accepted. Django's order_by() follows __ across relations, so ?sort=company__owner__password sorts by the password hash of each company's owner. Nothing is serialized, but the order itself is an oracle: by comparing target rows with rows of known value, a client narrows the hidden value.
Random ordering. order_by('?') asks the database for a random order, an expensive sort that the client can request on every call.
Errors become 500s. Django's add_ordering() validates names eagerly; an unknown name raises FieldError ('Cannot resolve keyword ... into field') inside the view. That is not a DRF APIException, so the client sees a server error and your error tracking fills with noise.

OrderingFilter fixes all three: it keeps only terms whose names appear in ordering_fields, silently drops the rest, and falls back to the view's ordering attribute.

Problem 2: sort cost and unstable pages

Every allowed field is a sort the database must serve. On a large table, sorting by an unindexed column means reading and sorting the whole filtered set before returning one page. Keep ordering_fields to columns you index, ideally in combination with the most common filters.
Ties make pagination unstable. OrderingFilter calls order_by(*terms), which replaces any earlier ordering, including a tiebreaker you put in get_queryset(). Thousands of jobs share salary_max = 120000, and the database may return tied rows in a different order on each query, so page 2 repeats a job from page 1 and skips another. Append a unique key in a subclass.

Problem 3: client-written regular expressions

The $ prefix in search_fields maps to Django's iregex lookup. The pattern is the client's search term, evaluated by the database against every candidate row. Patterns with nested quantifiers can backtrack catastrophically; DRF's own docs warn that this can become a denial of service. For untrusted clients use:

^ (istartswith) for codes and names;
= (iexact) for exact identifiers;
@ (full-text search) on PostgreSQL with django.contrib.postgres, which matches words rather than executing patterns.

Problem 4: the cost of ordinary search

The default lookup is icontains, a case-insensitive substring match. A leading-wildcard match generally cannot use an ordinary B-tree index, so searching a long description column scans every row that survives the other filters.
DRF ANDs every term and ORs every field per term, so the WHERE clause grows with terms times fields. There is no built-in limit on the number of terms; override get_search_terms() to cap them.
A to-many path such as skills__name adds an Exists() subquery for de-duplication; keep such paths deliberate.

Symptom	Cause	Fix
500 on `?sort=typo`	`FieldError` from raw `order_by()`	`OrderingFilter` with `ordering_fields`
hidden values inferred	sort by unserialized field	explicit whitelist of public fields
duplicates across pages	ties in the sort key	append `-pk` as tiebreaker
database CPU spikes	`$` regex search	remove `$`, use `^`, `=` or `@`
slow searches	`icontains` on long text	narrower `search_fields`, full-text, term cap

A hardening checklist

Replace raw order_by() with OrderingFilter and an explicit ordering_fields list.
Index every orderable column you allow, together with the filters it is combined with.
Guarantee a unique tiebreaker after the client's ordering.
Remove $ from search_fields; prefer ^, = or @.
Cap the number of search terms and keep search_fields short.
Keep a maximum page size on the paginator so no request reads an unbounded page.

## Problem 1: a raw order_by() is an open query surface A hand-rolled view such as `Job.objects.order_by(self.request.query_params.get('sort', '-posted_at'))` looks harmless because Django escapes identifiers and this is not SQL injection in the classic sense. The problems are elsewhere: - **Any field path is accepted.** Django's `order_by()` follows `__` across relations, so `?sort=company__owner__password` sorts by the password hash of each company's owner. Nothing is serialized, but the order itself is an **oracle**: by comparing target rows with rows of known value, a client narrows the hidden value. - **Random ordering.** `order_by('?')` asks the database for a random order, an expensive sort that the client can request on every call. - **Errors become 500s.** Django's `add_ordering()` validates names eagerly; an unknown name raises `FieldError` ('Cannot resolve keyword ... into field') inside the view. That is not a DRF `APIException`, so the client sees a server error and your error tracking fills with noise. `OrderingFilter` fixes all three: it keeps only terms whose names appear in `ordering_fields`, silently drops the rest, and falls back to the view's `ordering` attribute. ## Problem 2: sort cost and unstable pages - **Every allowed field is a sort the database must serve.** On a large table, sorting by an unindexed column means reading and sorting the whole filtered set before returning one page. Keep `ordering_fields` to columns you index, ideally in combination with the most common filters. - **Ties make pagination unstable.** `OrderingFilter` calls `order_by(*terms)`, which **replaces** any earlier ordering, including a tiebreaker you put in `get_queryset()`. Thousands of jobs share `salary_max = 120000`, and the database may return tied rows in a different order on each query, so page 2 repeats a job from page 1 and skips another. Append a unique key in a subclass. ## Problem 3: client-written regular expressions The `$` prefix in `search_fields` maps to Django's `iregex` lookup. The pattern is the client's search term, evaluated by the database against every candidate row. Patterns with nested quantifiers can backtrack catastrophically; DRF's own docs warn that this can become a denial of service. For untrusted clients use: - `^` (`istartswith`) for codes and names; - `=` (`iexact`) for exact identifiers; - `@` (full-text `search`) on PostgreSQL with `django.contrib.postgres`, which matches words rather than executing patterns. ## Problem 4: the cost of ordinary search - The default lookup is `icontains`, a case-insensitive substring match. A leading-wildcard match generally cannot use an ordinary B-tree index, so searching a long `description` column scans every row that survives the other filters. - DRF ANDs every term and ORs every field per term, so the WHERE clause grows with terms times fields. There is no built-in limit on the number of terms; override `get_search_terms()` to cap them. - A to-many path such as `skills__name` adds an `Exists()` subquery for de-duplication; keep such paths deliberate. | Symptom | Cause | Fix | |---|---|---| | 500 on `?sort=typo` | `FieldError` from raw `order_by()` | `OrderingFilter` with `ordering_fields` | | hidden values inferred | sort by unserialized field | explicit whitelist of public fields | | duplicates across pages | ties in the sort key | append `-pk` as tiebreaker | | database CPU spikes | `$` regex search | remove `$`, use `^`, `=` or `@` | | slow searches | `icontains` on long text | narrower `search_fields`, full-text, term cap | ## A hardening checklist 1. Replace raw `order_by()` with `OrderingFilter` and an explicit `ordering_fields` list. 2. Index every orderable column you allow, together with the filters it is combined with. 3. Guarantee a unique tiebreaker after the client's ordering. 4. Remove `$` from `search_fields`; prefer `^`, `=` or `@`. 5. Cap the number of search terms and keep `search_fields` short. 6. Keep a maximum page size on the paginator so no request reads an unbounded page.

Why not fix the duplicate-rows problem by adding order_by('-posted_at', '-pk') in get_queryset()?
Because `OrderingFilter` calls `queryset.order_by(*terms)` whenever the client sends a valid ordering, and `order_by()` replaces the existing ordering. The tiebreaker survives only when the client sends nothing. Appending it inside the filter's `get_ordering()` keeps it after every client choice.
Is Django's order_by() vulnerable to SQL injection through a field name?
Not in the classic sense: `order_by()` resolves the string against model fields, relations and annotations and raises `FieldError` for anything else, so arbitrary SQL cannot be spliced in. The risks are that valid paths reach hidden or related columns, that `?` requests a random sort, and that errors surface as 500s.

saying these in an interview costs you the question

Django escapes the field name, so passing ?sort= to order_by() is safe
An invalid ordering name makes Django fall back to the default order
Putting a tiebreaker in get_queryset() survives OrderingFilter's order_by()
The $ prefix is safe because the database sandboxes regular expressions
Duplicates across pages are fixed by calling distinct() on the queryset

A DRF job-listings endpoint over millions of rows passes ?sort= straight to order_by() and uses SearchFilter with '$description'; what breaks in production and how do you harden it?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Problem 1: a raw order_by() is an open query surface

Problem 2: sort cost and unstable pages

Problem 3: client-written regular expressions

Problem 4: the cost of ordinary search

A hardening checklist

▾common follow-ups2 questions

▾red flags5 flags

Problem 1: a raw order_by() is an open query surface

Problem 2: sort cost and unstable pages

Problem 3: client-written regular expressions

Problem 4: the cost of ordinary search

A hardening checklist

answer

go deeper