legacy-db-mcp

A custom Model Context Protocol server that lets an LLM query a legacy ERP database in plain language, without being able to change it, read its sensitive columns, or stall it.

The interesting part is not that the model can write SQL. It is that the database engine, not a prompt and not a regex, decides what that SQL is allowed to touch.

"Which customers on credit hold still have open orders?"
        │
   Claude (MCP host) ──MCP/stdio──▶ server.py ──▶ SQLite authorizer ──▶ legacy_erp.db (opened read-only)
        │                              │
        │                              └──▶ audit.jsonl (every call, allowed or rejected)
        ▼
   list_tables → describe_table(CUSTMST) → describe_table(ORDHDR) → run_query(SELECT ...)

The problem it models

The demo database imitates a system nobody wants to touch: tables called CUSTMST and ORDHDR, dates stored as YYYYMMDD integers, one-letter status codes. An LLM cannot guess that STSCD = 'H' means "on credit hold". So the server exposes two things:

• A semantic layer. list_tables and describe_table translate the cryptic schema into business meaning, and flag which columns are restricted. The same dictionary is published as the MCP resource erp://dictionary.

• A guarded query tool. run_query accepts one SELECT and returns at most 200 rows.

Related MCP server: mcp-sql-agent

Security model

There is no SQL parsing and no keyword blocklist anywhere in the code. The authorizer runs while SQLite compiles the statement and sees the real tables, columns and operations, so there is no string for an attacker to obfuscate.

Run it

uv sync
uv run python seed.py          # creates legacy_erp.db
uv run pytest                  # 16 tests, no API key needed

Connect it to a client:

# Claude Code
claude mcp add legacy-erp -- uv run --directory /absolute/path/to/legacy-db-mcp python server.py

// Claude Desktop: claude_desktop_config.json
{ "mcpServers": { "legacy-erp": {
    "command": "uv",
    "args": ["run", "--directory", "/absolute/path/to/legacy-db-mcp", "python", "server.py"] } } }

Or use the included host, which connects Claude to the server over stdio (needs ANTHROPIC_API_KEY):

uv run python chat.py "Which sales rep shipped the most revenue in Q3 2024?"

The host calls the model through model-gateway: every turn of the tool runner is recorded in .gateway/ledger.jsonl, and the cost printed at the end is the sum of those records. That path has run against a fake model only; the three sessions below predate it.

What the tests prove

test_server.py attacks the server the way a confused or manipulated model would: nine kinds of non-SELECT statements, five ways of reaching a masked column, a recursive query that never ends, an injection through the table name, and a full round trip over the MCP protocol with an in-process client.

Checked against the live model

Three sessions with claude-sonnet-5-5 through chat.py, each answer recomputed directly against the database afterwards.

The miscount is the useful finding. The model fetched one line count per order and added 22 numbers itself instead of asking the database for the total. The server instructions now say that every count or total must come from SQL. That is a prompt, not a control: the engine can stop a model from writing or from reading a column, and it cannot stop it from doing arithmetic badly.

In these sessions the model never tried a forbidden statement, so the engine-level rejection was exercised only by test_server.py, not by a live model.

Limits, stated plainly

• SQLite stands in for the legacy system. The authorizer is a SQLite feature; on DB2, Oracle or SQL Server the same design maps to a read-only role, column grants or masking views, and a statement timeout.

• Access is per column, not per row. There is no notion of "this user may only see their region".

• Restricted columns are hidden by value, not by name: the model knows SALARY exists.

• The server runs over stdio for a single local user. A shared deployment would need the HTTP transport with authentication, and the audit log would need the caller's identity.

Layout

server.py       the MCP server: three tools, one resource, the authorizer, the audit log
seed.py         builds the demo database (deterministic)
chat.py         Claude as MCP host: the Anthropic SDK tool runner, called through model-gateway
test_server.py  the security and protocol tests

License

MIT.