T09 · Insecure Skill Coding Practices
- Location
analyze.py:110- Finding
SQL Injection Through Direct Interpolation of Database Query Parameters
- Content
View full analysis
Vulnerability Details
File Location:
analyze.py:110-125,analyze.py:152-168, andanalyze.py:179-192
Vulnerability Type: SQL injection
Risk Level: HighVulnerable Code
python def fetch_daily_data(store_id: str, from_date: str, to_date: str) -> List[Dict]: """ 获取日粒度历史数据 数据源：v_gmv_daily_by_store """ sql = f""" SELECT biz_date, weekday_name, deal_amount, order_count, customer_unit_price, deal_attach_rate, deal_discount FROM v_gmv_daily_by_store WHERE store_id = '{store_id}' AND biz_date BETWEEN '{from_date}' AND '{to_date}' ORDER BY biz_date """ result = query_database(sql)python def fetch_weekly_data(store_id: str, from_date: str, to_date: str) -> List[Dict]: """ 获取周粒度历史数据 数据源：v_gmv_weekly_by_store """ sql = f""" SELECT year_week, week_start_date, week_end_date, deal_amount, order_count, customer_unit_price, deal_attach_rate FROM v_gmv_weekly_by_store WHERE store_id = '{store_id}' AND week_start_date >= '{from_date}' AND week_end_date <= '{to_date}' ORDER BY year_week """ result = query_database(sql)python def fetch_monthly_data(store_id: str, from_date: str, to_date: str) -> List[Dict]: """ 获取月粒度历史数据 数据源：v_gmv_monthly_by_store """ sql = f""" SELECT year_month, deal_amount, order_count, customer_unit_price, deal_attach_rate FROM v_gmv_monthly_by_store WHERE store_id = '{store_id}' AND year_month BETWEEN '{from_date[:7]}' AND ' ...[truncated 2278 chars]- Remediation
View remediation
Remediation Suggestions
-
Replace f-string SQL construction with parameterized queries supported by the database client:
python sql = """ SELECT biz_date, weekday_name, deal_amount, order_count, customer_unit_price, deal_attach_rate, deal_discount FROM v_gmv_daily_by_store WHERE store_id = :store_id AND biz_date BETWEEN :from_date AND :to_date ORDER BY biz_date """ result = query_database( sql, { "store_id": store_id, "from_date": from_date, "to_date": to_date, }, )Adapt placeholder syntax to the actual database driver. Parameterization must occur in the database client rather than through manual quoting or string replacement.
-
Validate
store_idagainst the application's canonical identifier format and reject unexpected characters or lengths. -
Parse every date parameter with
datetime.strptime(value, "%Y-%m-%d")before it reaches query construction, including calls made directly to exported fetch functions. -
Enforce server-side authorization so the authenticated caller can query only permitted store IDs. Input validation alone is not an authorization control.
-
Configure the database identity as read-only and grant access only to the required views.
-
Make the fetch functions internal if direct external invocation is unnecessary, and add tests proving that quote characters and SQL metacharacters cannot alter query structure.
-