T09 · Insecure Skill Coding Practices
- Location
analyze.py:100- Finding
SQL Injection Through Unparameterized Benchmark Query Inputs
- Content
View full analysis
Vulnerability Details
File Location:
analyze.py, lines 100-125
Vulnerability Type: SQL injection caused by direct interpolation of caller-controlled values
Risk Level: HighVulnerable Code
python if scope == ComparisonScope.CITY: where_clause = f"city_name = '{scope_code}'" elif scope == ComparisonScope.PROVINCE: where_clause = f"province_name = '{scope_code}'" elif scope == ComparisonScope.REGION: # 区域需要通过门店ID关联查询 where_clause = f"region = '{scope_code}'" else: where_clause = "1=1" # 集团全部 sql = f""" SELECT store_id, store_name, SUM(deal_amount) as total_sales, SUM(order_count) as total_orders, AVG(customer_unit_price) as avg_atv, AVG(deal_attach_rate) as avg_attach FROM v_gmv_daily_by_store WHERE {where_clause} AND biz_date BETWEEN '{from_date}' AND '{to_date}' GROUP BY store_id, store_name HAVING SUM(deal_amount) > 0 ORDER BY total_sales DESC """ result = query_database(sql)Technical Analysis
The
scope_code,from_date, andto_datevalues are incorporated directly into an SQL statement using formatted strings. No parameter binding, escaping, or strict input validation is applied before the statement is passed toquery_database.An attacker who can control any of these function arguments can introduce SQL syntax that changes the intended query. For example, a crafted
scope_codecan terminate the quoted value and inject an always-true condition or another expression. Date arguments provide additional injection points.Validation of the
scopeenumeration does not mitigate this issue because the values interpolated into the selected condition remain unconstrained.Attack Path
- The attacker gains access to an interface that invokes
analyzeorfetch_stores_performancewith caller-supplied arguments. - The attacker submits a malic ...[truncated 1129 chars]
- The attacker gains access to an interface that invokes
- Remediation
View remediation
Remediation Suggestions
- Replace string interpolation with parameterized queries supported by the database client. Bind
scope_code,from_date, andto_dateas data rather than including them in the SQL text. - Keep column selection under application control through the existing enumeration; never accept a column name directly from a caller.
- Parse dates with a strict format such as
%Y-%m-%d, reject invalid values, and verify that the start date does not exceed the end date. - Apply reasonable maximum date-range limits to prevent abusive or unexpectedly expensive queries.
- Run the query through a dedicated, read-only database identity restricted to the required view and rows wherever row-level security is available.
- Ensure the database client rejects multiple statements per execution.
- Add tests using quotation marks, SQL comments, Boolean expressions, and malformed dates to confirm that supplied values cannot alter query structure.
A parameterized design should follow this pattern:
python scope_columns = { ComparisonScope.CITY: "city_name", ComparisonScope.PROVINCE: "province_name", ComparisonScope.REGION: "region", } if scope == ComparisonScope.GROUP: where_clause = "1=1" params = [from_date, to_date] else: where_clause = f"{scope_columns[scope]} = %s" params = [scope_code, from_date, to_date] sql = f""" SELECT store_id, store_name, SUM(deal_amount) AS total_sales, SUM(order_count) AS total_orders, AVG(customer_unit_price) AS avg_atv, AVG(deal_attach_rate) AS avg_attach FROM v_gmv_daily_by_store WHERE {where_clause} AND biz_date BETWEEN %s AND %s GROUP BY store_id, store_name HAVING SUM(deal_amount) > 0 ORDER BY total_sales DESC """ result = query_database(sql, params)The exact placeholder style should be adapted to the database driver's supported parameter API.
- Replace string interpolation with parameterized queries supported by the database client. Bind