T09 · Insecure Skill Coding Practices
Error
- Location
- SKILL.md:109
- Finding
- SQL Injection Through Direct Interpolation of User-Controlled Values<![CDATA[ ## Vulnerability Details **File Location**: `SKILL.md`, lines 109–129 **Vulnerability Type**: SQL injection **Risk Level**: High ### Vulnerable Code ```python conditions = [f"(order_title LIKE '%{k}%' OR order_desc LIKE '%{k}%')" for k in keyword.split()] keyword_filter = f"AND ({' OR '.join(conditions)})" ``` ```python sql = f""" SELECT {time_select}, COUNT(DISTINCT user_id) AS 用户, COUNT(CASE WHEN order_state IN ('CREATED', 'PAY_CANCEL', 'PAY_FAILED', 'PAY_WAIT', 'ORDER_CLOSED') THEN 1 END) AS 未支付订单, COUNT(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN 1 END) AS 退款订单, ROUND(SUM(CASE WHEN order_state IN ('PAY_SUCCESS', 'ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN pay_amount ELSE 0 END) / 100, 0) as 支付金额, FORMAT(SUM(CASE WHEN order_state IN ('ORDER_REFUND_ALL', 'ORDER_REFUND_PART') THEN refunded_amount ELSE 0 END) / 100, 0) AS 退款金额, COUNT(DISTINCT user_id) as 用户数 FROM juss_dw.app_j_order FORCE INDEX (idx_order_title_desc) WHERE create_time >= '{start_time}' AND create_time < '{end_time}' {keyword_filter} GROUP BY {group_by} ORDER BY {order_by}; """ ``` ### Technical Analysis The Skill inserts `keyword`, `start_time`, and `end_time` values directly into an SQL statement through Python f-strings. No parameterized query, escaping mechanism, or strict input validation is applied. The keyword is especially dangerous because every token is placed between SQL string delimiters: ```python f"(order_title LIKE '%{k}%' OR order_desc LIKE '%{k}%')" ``` A token containing a quote can terminate the intended string literal and introduce SQL operators, expressions, or comments. The start and end times are similarly vulnerable because they are embedded directly between single quotes. Restricting the database account to read-only access reduces the potential for database modification, but it does not prevent attackers from changing query semantics, bypassing filters, accessing additional rows available ...[truncated 1984 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Use MySQL parameter placeholders for every user-controlled value: ```python keyword_conditions = [] params = [start_time, end_time] for keyword_item in keywords: escaped_item = ( keyword_item .replace("\\", "\\\\") .replace("%", "\\%") .replace("_", "\\_") ) keyword_conditions.append( "(order_title LIKE %s ESCAPE '\\\\' OR order_desc LIKE %s ESCAPE '\\\\')" ) pattern = f"%{escaped_item}%" params.extend([pattern, pattern]) keyword_filter = "" if keyword_conditions: keyword_filter = "AND (" + " OR ".join(keyword_conditions) + ")" sql = f""" SELECT ... FROM juss_dw.app_j_order FORCE INDEX (idx_order_title_desc) WHERE create_time >= %s AND create_time < %s {keyword_filter} GROUP BY {group_by} ORDER BY {order_by} """ df = pd.read_sql(sql, conn, params=params) ``` 2. Parse dates with `datetime.strptime` or an equivalent strict parser and reject values that do not match the accepted formats. 3. Preserve a strict allowlist mapping for `DATE`, `HOUR`, and `MONTH`; never insert arbitrary user-provided identifier or expression text. 4. Define the keyword delimiter explicitly and impose limits on keyword count and length. 5. Retain the read-only database account and restrict it to only the required table and columns. 6. Configure query timeouts and row/resource limits to reduce denial-of-service exposure. 7. Avoid returning raw database error messages to users because they may disclose schema or connection details. 8. Add automated tests using quotes, comment markers, wildcard characters, malformed dates, and oversized inputs. ]]>