Dynamic SQL Identifiers, Sorting/Filtering APIs, Allow-Lists, and Query-Shape Abuse

Learning outcomes

1. Injection is only one way a dynamic query API can be abused

A public ServiceHub endpoint may let clients choose sort order, filters, page size, optional related data, and search patterns. Even if every value is parameterized, an attacker can still request a pathological query shape: a non-indexed sort over millions of rows, dozens of includes, a huge IN list, an unbounded page size, or a wildcard search that defeats useful indexes.

Query-shape abuse is user control over the structure or cost of a query. It can become a confidentiality problem (sorting/filtering on hidden fields), an availability problem (CPU/memory/locks/round trips), or an injection problem if the application inserts raw SQL fragments.

2. Prefer typed expression maps for sortable fields

The safest dynamic identifier is often no identifier at all. Map a small public vocabulary to compiled C# expression trees and let EF translate the selected expression.

public enum WorkOrderSort{    OpenedUtc,    Priority,    WorkOrderNumber}public static IQueryable<WorkOrder> ApplySort(    IQueryable<WorkOrder> query,    WorkOrderSort sort,    bool descending)    => (sort, descending) switch    {        (WorkOrderSort.OpenedUtc, false) => query.OrderBy(w => w.OpenedUtc),        (WorkOrderSort.OpenedUtc, true)  => query.OrderByDescending(w => w.OpenedUtc),        (WorkOrderSort.Priority, false)  => query.OrderBy(w => w.Priority),        (WorkOrderSort.Priority, true)   => query.OrderByDescending(w => w.Priority),        (WorkOrderSort.WorkOrderNumber, false) => query.OrderBy(w => w.WorkOrderNumber),        _ => query.OrderByDescending(w => w.WorkOrderNumber)    };

The client controls only an enum value. Column mapping, quoting, collation, provider syntax, and tenant filters remain EF/provider concerns. Hidden fields such as TenantId, audit metadata, or internal cost estimates simply never appear in the public sort vocabulary.

3. Build filtering from explicit operators and limits

public sealed record WorkOrderQuery(    int? MinimumPriority,    DateTimeOffset? OpenedAfter,    string? Search,    WorkOrderSort Sort,    bool Descending,    int Page,    int PageSize);public static IQueryable<WorkOrder> ApplyQuery(    IQueryable<WorkOrder> query,    WorkOrderQuery request){    if (request.MinimumPriority is { } priority)        query = query.Where(w => w.Priority >= priority);    if (request.OpenedAfter is { } openedAfter)        query = query.Where(w => w.OpenedUtc >= openedAfter);    if (!string.IsNullOrWhiteSpace(request.Search))    {        var term = request.Search.Trim();        if (term.Length > 100) throw new ArgumentOutOfRangeException(nameof(request.Search));        query = query.Where(w => w.Summary.Contains(term));    }    query = ApplySort(query, request.Sort, request.Descending);    var pageSize = Math.Clamp(request.PageSize, 1, 100);    var page = Math.Max(request.Page, 1);    return query.Skip((page - 1) * pageSize).Take(pageSize);}

This is not a universal tuning prescription: the 100-character/100-row limits are example API policy for the lab. Production limits belong to capacity, product, and threat-model decisions and should be load-tested.

4. Deliberate failure: raw ORDER BY from user input

var sort = request.Query["sort"].ToString();var sql = $"SELECT * FROM work_orders ORDER BY {sort}";return await db.WorkOrders    .FromSqlRaw(sql)    .Take(100)    .ToListAsync(cancellationToken);

Because the sort fragment is inserted into command text, an attacker can supply SQL syntax or select a sensitive/unindexed expression. Sanitizing by deleting a semicolon is not a security model; SQL grammars offer many alternate tokens/comments/functions. The repair is a typed sort map. If raw identifiers are genuinely unavoidable, choose from a fixed dictionary of reviewed SQL fragments.

var allowedOrderBy = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase){    ["opened"] = "opened_utc",    ["priority"] = "priority",    ["number"] = "work_order_number"};if (!allowedOrderBy.TryGetValue(request.SortKey, out var column))    throw new ArgumentException("Unsupported sort field.", nameof(request.SortKey));var sql = $"SELECT * FROM work_orders ORDER BY "{column}" LIMIT 100";var rows = await db.WorkOrders.FromSqlRaw(sql).AsNoTracking().ToListAsync();

Here the inserted fragment can only be one of three developer-authored literals. Provider quoting differs, so prefer the typed LINQ path when portability matters.

5. Bound includes and relationship expansion

A GraphQL-like or generic “include any path” endpoint can create cartesian explosion, hidden N+1 behavior, or unauthorized data exposure. Expose reviewed shapes such as summary, withTechnician, or withRecentNotes instead of arbitrary navigation names.

6. Cancellation and command timeout are backstops, not primary limits

Propagate request cancellation to EF and configure provider-appropriate command timeouts for bounded failure. A timeout does not make an abusive query cheap; the database may already have consumed substantial CPU, memory, I/O, or locks. Reject pathological shapes before execution and monitor timeout rate as a security/availability signal.

public sealed record WorkOrderListItem(    int Id, string WorkOrderNumber, int Priority,    DateTimeOffset OpenedUtc, string Summary);var query = ApplyQuery(    db.WorkOrders.AsNoTracking(),    request);var rows = await query    .Select(w => new WorkOrderListItem(        w.Id, w.WorkOrderNumber, w.Priority, w.OpenedUtc, w.Summary))    .ToListAsync(httpContext.RequestAborted);

7. Observe shape, count, plan and authorization separately

ToQueryString() shows the translated SQL shape. A command interceptor can count round trips and record duration. A database plan shows whether indexes are used. Authorization tests prove the public vocabulary cannot request hidden tenant/audit fields. No one signal substitutes for the others.

request: sort=priority, pageSize=100translated: ORDER BY priority ... LIMIT @pcommands: 1rows: <= 100tenant predicate: presentsensitive columns selected: noSQLite plan: reviewed for lab indexproduction provider plan: required before portability/performance claim

8. Mandatory lab: attack the query surface

1. Expose the typed WorkOrderQuery contract and seed at least 5,000 disposable SQLite rows with skewed priorities.
2. Verify all allowed sort values produce one bounded query and no hidden fields.
3. Send invalid sort keys/operators, a 10,000-character search term, negative page numbers, and a very large page size; assert they are rejected or clamped by explicit policy.
4. Run the deliberately unsafe raw ORDER BY version only against the disposable database and capture its command text.
5. Replace it with the typed/allow-listed implementation.
6. Use cancellation and a conservative lab timeout to test bounded failure, then inspect plan/command-count evidence.
7. Delete the disposable database and retain only sanitized diagnostics.

9. Production judgment and bridge

A secure data API constrains both values and shape. Typed expressions, narrow DTOs, bounded cardinality, allow-listed capabilities, cancellation, rate/authorization controls, and provider plan evidence work together. Lesson 3 moves to the next security boundary: the credentials and connection configuration that give the application database authority in the first place.

Authoritative references

• SQL queries in EF Core — dynamic SQL/identifier limitations and raw SQL safety guidance
• OWASP SQL Injection Prevention Cheat Sheet — allow-list validation and parameterization guidance
• EF Core performance — query shape, result-size and efficient-querying considerations
• Global query filters — tenant filters and deliberate bypass semantics
• EF Core interceptors — command counting and diagnostics for query-shape tests
• SQLite query-plan documentation — free local plan evidence for the lab