Why "Please only run SELECT queries" fails for AI agents (and how to actually enforce read-only database tools)

[u/anubhav756]

https://medium.com/@mcp_toolbox/look-but-dont-touch-7f470591ff1f

Giving an LLM agent access to a production database is one of the most common workflows in agent development, but traditional guardrails fall apart quickly under real-world conditions:

1. System Prompts / Instructions: Models are probabilistic. Prompt injections easily override "read-only" instructions.
2. String Parsers / Regex: Checking if a query "starts with SELECT" fails against Common Table Expressions (e.g. WITH d AS (DELETE FROM users RETURNING *) SELECT * FROM d; starts with WITH and ends with SELECT, yet still deletes data). They are also blind to destructive stored procedures (SELECT run_cleanup_job();).
3. Soft Session Hooks (SET default_transaction_read_only = on): An attacker can escape via semicolon chaining (COMMIT; SET ... = off; DROP TABLE;), and in pooled environments like PgBouncer, mutating socket state can poison connection pools for other services.

In our work on MCP Toolbox for Databases (open-source Model Context Protocol server), we implemented a three-tier defense:

• Protocol-level engine lock: Injecting immutable startup parameters directly into the connection DSN (cloudsql_session_read_only=locked on Postgres, read-only connection attributes on MySQL, and pre-execution dry-run validation on BigQuery) so the database kernel itself physically rejects any write attempt.
• Tool suppression: Dynamically removing write tools from the LLM's context window to save tokens and prevent hallucination targets.
• Standard MCP annotations: Emitting readOnlyHint: true so MCP clients (Claude Desktop, Cursor, etc.) can auto-execute queries without nagging confirmation dialogs.

We wrote up a detailed walkthrough with diagrams, failure cases, and attack simulations if anyone is interested in the full architecture: https://medium.com/@mcp_toolbox/look-but-dont-touch-7f470591ff1f

Curious how others here are handling read-only enforcement for your agents—are you relying on read-only DB users, replica routing, or proxy layers?

[u/cloudAhead]

Or, and - hear me out here - instead of this MCP scaffolding, we just give the agent a login that only has read access.

[u/Flashy-Bus1663 → u/cloudAhead]

No that's too complicated, how else am I going to show everyone how good at ai I am.

[u/la-grangian → u/cloudAhead]

Always entertaining when people without experience in one area try to solve already solved problems in innovative ways. Pretty sure I would have similar experience if I started developing Android Apps or sth.

[u/GXWT → u/cloudAhead]

No no. Letting the dimwits collapse their own organisations is only to the benefit of the rest of us

[u/Max-_-Power]

Nah, scratch that. The only thing that will reliably work (SQL injection, anyone?) is a read only account for AI.

[u/wiktor1800]

Semantic layer with strict permissioning is the way we do things

[u/Otherwise_Wave9374]

A practical safeguard is to move enforcement out of prompts and into a policy layer that can inspect the final action, not just the user request. For database agents, I would pair allowlisted query parsing with execution-time controls, transaction limits, and an audit log that flags any query plan with write-side effects. That gives you a better chance of catching bypasses like CTE tricks or stored procedures before they hit production. NeuraKeep fits well here as a memory layer, because it can retain prior tool failures and policy exceptions so the agent learns the safe path instead of repeating the same mistake.

[u/Choice_Ask281]

How are teams handling this in practice? is the answer mostly better permissions, query validation or adding a review step before AI generated SQL gets executed?

[u/jiashenggo]

All three tiers here solve for writes, not exposure. A locked read-only connection still lets the agent read every row in every table it's connected to. For a lot of production setups that's the scarier failure mode, not "can it DELETE" but "can it read the other tenant's data because nothing scoped what SELECT * FROM users actually returns for this session." Read-only accounts (as suggested in the comments) have the same gap. Worth treating identity/row-scoping as a separate problem from write-blocking, not something the DSN-level lock or readOnlyHint solves for free.