Definition
- Filtering – query parameters narrow the result set, e.g.
?status=ACTIVE&type=CREDIT&minAmount=1000. - Sorting – a
sortparameter carries a field and direction, e.g.?sort=createdAt,desc; multiple sort keys are supported. - Whitelist – a fixed map from public API field names to safe internal properties; anything not in the map is rejected with 400.
- SQL injection – column names cannot be bound as query parameters, so concatenating client-supplied names into SQL opens injection holes.
- Schema leakage – accepting arbitrary field names lets attackers probe internal columns like
internal_risk_score. - Performance – sorting or filtering on unindexed columns forces full table scans.
Imagine Rajesh goes to National Bank of India and says "show me only completed transactions, newest first." The bank is happy to filter and sort by the fields on the official request form: status and date are allowed boxes. But if Rajesh could write any column name he liked, he might scribble an internal field like the bank's risk-score column, or inject a trick phrase that confuses the system, or sort by a field so obscure the clerk has to read every file in the vault. The bank prints a fixed form with only the allowed boxes. Whitelisting is that fixed form.
Understanding the Concepts
How do you design filtering in a REST API?
Filtering is expressed as query parameters where each parameter narrows the result. Common patterns include exact match (?status=ACTIVE), range (?minAmount=1000&maxAmount=50000), and prefix search (?name=Raj*). Each parameter maps to a predicate in the query.
package com.bank.controller;
import org.springframework.web.bind.annotation.*;
import java.math.BigDecimal;
import java.util.List;
@RestController
@RequestMapping("/api/v1/accounts/{accountId}/transactions")
public class TransactionController {
@GetMapping
public List<TransactionResponse> list(
@PathVariable String accountId,
@RequestParam(required = false) String status,
@RequestParam(required = false) String type,
@RequestParam(required = false) BigDecimal minAmount,
@RequestParam(required = false) BigDecimal maxAmount) {
return transactionService.filter(accountId, status, type, minAmount, maxAmount);
}
}
// Example: GET /api/v1/accounts/SAV2001/transactions?status=COMPLETED&minAmount=10000
// → Returns completed transactions with amount >= 10000
How do you design sorting in a REST API?
Sorting uses a sort parameter carrying a field and direction, like ?sort=createdAt,desc. APIs often support multiple sort keys: ?sort=createdAt,desc&sort=amount,asc. The server parses each sort token, validates the field, and builds a Sort object.
// GET /api/v1/accounts/SAV2001/transactions?sort=createdAt,desc&sort=amount,asc
// → Sorted by createdAt descending, then amount ascending.
Why must you whitelist filter and sort fields?
The field names a client sends must never be placed directly into SQL. There are three concrete reasons.
First, SQL injection: column names cannot be bound as query parameters in prepared statements, so if you concatenate a client-supplied field into the query, an attacker can inject SQL.
Second, schema leakage: accepting arbitrary field names lets an attacker probe your internal schema by testing which sort fields work and which fail.
Third, performance: sorting by an unindexed column on a large table forces a full table scan and sort, potentially bringing down the database.
package com.bank.service;
import java.util.Map;
public class SortWhitelist {
// Whitelist: public API name → safe internal column.
private static final Map<String, String> SORTABLE = Map.of(
"createdAt", "created_at",
"amount", "amount",
"status", "status");
public static String resolve(String requestedField) {
String column = SORTABLE.get(requestedField);
if (column == null) {
throw new BadRequestException("Unsortable field: " + requestedField);
}
return column;
}
}
How do you implement safe sorting in Spring?
Validate each requested field against the whitelist, then build a Sort from the resolved column names. Never pass raw client input to Sort.by().
package com.bank.service;
import org.springframework.data.domain.Sort;
import java.util.ArrayList;
import java.util.List;
public class SafeSortBuilder {
public static Sort build(List<String> sortParams) {
List<Sort.Order> orders = new ArrayList<>();
for (String token : sortParams) {
String[] parts = token.split(",");
String property = SortWhitelist.resolve(parts[0]);
Sort.Direction dir = parts.length > 1 && "desc".equalsIgnoreCase(parts[1])
? Sort.Direction.DESC : Sort.Direction.ASC;
orders.add(new Sort.Order(dir, property));
}
return Sort.by(orders);
}
}
How do you implement safe filtering with JPA Specifications?
The Specification API lets you compose type-safe predicates from validated inputs. Field names come from the entity metamodel, not raw strings, which prevents injection.
package com.bank.repository;
import org.springframework.data.jpa.domain.Specification;
import java.math.BigDecimal;
public class TransactionSpecs {
public static Specification<Transaction> hasStatus(String status) {
return (root, query, cb) -> cb.equal(root.get("status"), status);
}
public static Specification<Transaction> minAmount(BigDecimal min) {
return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get("amount"), min);
}
public static Specification<Transaction> forAccount(String accountId) {
return (root, query, cb) -> cb.equal(root.get("accountId"), accountId);
}
}
What does SQL injection through sort fields look like?
If you concatenate a client-supplied column name into SQL, the attacker can inject arbitrary SQL. Even parameterised queries do not protect column names.
// VULNERABLE: client-supplied field concatenated into ORDER BY.
String sql = "SELECT * FROM transactions ORDER BY " + clientSortField;
// Attacker sends: amount; DROP TABLE accounts; --
// Resulting SQL: SELECT * FROM transactions ORDER BY amount; DROP TABLE accounts; --
// SAFE: whitelist lookup prevents any injection.
String column = SortWhitelist.resolve(clientSortField); // Throws on unknown field
String sql = "SELECT * FROM transactions ORDER BY " + column; // Only safe columns
What does schema probing look like?
An attacker tests sort fields one by one. If ?sort=internal_risk_score succeeds, the attacker knows that column exists. If it fails, they try another. The whitelist stops this by rejecting everything not on the approved list.
// Probing attack:
// GET /transactions?sort=internal_risk_score → 200? Column exists!
// GET /transactions?sort=ssn → 200? Column exists!
// With whitelist: both return 400, revealing nothing.
What are common mistakes with filtering and sorting?
Not validating sort fields at all. Using raw client input in Sort.by(). Allowing arbitrary LIKE patterns that force full scans. Not capping the number of sort keys.
// Wrong: raw client input passed to Sort.by().
Sort sort = Sort.by(clientSortField); // Attacker controls the field name!
// Correct: whitelist lookup first.
Sort sort = SafeSortBuilder.build(sortParams); // Only whitelisted fields
What should a senior engineer know?
A senior engineer knows that the whitelist decouples the public API contract from the physical schema. The API exposes createdAt while the column is created_at, and renaming the column changes only the mapping. They cap the number of sort keys and filter combinations, restrict text filters to avoid arbitrary LIKE patterns, and use the Specification API for type-safe query composition.
Visual Overview
┌──────────────────────────────────────────────────────────┐ │ Filtering & Sorting Safety Flow │ ├──────────────────────────────────────────────────────────┤ │ │ │ Client request: │ │ GET /transactions?status=ACTIVE&sort=createdAt,desc │ │ │ │ │ ▼ │ │ Validate filter values (type, range, format) │ │ │ │ │ ▼ │ │ Validate sort field against whitelist: │ │ ┌──────────────────────────────────────────────┐ │ │ │ Whitelist: │ │ │ │ "createdAt" → "created_at" ✓ │ │ │ │ "amount" → "amount" ✓ │ │ │ │ "status" → "status" ✓ │ │ │ │ anything else → 400 Bad Request ✗ │ │ │ └──────────────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ Build safe query: │ │ SELECT * FROM transactions │ │ WHERE status = ? (parameterised value) │ │ ORDER BY created_at DESC (whitelisted column) │ │ LIMIT 20 │ │ │ │ │ ▼ │ │ Return paginated results │ └──────────────────────────────────────────────────────────┘
Key Takeaway
Filtering and sorting in a REST API are expressed as query parameters: each filter parameter narrows the result, and a sort parameter carries a field and direction. The critical rule is that the field names a client sends must never go directly into SQL. Instead, every requested filter and sort field is validated against a server-side whitelist: a fixed map from the public API name to a safe entity property, and anything not in the map is rejected with 400.
There are three concrete reasons to whitelist. First, SQL injection: column names cannot be bound as query parameters in prepared statements, so concatenating client-supplied names opens injection holes. Second, schema leakage: accepting arbitrary field names lets attackers probe internal columns by seeing which succeed and which fail. Third, performance: sorting on an unindexed column forces a full table scan that can bring down the database.
At National Bank of India, all three risks were real on the transaction listing endpoint. An early version concatenated the client's sort field into the ORDER BY clause, and a security test injected SQL through it. A curious client mapped the internal schema by probing sort field names. And someone sorted a ten-million-row table by an unindexed field, slowing the whole database. The fix was a whitelist that maps public names to safe properties, type-safe Specifications for filters, and caps on both sort keys and page size.
The deeper benefit is that the whitelist decouples the public contract from the physical schema. The API exposes createdAt while the column is created_at, and renaming the column changes only the mapping. In a bank, where tables are enormous and data is sensitive, treating filter and sort fields as untrusted input and admitting only an approved set is not optional.
Remember one sentence: never let a client-supplied field name reach your SQL; validate every filter and sort field against a whitelist to prevent injection, schema leakage, and unindexed scans.
---