T09 · Insecure Skill Coding Practices
- Location
scripts/v2.15_prediction.py:55- Finding
SQL Query Manipulation Through an Unvalidated Table Identifier
- Content
View full analysis
List[Dict]: """获取历史数据""" conn.row_factory = sqlite3.Row cursor = conn.cursor() query = f"SELECT * FROM {lottery_type} ORDER BY issue DESC" if limit: query += f" LIMIT {limit}" cursor.execute(query) rows = cursor.fetchall() return [dict(row) for row in rows] def get_latest_issue(conn: sqlite3.Connection, lottery_type: str = "ssq") -> str: """获取最新已开奖期号""" cursor = conn.cursor() cursor.execute(f"SELECT issue FROM {lottery_type} ORDER BY issue DESC LIMIT 1") row = cursor.fetchone() return row[0] if row else None ``` The value is obtained from a command-line argument without an allowlist: ```python parser.add_argument("--lottery", type=str, default="ssq", help="彩种(ssq/dlt)") ``` ### Technical Analysis The `--lottery` argument is directly interpolated into SQL statements as a table expression. Although the help text presents `ssq` and `dlt` as the expected values, the program does not enforce that restriction. SQL parameter binding cannot normally be used for table identifiers. Consequently, identifiers must be selected from a fixed mapping or strictly validated before interpolation. In the current implementation, an attacker who controls command-line arguments can supply SQL syntax instead of a valid table name. Python's SQLite driver generally prevents execution of multiple statements in one `execute()` call, but this does not eliminate manipulation within the single permitted statement. The `limit` value does not present the same issue in the normal CLI path because the application supplies a fixed integer of `3430`. The confirmed vulnerable so ...[truncated 1254 chars]- Remediation
View remediation
str: try: return LOTTERY_TABLES[lottery_type] except KeyError: raise ValueError("Unsupported lottery type") ``` Use only the resolved value when constructing SQL: ```python table = resolve_lottery_table(lottery_type) cursor.execute(f"SELECT issue FROM {table} ORDER BY issue DESC LIMIT 1") ``` Additional hardening should include: 1. Use `argparse` choices to reject invalid CLI input early: ```python parser.add_argument("--lottery", choices=["ssq", "dlt"], default="ssq") ``` 2. Continue using parameter binding for values such as limits and filters. 3. Open the database in read-only mode when prediction does not require writes: ```python sqlite3.connect(f"file:{db_path}?mode=ro", uri=True) ``` 4. Avoid returning raw database exception details to untrusted callers. 5. Add tests proving that spaces, quotes, comments, subqueries, and unknown lottery names are rejected. ]]>
