Read-only on the SQL tree, not on the first keyword

An MCP server that exposes a SQL engine to agents almost always makes the same promise: it is read-only. The promise is easy to state and hard to keep. Most implementations we read keep it with a filter on the first keyword: if the query starts with SELECT, SHOW or EXPLAIN, it goes through; otherwise it is refused. We started the same way in akko-mcp-trino, then we tried to break our own guard. Here are the six defects we met, in our code or in someone else’s, and the shape the guard took afterwards.

What the first word does not say

A prefix filter answers the question “what does the text start with?”. The right question is “what will the engine do if I send it this text?”. The two questions only share an answer in the simple cases. Each defect below is a case where they part ways.

1. Stacked statements. SELECT 1; DROP TABLE t starts with SELECT. Depending on the driver and the engine, the second statement runs, is refused, or is silently dropped. A guard must not depend on that behaviour. It requires exactly one statement and refuses the rest.

2. A write inside a CTE. WITH w AS (DELETE FROM t) SELECT 1 starts with WITH, which the filter allows, and its root really is a SELECT. We found this case on 12 September on our own guard, with the adversarial proof we replay on a real cluster: the guard only looked at the root node of the tree, and the wrapped write reached Trino. Trino refused it as a syntax error; we would rather not count on that.

3. A write inside a subquery. SELECT * FROM (DROP TABLE t) x is the same idea in another form. The conclusion is the same: walk the whole tree, and refuse as soon as a write node appears, wherever it sits.

4. EXPLAIN ANALYZE executes. On Trino, EXPLAIN plans a query without running it, but EXPLAIN ANALYZE runs it to measure the plan. EXPLAIN ANALYZE INSERT INTO t VALUES (1) starts with EXPLAIN and writes a row. We found this case on 13 September while reading another server’s guard, and ours had the same hole. The rule became: EXPLAIN is allowed if and only if the explained statement is itself a read, and ANALYZE is always refused.

5. Commands that are not called writes. GRANT, SET SESSION, CALL do not modify a table and do not appear in the usual list of forbidden verbs. GRANT changes who can read what. SET SESSION changes how the session’s next queries behave. CALL runs a procedure whose effects nobody knows in advance. A read-only guard refuses all three.

6. What the filter cannot read. /* note */ DELETE FROM t starts with /*. Text the parser does not understand, or that matches no known shape, must be refused, not handed to the engine in the hope that it decides. That is the fail-closed principle: uncertainty closes, it never opens.

The guard we kept

The current version fits in one function. It parses the text with sqlglot in the Trino dialect, requires a single statement, then walks the whole tree and refuses as soon as a node belongs to the family of writes and side effects (Insert, Update, Delete, Merge, Create, Drop, Alter, Truncate, Grant, Set, generic command). SHOW and DESCRIBE, which sqlglot does not structure, are accepted by their leading keyword, and EXPLAIN is re-analysed on the statement it wraps. Any parser exception is a refusal.

statements = sqlglot.parse(sql, read="trino")
if len(statements) != 1:
    return False
root = statements[0]
if isinstance(root, (exp.Select, exp.Union)):
    return not any(isinstance(n, WRITE_TYPES) for n in root.walk())

Each of the six cases above is a test in the repository, and the adversarial proof replays part of them on a real cluster at every release. We do not consider the list closed. That is why we published it.

What the guard does not replace

A read-only guard on the server is a second line. The first line remains the engine, which applies its own permissions to the person the agent works for. That is what akko-mcp-trino does by carrying the user’s identity all the way to Trino; we described it in our first article. If the engine receives every query under the right identity, a write that slipped past the guard would still be refused to anyone without the right to write. If the engine receives everything under a service account, the best guard in the world protects a door that no longer has a lock.

The code is on GitHub under the Apache 2.0 licence, and the package installs with pip install akko-mcp-trino. If you break the guard, open an issue: that is exactly what we are looking for.