ORDER BY Column Allowlist

Challenge

The problem

User-supplied ORDER BY columns are a classic SQL injection sink. Bind parameters (?) protect values in a WHERE clause, but they cannot parameterize an identifier like a column or table name. So developers often paste the user's requested sort column straight into the query string: ... ORDER BY " + col. An attacker sends col = "id; DROP TABLE users;--" or a blind-injection boolean payload, and the database happily executes it.

The asset here is the query itself: the sort column is attacker-controlled text and the threat is arbitrary SQL execution. The insecure assumption is "the column name is harmless because it's just a field name."

The task

Implement in_sort_allowlist(const char *col). Return 1 only if col exactly matches one of a fixed, hard-coded set of sortable columns: id, name, email, created_at, updated_at. Return 0 for everything else. This is the safe pattern: deny by default, and only map user input onto a known-good identifier you control. The caller uses the boolean to decide whether the requested column is allowed into the query at all.

Edge cases

• NULL must return 0, never dereference it.
• The empty string is not a valid column.
• Matching is exact and case-sensitive: "ID", "id_", "name " (trailing space), and any injection payload built on a valid prefix must all be rejected.

Input format

A single argument: const char *col, the user-requested sort column. It may be NULL, empty, a valid column name, or a malicious string.

Output format

Return an int: 1 if col is exactly one of the five allowlisted columns, otherwise 0.

Constraints

C11. The harness provides all needed includes (string.h etc.). Do not read files, network, or any live target — operate only on the fixed allowlist baked into your function. Never dereference a NULL pointer. Comparisons must be exact (no prefix/substring/case-insensitive matching).

Starter code

int in_sort_allowlist(const char *col){
    /* TODO: only accept columns from the fixed allowlist; reject everything else. */
    (void)col;
    return -1;
}

Common mistakes

Using strncmp or a prefix check so "id_evil" or "id; ..." slips through; case-insensitive comparison; forgetting the NULL check and crashing; returning a non-boolean (e.g. the strcmp result) so callers see a nonzero for a mismatch; blocklisting bad keywords instead of allowlisting good columns (blocklists are always incomplete).

Edge cases to handle

NULL pointer (return 0, no deref); empty string; exact-but-wrong case ("ID"); valid column with trailing/leading whitespace ("name "); a valid prefix extended into an injection payload ("id; DROP TABLE users;--"); a column that merely starts like an allowed one ("id_").

Background lessons

• SQL basics: querying a relational database