T09 · Insecure Skill Coding Practices
- Location
SKILL.md:65- Finding
Untrusted SQL May Be Executed During Query Analysis
- Content
View full analysis
Vulnerability Details
File Location:
SKILL.md, lines 65-83
Vulnerability Type: Unsafe execution of user-controlled SQL throughEXPLAIN ANALYZE
Risk Level: HighVulnerable Code
markdown ## Step 1: Run EXPLAIN ANALYZE If the user provides a query but no EXPLAIN output, run it: ### PostgreSQL ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';MySQL
sql EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'; -- Or for runtime stats: EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';text ### Technical Analysis The skill instructs the agent to run a user-provided query when no execution plan is supplied. In PostgreSQL and supported MySQL versions, `EXPLAIN ANALYZE` does not merely inspect a statement: it executes the statement to collect runtime statistics. The instructions do not: - Restrict analyzed statements to a single read-only `SELECT`. - Reject DML, DDL, transaction-control statements, or side-effecting stored functions. - Require an explicit confirmation before executing the statement. - Require a read-only transaction or least-privileged database account. - Require execution against a disposable or non-production database. - Define statement timeouts, lock timeouts, row limits, or resource controls. Consequently, attacker-controlled SQL could be executed with the privileges of the database account available to the agent. Even syntactically read-only queries may invoke volatile functions, trigger expensive operations, acquire locks, or consume excessive CPU, memory, and I/O. ### Attack Path 1. The agent has access to a database and loads this skill to diagnose SQL performance. 2. An attacker submits a side-effecting or resource-intensive SQL statement while withholding an existing execution plan. 3. The skill directs th ...[truncated 1416 chars]- Remediation
View remediation
Remediation Suggestions
-
Use non-executing plans by default. Run plain
EXPLAINrather thanEXPLAIN ANALYZEfor user-provided SQL unless runtime execution is explicitly required. -
Require explicit informed confirmation. Before runtime analysis, explain that the statement will execute and obtain confirmation identifying the target database and environment.
-
Allow only validated read-only statements. Parse SQL using a database-aware parser and permit exactly one approved
SELECTstatement. Reject DML, DDL, transaction commands, multi-statement input,COPY, administrative commands, and unsafe stored-function calls. Keyword filtering alone is insufficient. -
Enforce read-only execution at the database layer. Use a dedicated least-privileged read-only account and a read-only transaction. Do not rely solely on prompt instructions or application-side validation.
-
Use an isolated environment. Prefer a disposable database, staging environment, or read-only replica containing appropriately sanitized data. Runtime analysis should not default to production.
-
Add resource limits. Configure statement and lock timeouts, connection limits, workload controls, and temporary-file or memory limits. Cancel queries that exceed defined thresholds.
-
Use rollback protection where supported. Execute approved analysis in an explicit read-only transaction and roll it back after collecting results. Note that rollback is not a substitute for read-only privileges because some external or volatile side effects may not be transactional.
-
Document safe behavior directly in the skill. Replace “run it” with a policy such as: “Generate plain
EXPLAINonly. RunEXPLAIN ANALYZEsolely for a validated read-only query after explicit approval, using a read-only account on a non-production system with strict timeouts.”
-