T09 · Insecure Skill Coding Practices
Error
- Location
SKILL.md:99- Finding
SQL Injection Through Unsafely Interpolated Query Parameters
- Content
View full analysis
= '{start_time}' AND create_time < '{end_time}' {keyword_filter} {order_type_filter} GROUP BY {group_by} ORDER BY payment_amount DESC; """ ``` ### Technical Analysis Python f-string interpolation is not an SQL-escaping or parameterization mechanism. An attacker can place quote characters, SQL operators, comments, subqueries, or `UNION` clauses in any of the following values: - `start_time` - `end_time` - Individual entries derived from `keyword` - `order_type` For example, the business-area filter is assembled as: ```sql AND order_type='' ``` A value such as: ```text ' OR 1=1 -- ``` terminates the string literal, adds an always-true expression, and comments out the remainder of the query. Keyword values are similarly embedded inside two `LIKE` patterns without escaping. The dimension expressions are selected from fixed branches and are therefore allowlisted in the shown template. They are not the source of this vulnerability. ### Attack Path 1. An attacker requests a report and sup ...[truncated 1779 chars]- Remediation
View remediation
= %s AND create_time < %s {keyword_filter} {order_type_filter} GROUP BY {group_by} """ df = pd.read_sql(sql, conn, params=params) ``` 2. Keep `select_dim` and `group_by` restricted to hardcoded allowlisted constants. SQL identifiers generally cannot be supplied through value placeholders. 3. Parse the start and end values with `datetime.strptime` and reject values that do not exactly match an approved date format. 4. Accept business-area values only through the documented mapping table. Reject arbitrary database values. 5. Set maximum keyword lengths and counts to reduce abuse and denial-of-service risk. 6. Give the database account access only to the required table and columns, rather than broad schema-level read access. 7. Configure a server-side statement timeout or execution-time limit. 8. Avoid returning raw database exception messages to end users because they can assist SQL injection development. 9. Add tests containing quotes, comment markers, wildcard characters, and SQL operators to verify that all inputs remain data rather than executable SQL. ]]>