Rejecting writes and stacked statements in an MCP SQL tool before they run

An MCP query_database tool takes a string and runs it. The model writes that string. Your code only gets to decide whether it runs.

That means the SQL guard is doing the real work. The tool's description is a request the model can ignore. This post builds a text-level guard in plain Python that rejects writes and stacked statements before SQLite ever sees them. It covers the parsing traps along the way and the cases a guard like this still can't catch.

Why check the text at all

Python's sqlite3 already refuses more than a single statement per execute() call. That's a useful backstop, but you shouldn't rely on it alone, for a few reasons.

• The refusal comes from the driver, so its wording and behaviour depend on the driver. Swap SQLite for another database and the driver might happily run everything after the semicolon.
• It says nothing about writes. DELETE FROM users is a single statement, and execute() runs it.
• By the time the driver complains, you've already handed it the query. A guard that runs first lets you return a clear, specific refusal to the model, which it can read and use to rewrite the query.

So the guard's job is narrow: look at the text, decide whether it's a single read-only query, and refuse anything else with a message that says why.

The naive version, and where it breaks

The obvious attempt is a pair of string checks:

def check_read_only(sql: str) -> None:
 if ";" in sql.strip().rstrip(";"):
 raise ValueError("multiple statements are not allowed")
 if not sql.strip().lower().startswith("select"):
 raise ValueError("only SELECT queries are allowed")

This breaks in both directions.

It rejects valid queries. SELECT name FROM users WHERE bio = 'likes; semicolons' has a semicolon inside a string literal, and the guard calls it a stacked statement. A comment like -- see ticket; fixed later does the same.

It also lets bad ones through. SELECT /* ; */ ... is harmless, but the reverse trick matters: comments and literals can hide text from a naive scan, or show text that isn't really SQL. And WITH is missing from the allowlist. That's easy to add, but it opens a bigger hole, covered below.

The fix for both is the same. Before you look for semicolons or keywords, remove everything that isn't SQL structure.

Strip literals and comments in a single pass

String literals, quoted identifiers, and comments all have to be removed together, scanning left to right. If you strip comments first, a -- inside a string eats the rest of the line. If you strip strings first, a ' inside a comment opens a string that never closes. A single regex with alternation fixes this, because the regex engine takes whichever construct starts earliest:

import re

_SKIP = re.compile(
 r"'(?:[^']|'')*'" # string literal, '' is an escaped quote
 r'|"(?:[^"]|"")*"' # quoted identifier
 r"|--[^\n]*" # line comment
 r"|/\*.*?\*/", # block comment
 re.DOTALL,
)

Replace each match with a space rather than an empty string. Otherwise SELECT/**/name collapses into SELECTname, and keyword matching behaves strangely.

Whatever remains is the structure of the query: keywords, identifiers, operators, and real semicolons.

The guard

_WRITE_WORDS = re.compile(
 r"\b(insert|update|delete|replace|upsert|drop|create|alter"
 r"|attach|detach|pragma|vacuum|reindex)\b",
 re.IGNORECASE,
)

def check_read_only(sql: str) -> None:
 body = _SKIP.sub(" ", sql).strip()

 # A leftover quote or comment opener means something never closed.
 if "'" in body or '"' in body or "/*" in body:
 raise ValueError("unterminated string or comment")

 body = body.rstrip(";").strip()
 if not body:
 raise ValueError("empty query")
 if ";" in body:
 raise ValueError("multiple statements are not allowed")

 if not body.lower().startswith(("select", "with")):
 raise ValueError("only SELECT queries are allowed")

 if _WRITE_WORDS.search(body):
 raise ValueError("query contains a write or schema keyword")

A few of these checks are worth explaining.

The unterminated check. SELECT 'abc doesn't match the string pattern, so the quote survives the strip. Treating leftovers as an error closes off a class of inputs where the guard and the database might disagree about where a literal ends. Rejecting ambiguous input is cheaper than reasoning about it.

Trailing semicolons are allowed. Models often end queries with ;. Stripping trailing semicolons before the stacked-statement check means SELECT * FROM t; passes and SELECT * FROM t; DROP TABLE t doesn't.

Allowing WITH needs the keyword scan. In SQLite a common table expression can lead into a write:

WITH stale AS (SELECT id FROM sessions WHERE expired)
DELETE FROM sessions WHERE id IN (SELECT id FROM stale);

That starts with WITH and contains no semicolon in the middle. Checking the leading keyword alone would let it through. The blocklist scan catches the DELETE. So the leading-keyword check and the keyword scan cover different gaps, and you need both.

pragma and attach are on the list. They don't look like writes, but some pragmas change database settings, and ATTACH points the connection at another file. A read-only query tool has no reason to issue either.

Test the denials, not just the happy path

The guard exists to refuse, so most of its tests should be refusals. A small parametrized pytest file covers a lot:

import pytest

REJECT = [
 "DELETE FROM users",
 "SELECT * FROM users; DROP TABLE users",
 "WITH x AS (SELECT id FROM t) DELETE FROM t WHERE id IN (SELECT id FROM x)",
 "SELECT 'unterminated",
 "PRAGMA journal_mode = off",
 "ATTACH DATABASE 'other.db' AS other",
 " ",
]

ALLOW = [
 "SELECT name FROM users WHERE bio = 'likes; semicolons'",
 "SELECT name FROM users -- trailing; comment",
 "SELECT * FROM users;",
 "WITH recent AS (SELECT * FROM orders) SELECT count(*) FROM recent",
]

@pytest.mark.parametrize("sql", REJECT)
def test_rejects(sql):
 with pytest.raises(ValueError):
 check_read_only(sql)

@pytest.mark.parametrize("sql", ALLOW)
def test_allows(sql):
 check_read_only(sql)

The ALLOW list matters as much as REJECT. It's what stops a later "tightening" of the guard from quietly breaking queries the model depends on.

What this guard does not do

This is a text-level check, and it has real limits.

It has false positives. replace() is a legitimate SQLite string function, and the blocklist rejects it. A column named update or delete would be rejected too. You can refine the scan to look only at statement position, but every refinement moves you closer to writing a SQL parser, and every parser you write is one more thing that can disagree with SQLite.

It doesn't know every SQLite quoting form. SQLite also accepts [bracketed] and `backticked` identifiers. The strip above doesn't handle them, so a bracketed identifier that contains a keyword or a semicolon gets misread. Add them to _SKIP if your schema needs them.

It isn't the only layer you want. A text guard answers "does this look like a single read"; the database should answer "can this connection write at all." Opening SQLite in read-only mode through a URI, or using a database user with only read grants, doesn't depend on your regex being right. Treat the text guard as the layer that gives the model a clear refusal, and the connection as the layer that holds even when the text guard is wrong.

It does nothing about what reads return. A read-only tool pointed at a table of password hashes or customer emails is still a leak. Which database file the tool can open is a separate decision, and it matters more than any guard.

If your MCP server needs the model to write data, this approach doesn't fit. Build narrow tools for specific writes, like create_ticket(title, body), instead of accepting open-ended SQL and trying to filter it.

I packaged a read-only SQLite query_database tool with a guard that rejects writes and multi-statements before they run, alongside the other guards and transports, as the MCP Starter Kit: https://fulcrumenterprises.gumroad.com/l/mcp-starter-kit

Comments

[Raju Dandigam]

I like that you treat the regex as an explanatory guard and the read-only connection as the actual write boundary. A SELECT can still be damaging operationally—a huge join or recursive CTE can burn time and memory even without changing data. Do you also enforce a query timeout or row limit at the SQLite connection/tool layer, separate from this text check? That seems like the next useful denial test for an agent-generated query.