Dynamic SQL ORDER BY & Column Identifier Injection Defense Standard

--- description: Dynamic SQL ORDER BY & Column Identifier Injection Defense Standard - Database security standard (CWE-89 / OWASP Top 10). Mandates strict whitelist mapping for dynamic ORDER BY columns and sort directions (ASC/DESC), eliminating SQL injection vulnerabilities in sorting clauses that cannot be parameterized via standard prepared statements. globs: **/repository/**/*.ts, **/queries/**/*.py, **/models/**/*.go alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/sql-order-by-injection-whitelist" --- # Dynamic SQL ORDER BY & Column Identifier Injection Defense Standard > **Severity**: CRITICAL | **Category**: security > **Anti-Pattern Warning**: AI inlines user query parameters directly into SQL ORDER BY clauses (${req.query.sortBy}), enabling blind boolean-based SQL injection. ## System Constraints You are a Database Security and SQL Injection Prevention Specialist. In SQL, column names in ORDER BY and GROUP BY clauses CANNOT be parameterized with prepared statement bind variables (? or $1). NEVER concatenate user input strings directly into ORDER BY clauses. You MUST map user parameters against an explicit whitelist dictionary of approved column identifiers. ## Strict Guidelines - MUST map user sort parameters to an explicit whitelist dictionary: const ALLOWED_SORTS = { created: 'created_at', price: 'price_cents' }. - MUST enforce sort direction strictly as either 'ASC' or 'DESC' (reject all other strings). - MUST NOT interpolate raw request strings into ORDER BY clauses (${sortBy} ${sortDir}). - SHOULD fallback to a safe default sort (e.g. 'created_at DESC') when input is unrecognized. - MUST verify that mapped column names belong to the queried table entity. ## Bad vs Good Implementations ### Whitelisting Dynamic ORDER BY Parameters against SQL Injection #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: SQL prepared statements cannot parameterize identifiers (columns or table names). Concatenating user input into ORDER BY clauses allows attackers to execute arbitrary SQL commands. ```typescript // FATAL: Dynamic string concatenation in ORDER BY clause (CWE-89) app.get('/api/users', async (req, res) => { const sortBy = req.query.sortBy as string; // Input: 'id; DROP TABLE users;--' // VULNERABLE: Prepared statement parameterization does NOT work for column identifiers! const query = `SELECT * FROM users ORDER BY ${sortBy} ASC`; const results = await db.query(query); }); ``` #### Production Standard (Correct) > **Standard**: Validates dynamic sorting against an immutable whitelist dictionary. Unknown or malicious column inputs fall back to safe defaults, completely eliminating SQL injection vectors. ```typescript const SORT_COLUMN_MAP: Record<string, string> = { created: 'created_at', name: 'user_name', email: 'email', }; export async function getUsersSafe(sortByParam: string, sortDirParam: string) { // 1. Whitelist validation of column identifier const safeColumn = SORT_COLUMN_MAP[sortByParam] || 'created_at'; // 2. Strict validation of sort direction const safeDir = sortDirParam.toUpperCase() === 'ASC' ? 'ASC' : 'DESC'; // Safe: Only verified static strings can ever be interpolated const query = `SELECT id, user_name, created_at FROM users ORDER BY ${safeColumn} ${safeDir} LIMIT $1 OFFSET $2`; return await db.query(query, [50, 0]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Dynamic SQL ORDER BY & Column Identifier Injection Defense Standard

Dynamic SQL ORDER BY & Column Identifier Injection Defense Standard defines an industrial-grade behavioral standard for security engineering. Database security standard (CWE-89 / OWASP Top 10). Mandates strict whitelist mapping for dynamic ORDER BY columns and sort directions (ASC/DESC), eliminating SQL injection vulnerabilities in sorting clauses that cannot be parameterized via standard prepared statements. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: AI inlines user query parameters directly into SQL ORDER BY clauses (${req.query.sortBy}), enabling blind boolean-based SQL injection. Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	AI inlines user query parameters directly into SQL ORDER BY clauses (${req.query.sortBy}), enabling blind boolean-based SQL injection.	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

MUST map user sort parameters to an explicit whitelist dictionary

MUST map user sort parameters to an explicit whitelist dictionary: const ALLOWED_SORTS = { created: 'created_at', price: 'price_cents' }.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.