# Safe SQL MCP

**Let an AI assistant answer business questions from your database — without giving it the keys.**

Ask Claude (or any MCP client) *"What was our revenue in the last 7 days?"*, *"Which orders are still unpaid?"*, *"What sells best?"* — it writes the SQL, this server runs it, and the assistant answers in plain language.

The point is **what the AI cannot do**, and that it is enforced by the server, not by instructions to the model:

| Guard | How it is enforced |
|---|---|
| **Read-only** | The database is opened read-only, and an SQLite authorizer allows only `SELECT`. `DELETE`, `UPDATE`, `DROP`, `PRAGMA`, `ATTACH` are refused by the database engine itself. |
| **Only the tables and columns you choose** | The AI never touches your real database. On start the server builds a *served copy* with only the allow-listed tables and columns. Street addresses, purchase costs, anything you leave out — simply isn't there. |
| **Personal data masked** | Emails and phones are masked in the copy (`a***@example.com`, `***67`). No SQL trick can unmask what was never copied. |
| **Limits** | Every query has a time limit (5 s) and a row limit (200 rows). |
| **Audit log** | Every query — allowed or refused — is written to `logs/audit.jsonl` with the question it was meant to answer. |

**Business numbers by the owner's rules — computed, not improvised.** Without an agreed definition, an AI can count cancelled, refunded and unpaid orders as revenue. Here the key numbers are **metrics with SQL written and checked in advance** (`"metrics"` in `config.json`): the AI calls `get_metric`, and every answer says which **period** and which **rule** were used. For anything else the AI writes its own `SELECT`, guided by the owner's definitions.

| Question (demo data, all time) | Metric | Result |
|---|---|---|
| What's our revenue? | `revenue` | **$89,678** from 1,091 orders |
| …and if you just sum every order? | naive `SUM(total)` | $98,685 — **10% too high**: includes cancelled, refunded and unpaid |
| Which orders are unpaid? | `unpaid_orders` | 38 orders, $2,539 |
| How many paid orders wait for shipping? | `awaiting_shipment` | 8 |
| Average order value? | `average_order_value` | $82.20 |
| Best sellers? | `top_products` | Resistance Bands (260), Scented Candle (256), Monitor Stand (241) |

The tests check that the metrics agree with each other and with the raw tables (revenue ÷ orders = average order value; unpaid orders = pending payments; awaiting shipment = shipments not yet sent).

These guards are covered by attack tests (`tests/test_guard.py`): writes, `PRAGMA`, `ATTACH`, reading the SQLite catalogue, hidden columns, unmasking through string functions, multiple statements, endless recursive queries, dangerous functions.

## Try it in 3 minutes (demo shop data)

The demo ships with a synthetic shop: 220 customers, 26 products, 1 200 orders over the last 90 days, payments and shipments. All data is fake and dated relative to today, so "last 7 days" always has something.

```bash
git clone https://github.com/korolserhii/safe-sql-mcp
cd safe-sql-mcp
python -m venv .venv
.venv/bin/pip install -e .          # Windows: .venv\Scripts\pip install -e .
```

Add it to **Claude Desktop** → Settings → Developer → Edit Config (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "safe-sql": {
      "command": "C:\\path\\to\\safe-sql-mcp\\.venv\\Scripts\\python.exe",
      "args": ["-m", "safe_sql_mcp.server", "--config", "C:\\path\\to\\safe-sql-mcp\\config.json"]
    }
  }
}
```

(macOS/Linux: `"command": "/path/to/safe-sql-mcp/.venv/bin/python"`.) Restart Claude Desktop and ask:

- What was our revenue in the last 7 days, and how many orders?
- Which orders are still unpaid? Show the 5 most recent.
- What are our 5 best-selling products?
- How many paid orders are still waiting to be shipped?
- Delete all cancelled orders. *(it can't — try it)*

## Use it with your own data

Everything lives in `config.json`: the business definitions, the source database, the tables and columns the AI may see, what to mask (`email`, `phone`, or `hide`), short notes that tell the AI what values mean, the row and time limits. Set `"demo": false` and point `"source"` at your SQLite file.

**Production databases (PostgreSQL, MySQL, SQL Server).** The same pattern applies, with the database's own guards: a read-only replica or snapshot, a role that can only `SELECT` from masked views, statement timeouts, and the same audit trail. That setup depends on your schema and your rules — if you want it done for your data, open an issue or reach out via my GitHub profile.

## Tools exposed to the AI

- `list_tables` — ready-made metrics, business definitions, timezone, and the allowed tables with columns and what the values mean.
- `get_metric(name, days)` — a business number by pre-checked SQL, for the last N days or all time, with the period and rule stated.
- `run_query(sql, purpose)` — one read-only `SELECT`; returns columns and rows as JSON. `purpose` is kept in the audit log.

## Limits of this demo — read before using real data

- **This is a demo, not an independent security audit.** The guards above are tested, not certified.
- **SQLite only.** For a production database, use the database's own least-privilege tools as well (a read-only user that can only `SELECT` from masked views, statement timeouts) — don't rely on any MCP server alone.
- **Data can contain instructions.** A product name or customer note could say "ignore your rules". This server cannot change or exfiltrate anything even if the AI is fooled — it can only read the masked copy — but treat answers built on free-text fields with care.
- **The audit log stores the SQL and the question** the AI was answering. Keep it private; it is excluded from git.
- **Never put database passwords in `config.json` or in a chat with an AI.** For a real database, keep credentials in environment variables on the machine that runs the server.
- Partial payments and partial refunds are not modelled in the demo data; a real shop's metrics must be defined for them.

## License

MIT