T09 · Insecure Skill Coding Practices
- Location
references/key-rules.md:64- Finding
Unprotected execution of user-supplied SQL through EXPLAIN ANALYZE
- Content
View full analysis
Vulnerability Details
File Location:
references/key-rules.md:64-66;references/triage-playbook.md:20-23;assets/demo-prompts.md:6
Vulnerability Type: Unsafe execution of untrusted diagnostic SQL
Risk Level: MediumVulnerable Code
references/key-rules.md:64-66:markdown - performance issue with a user-provided slow SQL statement: start from `EXPLAIN ANALYZE` - performance issue without a confirmed slow SQL statement: start from the `query-metrics-history` tool results - slow SQL identified after metrics analysis: use `EXPLAIN ANALYZE`references/triage-playbook.md:20-23:markdown 3. if the user already provided the slow SQL statement, go directly to `EXPLAIN ANALYZE` 4. if no slow SQL statement is confirmed, call the `kwdb-mcp-server` `query-metrics-history` tool for the fault time window 5. decide whether the first hard bottleneck is CPU, IO, memory, or slow SQL 6. if slow SQL is primary, capture the SQL text and run `EXPLAIN ANALYZE` when possibleassets/demo-prompts.md:6:markdown - 这是一次性能问题,用户已经提供慢 SQL。请直接用 `EXPLAIN ANALYZE` 定位瓶颈,并输出通用诊断报告。The last snippet instructs the agent to use
EXPLAIN ANALYZEdirectly when a slow SQL statement has been supplied.Technical Analysis
Unlike plain
EXPLAIN,EXPLAIN ANALYZEexecutes the underlying SQL statement to collect runtime statistics. The skill directs the agent to apply it directly to user-provided SQL but does not require the statement to be parsed and verified as read-only. It also does not require explicit execution approval, reject multiple statements, restrict the target to a test environment or read replica, or impose statement, lock, and resource limits.Consequently, an incident submitter can present a mutating, locking, or computationally expensive statement as “slow SQL.” If an agent or operator follows the skill against a live database, the statement executes with the privileges of the ...[truncated 1771 chars]
- Remediation
View remediation
Remediation Suggestions
- Make plain
EXPLAINthe default diagnostic operation because it does not execute the underlying statement. - Permit
EXPLAIN ANALYZEonly after parsing and verifying that the input is exactly one read-only statement. Reject multi-statement input and all DDL, DML, administrative, and transaction-control operations. - Require explicit user approval immediately before runtime execution, identifying the target cluster, database, statement, and execution risks.
- Prefer a staging environment or read replica containing representative statistics and data. Do not default to a production primary.
- Use a dedicated least-privilege, read-only database account with no schema-modification, write, administrative, or role-management permissions.
- Apply strict statement and lock timeouts, resource limits, concurrency controls, and result-size limits.
- Where supported, use a read-only, rollback-only transaction and verify that the session cannot perform writes.
- Require the operator to review the plain execution plan and estimated cost before authorizing runtime analysis.
- Update the cited rule, playbook, and demonstration prompt consistently so none of them instructs the agent to execute user-provided SQL directly.
- Make plain
