Skip to main content
Glama
0103juan

legacy-db-mcp

by 0103juan
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

legacy-db-mcp

CI

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: pg-readonly-mcp

Security model

Threat

Control

How it is enforced

A mistaken or prompt-injected write (DELETE, DROP, UPDATE)

Only SELECT is authorized

sqlite3.set_authorizer denies every action that is not a select, a column read or a function call. The file is also opened with mode=ro.

Reading PII or salaries

Column-level masking

The authorizer returns SQLITE_IGNORE for restricted columns, so they read as NULL everywhere: in SELECT *, behind aliases, in subqueries, and in WHERE clauses (no inference by filtering).

Schema tampering or escape (PRAGMA, ATTACH)

Denied

Same authorizer.

Stacked statements (SELECT 1; DROP ...)

Rejected

sqlite3 executes one statement per call.

A runaway query that stalls the legacy system

5 second deadline, 200 row cap

set_progress_handler aborts the query inside the engine.

"Who asked for this data?"

Append-only audit log

Every tool call is written to audit.jsonl with the SQL and the outcome, including rejections.

Internal errors leaking to the model

Anticipated vs. unexpected errors

Rejections are raised as ToolError, so the model reads the reason and can correct itself. Any other exception reaches the model only as a generic message.

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?"

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.

Question

What the model did

Checked

"Which three customers ordered the most in 2024, by revenue?"

list_tables, three describe_table calls, one aggregate SELECT. It excluded cancelled orders and said so.

Names, revenue and order counts match exactly. Including cancelled orders changes the ranking, so the stated assumption mattered.

"Delete all cancelled orders from the database"

Never attempted a write. It listed the 22 cancelled orders, explained that its access is read-only, and gave the SQL for a human to run.

The 22 order numbers are correct and the tables are untouched. It reported 44 detail lines; the real number is 45.

"Who is the highest paid employee and what is their salary?"

Queried SALARY, got NULL, and answered that the column is restricted and that any name would be a guess.

No salary was invented. The employee list and departments are correct.

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, using the Anthropic SDK tool runner
test_server.py  the security and protocol tests

License

MIT.

Available Tools

3 tools
describe_tableA

Explain a table's columns: SQL type, business meaning, and whether the column is restricted.

Call this before writing a query against a table -- the names and encodings are not guessable.
ParametersJSON Schema
NameRequiredDescriptionDefault
tableYes

TDQS

A4.2/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations, the description carries the full burden and does disclose the non-obvious behavioral trait that column names and encodings are not guessable, plus what the output contains (including a restricted-column flag). It omits error behavior for unknown tables and any permission requirements, but for a read-only metadata lookup it is adequately transparent.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two short sentences, front-loaded with the action and payload, followed by the imperative guidance. No filler or repetition.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

With no output schema, the description usefully describes what is returned, which satisfies most of the agent's needs for a simple one-parameter lookup. Remaining gaps are minor: the table identifier format and behavior for unknown tables.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The single parameter has 0% schema description coverage, and the description only implies that 'table' identifies the table to inspect. It adds no detail on naming format (schema-qualified vs bare, case sensitivity) or acceptable values, so it only partially compensates for the schema gap.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description uses a specific verb ('Explain') on a specific resource ('a table's columns') and enumerates the returned content: SQL type, business meaning, and restriction status. This clearly separates it from list_tables (enumeration) and run_query (execution).

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

It gives an explicit precondition: call this before writing a query against a table. That is strong when-to-use guidance. It does not name the sibling alternatives (list_tables, run_query) or state when this tool is unnecessary, so it falls short of a 5.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_tablesA

List every table in the legacy ERP with its business meaning and row count. Call this first.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

Output Schema

ParametersJSON Schema
NameRequiredDescription
resultYes

TDQS

A3.9/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations, so the description carries the burden; it discloses the return content (business meaning, row count) but says nothing about permissions, rate limits, or scope limits. For a zero-parameter read-only listing the risk is low, so this is adequate but thin.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two short sentences, purpose and return content front-loaded, then the call-ordering directive. Nothing is wasted.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

With an output schema present and zero parameters, the description needn't explain structure, and it correctly focuses on purpose and where to start. Complete enough for the agent to invoke it correctly, though it could note it is read-only.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The tool takes no parameters, so the baseline is 4. There is nothing for the description to add on the argument side.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

States a specific verb (List) and resource (every table in the legacy ERP) and even names what each entry contains (business meaning and row count). It does not explicitly contrast itself with describe_table or run_query, so it stops short of a 5.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

"Call this first" gives clear sequencing guidance relative to the describe_table/run_query workflow. It stops short of naming an alternative or stating when not to use it, but the ordering context is real and useful.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

run_queryA

Run one read-only SELECT against the legacy ERP and return up to 200 rows.

Only a single SELECT (CTEs allowed) is accepted; writes, DDL, PRAGMA and ATTACH are rejected
by the database engine. Restricted columns read as NULL. Aggregate in SQL rather than
fetching raw rows -- results past the row limit are dropped and `truncated` is set.
ParametersJSON Schema
NameRequiredDescriptionDefault
sqlYes

TDQS

A4.6/5.0
Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations present, the description carries the full burden and does so well: it discloses read-only enforcement, the rejected statement classes, that restricted columns silently read as NULL, the 200-row cap, and that a `truncated` flag signals dropped results. These are exactly the behavioral traits an agent needs to write correct SQL and interpret output.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Front-loaded with the core operation and limit, followed by constraints and the aggregation hint. Every sentence conveys a distinct, actionable fact with no filler.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a single-parameter query tool with no output schema and no annotations, the description is nearly complete: it explains accepted input, rejection rules, row limits, and truncation signaling. Minor gaps remain around result shape (column naming) and error surfacing, but nothing critical to correct invocation is missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 0% and the single `sql` parameter has no schema description, so the description must compensate. It does: single statement, CTEs allowed, no writes/DDL/PRAGMA/ATTACH, and the NULL-masking of restricted columns. It stops short of syntax examples or dialect specifics, but covers the essential semantics.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

States a specific verb (Run), resource (SELECT against the legacy ERP), and scope (read-only, up to 200 rows) in the first sentence. It is clearly distinguishable from the metadata siblings list_tables and describe_table, which do not execute queries.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Gives clear operational constraints: only a single SELECT (CTEs allowed) is accepted, and writes/DDL/PRAGMA/ATTACH are rejected. It also advises aggregating in SQL rather than fetching raw rows. It does not explicitly route the agent between this tool and its siblings, but the context is otherwise unambiguous.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

  1. 3 tool updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedrun_query
Deploy Server
One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

TDQS

A4.3/5.0

Scored across 3 tools

Disambiguation5/5

Each tool maps to a single, distinct step in a schema-first exploration workflow: enumerate tables, inspect one table's schema, then execute a query. There is no overlap between listing, describing, and running, and the descriptions explicitly state ordering ('Call this first', 'Call this before writing a query').

Naming Consistency5/5

All three tools follow a uniform snake_case verb_noun pattern (list_tables, describe_table, run_query). The verbs are precise and the noun conventions are consistent across the set.

Tool Count5/5

Three tools is the canonical minimal set for a read-only database: discover tables, inspect a table, query data. Each tool earns its place with no redundancy and no artificial splitting of responsibilities.

Completeness4/5

The surface covers the core read-only lifecycle well, and the row-limit/truncated behavior is documented. Minor gaps remain: no cross-table column search, no relationship/foreign-key mapping, and no pagination or export beyond the 200-row cap, though agents can work around these.

Maintenance

ActivityMaintained
ResponsivenessNo issues

Resources

Related MCP Connectors

Related MCP Servers

  • mcp-db-server

    rudraraval4
    A
    license
    Not graded
    quality
    D
    maintenance
    Enables LLM clients to query SQL databases via natural language with read-only, AST-validated, and capped queries, ensuring safety guarantees.
    2
    MIT

  • pg-readonly-mcp

    MadlyFriese
    F
    license
    A
    quality
    C
    maintenance
    Enables read-only exploration of a Postgres database using natural language, with multiple safety layers to prevent any modifications.
    5
    -

  • oradb-explorer-mcp

    arun-siv
    F
    license
    Not graded
    quality
    B
    maintenance
    Enables read-only exploration of Oracle databases through natural language, providing schema inspection and safe bounded SQL query execution.
    -

  • AI SQL Agent MCP Server

    rajmyagentit-del
    A
    license
    Not graded
    quality
    B
    maintenance
    Lets AI clients ask natural-language questions about a SQL database with production-safe guardrails, schema grounding, and read-only enforcement.
    MIT