Summary
Craft Commerce contains a SQL injection flaw in the purchasables table endpoint where the user-controlled sort parameter is used to build an ORDER BY clause without proper validation.
Under certain authenticated conditions, an attacker can inject arbitrary SQL fragments into the ORDER BY expression, potentially altering query logic, exposing sensitive data, or impacting database integrity.
Affected context
This vulnerability exists in Craft Commerce installations that expose the purchasables table endpoint and allow authenticated users to control the sort parameter.
The parameter is split by |, and the first segment, interpreted as a column name, is passed directly as an array key to Yii2's orderBy() query builder.
Because Yii2 does not escape array keys in this context, unvalidated sort values can influence the raw ORDER BY clause in generated SQL.
Exploit preconditions
- Attack surface: Web application endpoint providing the purchasables table (typically via the Craft CMS control panel or API-like endpoints).
- Required access: Authenticated access with permission to invoke the purchasables table endpoint and supply a
sortparameter. - Input vector: The
sortquery parameter, where the first segment before|is treated as the sort column key. - Trigger point: Execution of the database query that applies
orderBy()using the attacker-controlled array key. - Why exploitation is possible: The application treats a user-controlled string as a trusted column identifier, passes it as an array key to
orderBy(), and Yii2's query builder does not escape array keys, allowing crafted values to inject arbitrary SQL into theORDER BYclause.
Why this happens
The core issue is an unvalidated dynamic query construction pattern that directly maps to CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection').
The code assumes that the first part of the sort parameter is a valid column name and uses it as an array key in a call to orderBy() without any whitelist or canonicalization step.
In Yii2, string keys in associative arrays passed to orderBy() are inserted into the SQL ORDER BY clause verbatim, so attacker-controlled values can break out of the expected identifier context and introduce arbitrary SQL fragments.
Conceptually, this is an example of trusting client-controlled field names in query builders, where the query builder only protects value parameters, not structural elements such as column names or SQL expressions.
Potential impact
- Attackers could manipulate result ordering to infer application behavior or expose patterns that aid further attacks.
- Crafted
ORDER BYexpressions could be used to leak information about database structure (e.g., via error messages or timing differences). - In some database configurations, injected expressions could impact performance, leading to resource exhaustion on the database server.
- Under certain database and permission setups, injected SQL in
ORDER BYmay be chained with other weaknesses to escalate into broader data exposure or logic manipulation.
General mitigation guidance
Effective mitigation focuses on not treating user input as a trusted part of SQL structure, especially for column names and expressions:
- Introduce a strict whitelist of allowed sort fields, mapping short, user-facing tokens to fixed, hard-coded column names or expressions on the server.
- Avoid directly passing user input as array keys or raw SQL to query builders; instead, map input to safe constants and pass only these constants into
orderBy()or equivalent methods. - Encapsulate sort and filter handling in a dedicated utility or service that enforces allowed fields and directions, and reuse this across endpoints.
- Apply the principle of least privilege at the database level so that even if similar bugs occur, the application account has minimal ability to alter schema or access unrelated data.
- Consider adding centralized input validation for generic parameters like
sort,order, orfield, making it harder for unvalidated values to reach query construction.
How teams detect issues like this
Detection usually combines static analysis, code review, and runtime monitoring:
- Static analysis / SAST: Configure rules to flag uses of query builder methods (e.g.,
orderBy(),groupBy(),join()) where array keys or raw expressions are derived from request parameters or other untrusted sources. - Code review: During manual review, look for:
- Direct use of request/query/body parameters when building
ORDER BY,GROUP BY, orJOINclauses. - Lack of mapping or whitelisting logic for sortable fields.
- Use of dynamic strings as array keys in database query-building code.
- Security policies: Enforce architectural guidelines that all sortable/filterable fields are defined in a fixed configuration list, and block code changes that bypass this pattern (e.g., via CI checks).
- Runtime monitoring: Use application and database logging to identify anomalous
ORDER BYpatterns, such as injected functions or unexpected expressions, and feed these into alerting or WAF rules where appropriate. - Automated code review or linters can be configured to detect untrusted input flowing into query-structure parameters in Yii2 and similar frameworks.

