Query a SQL Database Safely

# Query a SQL Database Safely

## Purpose
Translate a natural-language question into a **read-only, parameterized** SQL query, execute it against a database connection under a least-privilege role, and return the rows — without ever mutating data or exposing the database to SQL injection.

## When to use
- A user asks a question that maps to data ("how many orders shipped last week?", "top 5 customers by spend").
- You have a known schema and a connection whose role is restricted to reads.

**Do NOT use for:** writes, schema changes, admin tasks, or anything that should alter state. This skill is strictly for retrieving data.

## Inputs
- `question` — the natural-language request.
- `schema` — table/column definitions (and ideally allowed tables/views) the query may touch.
- A **read-only** database connection, referenced only as a placeholder: `<DB_CONNECTION_STRING>` / `<DB_RO_USER>`. Never hardcode real credentials.
- Optional `max_rows` (default 100).

## Steps
1. **Clarify intent.** If the question is ambiguous (timeframe, units, which entity), ask one focused clarifying question rather than guessing.
2. **Map to schema.** Identify the specific tables/columns needed. If the question references data outside the allowed schema, refuse and explain.
3. **Build a SELECT only.** Construct a single `SELECT` statement. **Explicitly forbid** `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `TRUNCATE`, and any DDL (`CREATE`/`ALTER`/`DROP`/`GRANT`). Reject multi-statement input (no `;`-chained commands).
4. **Parameterize everything (decision point).** Any value derived from user input MUST be bound as a query parameter (`$1`, `?`, `:name`) supplied separately to the driver. **Never** string-concatenate or interpolate user input into the SQL text — that is the SQL-injection vector. Only validated identifiers from an allowlist may be templated, and only against a known-good list.
5. **Enforce a LIMIT.** Append a bounded `LIMIT :max_rows` (e.g. 100). Prefer keyset/explicit ordering for stable results. This caps blast radius and cost.
6. **Static-check the statement.** Verify it begins with `SELECT`/`WITH ... SELECT`, contains no forbidden keywords, no comment-based injection (`--`, `/* */`) smuggled from input, and exactly one statement.
7. **Use a least-privilege role.** Execute through a connection whose DB role has **SELECT-only** grants (no write/DDL), ideally on a read replica. Defense in depth: even a bypassed check can't mutate.
8. **Set safety limits.** Apply a statement timeout and (where available) a row/byte cap so a heavy query can't exhaust the database.
9. **Execute with bound parameters.** Run the query, passing parameters via the driver's parameter API — not inline.
10. **Return results.** Hand back the rows plus the exact SQL and the bound parameter values (for transparency/audit). Redact or omit columns containing secrets/PII where the caller isn't authorized.

## Output
```json
{
  "sql": "SELECT customer_id, SUM(total) AS spend FROM orders WHERE created_at >= $1 GROUP BY customer_id ORDER BY spend DESC LIMIT $2",
  "params": ["2026-01-01", 5],
  "row_count": 5,
  "rows": [
    { "customer_id": 1042, "spend": 9120.50 },
    { "customer_id": 318,  "spend": 7740.00 }
  ]
}
```

## Guardrails & notes
- **Read-only, always.** SELECT/CTE-SELECT only; forbid all writes and DDL. One statement per call.
- **Parameterize, never concatenate.** User values are bound parameters; only allowlisted identifiers may be templated.
- **Mandatory LIMIT** plus statement timeout to bound cost and exposure.
- **Least-privilege role** is the backstop — grant the query user SELECT only, ideally on a replica.
- **No real connection strings or credentials** in code or output — use `<DB_CONNECTION_STRING>` placeholders from the environment/secret manager.
- Treat the schema as the boundary: refuse questions about tables/columns outside the allowed set, and avoid returning unauthorized PII.

## Example
Question: "Who were our top 5 customers by spend this year?"
1. Map to `orders(customer_id, total, created_at)`.
2. Build `SELECT customer_id, SUM(total) AS spend FROM orders WHERE created_at >= $1 GROUP BY customer_id ORDER BY spend DESC LIMIT $2`.
3. Bind params `["2026-01-01", 5]` — the year boundary and limit come in as parameters, not inline text.
4. Static-check passes (single SELECT, no forbidden keywords).
5. Execute as `<DB_RO_USER>` (SELECT-only) with a 5s timeout.
6. Return the 5 rows alongside the SQL and params for audit.

curl -X POST https://agentprizm.com/api/v1/agent/marketplace/install \
  -H "Authorization: Bearer ap_your_key" \
  -H "Content-Type: application/json" \
  -d '{"sourceSkillId":"6a3d5f77ff1e38ac55db55ec"}'

curl -X POST https://agentprizm.com/api/v1/agent/marketplace/fork \
  -H "Authorization: Bearer ap_your_key" \
  -H "Content-Type: application/json" \
  -d '{"sourceSkillId":"6a3d5f77ff1e38ac55db55ec"}'