# SQL guards

> The exact rules the AST inspector enforces on every submission — WHERE requirements, LIMIT injection, transactions and blocked statements.

- Documentation: Reference
- Updated: 2026-09-07
- Source: https://queryproxy.com/docs/sql-guards/
- Language: en-US
- Author: Muhammet ŞAFAK

---
Every submission is parsed and inspected **before** it can enter the approval
queue. A request that violates a guard is rejected at submission with an
explicit message — it never reaches a DBA, let alone a database.

## The rules

### UPDATE / DELETE require a WHERE

```sql
DELETE FROM users;                    -- ✗ rejected
DELETE FROM users WHERE id = 42;      -- ✓ allowed (still needs approval)
```

This applies inside transactions too. There is no override — a full-table
write must be expressed explicitly (e.g. `WHERE 1 = 1`), so it can never happen
by accident.

### SELECT gets a LIMIT

- No `LIMIT`? The default (`1000`) is injected automatically, on its own line
  so a trailing line comment can never swallow it.
- `LIMIT` above the hard cap (`10000`)? It is clamped down.
- `LIMIT ALL` (PostgreSQL) counts as unlimited and is clamped.
- Offsets are preserved: `LIMIT 10, 20000` becomes `LIMIT 10, 10000`,
  `LIMIT 99999 OFFSET 4` becomes `LIMIT 10000 OFFSET 4`.
- `FETCH FIRST n ROWS ONLY` and T-SQL `TOP n` are clamped the same way.
- A `LIMIT` the guard cannot understand as a plain row count (arithmetic like
  `LIMIT 100*100000`, expressions, placeholders) is **rejected**, not left
  unclamped — a limit that can't be verified can't be enforced.
- Subquery LIMITs are left alone; only the outer query is guarded. Locking
  clauses are respected — the injected LIMIT lands *before* `FOR UPDATE`.

On top of this, the worker enforces an **absolute row ceiling** (the hard cap)
while streaming results, independent of the SQL — so even a query that dodges
the clause-level guard (dialect quirks, `SHOW`, `EXPLAIN`) can never return more
than the cap; the stored result is flagged as truncated.

Both numbers are configurable — see [Configuration](/docs/configuration/).
The prepared SQL (with the injected LIMIT) is shown to the developer before
submission and to the DBA at review; what you approve is exactly what runs.

### Multiple statements need an explicit transaction

```sql
UPDATE a SET x = 1 WHERE id = 1;
UPDATE b SET y = 2 WHERE id = 2;      -- ✗ rejected: two bare statements
```

```sql
BEGIN;
UPDATE a SET x = 1 WHERE id = 1;
UPDATE b SET y = 2 WHERE id = 2;
COMMIT;                               -- ✓ allowed, runs atomically
```

The worker wraps the statements in a real transaction: if any statement fails,
everything rolls back. `ROLLBACK` in a submission is rejected — rollback on
failure is automatic, not something to hand-write. Nested transactions are not
supported.

### Always blocked

Some statements are refused outright, whatever the role:

- `DROP DATABASE` / `DROP SCHEMA`
- `GRANT`, `REVOKE`
- `CREATE USER` / `ALTER USER` / `DROP USER` (and `ROLE` / `LOGIN` variants)
- `SET GLOBAL`
- `SHUTDOWN`
- Server-side file / program IO: `SELECT … INTO OUTFILE` / `INTO DUMPFILE`,
  `LOAD DATA`, PostgreSQL `COPY` (including `COPY … TO PROGRAM`), and the
  `LOAD_FILE()` function.

These checks run against a **comment-normalized** form of the statement, so
tricks like `DROP/**/DATABASE` or a leading `/* … */` comment cannot smuggle a
blocked statement past them.

`DROP TABLE`, `TRUNCATE` and `ALTER` are **not** blocked outright — they run as
approval-gated writes, but are flagged as **DDL / destructive** so a DBA sees
exactly what they're approving.

Database administration belongs in your infrastructure tooling, not in a query
portal.

## Dialects and the conservative fallback

The parser is MySQL-dialect-first. Statements it cannot fully parse (PostgreSQL
casts like `::jsonb`, driver-specific operators) are still guarded
**best-effort by keyword**: an unparseable `UPDATE` without `WHERE` is still
rejected, an unparseable `SELECT` still gets a LIMIT appended — and anything
unclassifiable is treated as a **write**, which means full approval scrutiny.
When in doubt, QueryProxy errs on the strict side.