# mcp-sql-analyst

[![CI](https://github.com/Tanzil-Ali-Rizvi/mcp-sql-analyst/actions/workflows/ci.yml/badge.svg)](https://github.com/Tanzil-Ali-Rizvi/mcp-sql-analyst/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/mcp-sql-analyst)](https://pypi.org/project/mcp-sql-analyst/)
![Python](https://img.shields.io/badge/python-3.11%20|%203.12%20|%203.13-blue)
![MCP](https://img.shields.io/badge/MCP-Python%20SDK%20v2-6E56CF)
![License](https://img.shields.io/badge/license-MIT-green)

**A secure, read-only SQL [Model Context Protocol](https://modelcontextprotocol.io) server. Point it at CSV, Parquet or JSON files and let Claude, Cursor or any MCP client analyse them, without being able to write, read other files, reach the network or see raw personal data.**

> Giving an AI assistant a database connection is the fastest way to get answers from data, and the fastest way to leak or damage it. This server makes the safe path the default: two independent security layers, personal-data masking, row and time limits, and an audit log of every call.

---

## Why it's safe: two independent layers

```mermaid
flowchart LR
    A[AI client<br/>Claude / Cursor / VS Code] -- MCP tool call --> G
    subgraph S[mcp-sql-analyst]
        G{Layer 1: SQL guard<br/>parse to AST, allow-list} -- rejected --> R[ToolError with reason<br/>model rewrites the query]
        G -- valid SELECT --> E[(Layer 2: DuckDB<br/>read-only file<br/>external access OFF<br/>config locked)]
        E --> L[Row cap · timeout]
        L --> P[PII masking<br/>name + value patterns]
        G & P --> AU[(Audit log JSONL)]
    end
    P -- results --> A
    F[Your CSV / Parquet / JSON] -. copied once at start-up .-> E
```

**Layer 1: the SQL guard** parses every query (sqlglot, DuckDB dialect) and allows only a single `SELECT` (with CTEs / UNION / EXCEPT) over the tables this server exposed. It rejects writes and DDL, statement stacking, `COPY` / `ATTACH` / `PRAGMA` / `SET`, file and network readers (`read_csv`, `glob`, `FROM 'file.csv'`), functions that execute SQL from a string (`query()`, `json_execute_serialized_sql`), secrets, environment and catalog snooping.

**Layer 2: the engine itself is locked.** Your files are copied into a private temporary DuckDB file, which is reopened **read-only** with `enable_external_access = false` and `lock_configuration = true`. Even if a query got past the parser, DuckDB would refuse to write, touch the file system, load extensions or use the network, and the settings can't be turned back on. Your original files are never opened for writing.

## Red-team results

52 attack queries across 9 categories, run on every commit in CI (`python -m mcp_sql_analyst.redteam --gate`). The gate fails the build if a single attack gets through, or if a legitimate analytics query stops working.

| Attack category | Attacks | Blocked by SQL guard | Also refused by the locked engine alone |
|---|---|---|---|
| Write / DDL | 9 | 9/9 | 9/9 |
| Statement stacking | 3 | 3/3 | 2/3 |
| File system read | 8 | 8/8 | 8/8 |
| File system write / export | 3 | 3/3 | 3/3 |
| Network / extensions | 6 | 6/6 | 6/6 |
| Dynamic SQL (guard bypass) | 3 | 3/3 | 2/3 |
| Settings, secrets & environment | 7 | 7/7 | 5/7 |
| Catalog snooping | 6 | 6/6 | 1/6 |
| Obfuscation (case, comments, CTEs, subqueries, UNION) | 7 | 7/7 | 7/7 |
| **Total** | **52** | **52/52** | **43/52** |

Legitimate analytical queries (joins, window functions, CTEs, `GROUP BY ALL`, `EXCEPT`) still working: **8/8**.

**How to read the last column:** it tests the engine *with the guard switched off*, as defence in depth. The engine alone stops every attack that could change data or escape the sandbox: writes, file access, exports, network and extensions. The 9 it lets through are harmless reads, such as listing the table names and columns, reading DuckDB's own settings, or re-reading a table that is already exposed. Results still pass through PII masking either way, and the guard blocks all of them.

## Tools, resources and prompts

| Type | Name | What it does |
|---|---|---|
| Tool | `list_tables` | Tables with row and column counts and source file |
| Tool | `describe_table` | Columns (type, nullable, personal-data flag) and sample rows |
| Tool | `profile_column` | Nulls, distinct values, min / max / mean / median, top values |
| Tool | `search_columns` | Find columns by name across all tables |
| Tool | `run_query` | One validated read-only `SELECT`; capped, timed and masked |
| Tool | `explain_query` | DuckDB query plan without returning data |
| Resource | `schema://overview` | Markdown data dictionary |
| Resource | `schema://tables/{table}` | One table's schema as JSON |
| Prompt | `saas_metrics_report` | Guided MRR / churn / retention report |
| Prompt | `data_quality_audit` | Guided data-quality review of one table |

Blocked queries come back as a tool error with the exact reason (e.g. *"Unknown table 'orders'. Available tables: customers, invoices, subscriptions."*), so the model can correct itself instead of guessing.

## Personal-data protection

- **By column name:** `email`, `phone`, `mobile`, `pan`, `aadhaar`, `card_number`, `dob` and similar columns are flagged in the schema and masked in samples, profiles and results.
- **By value pattern:** every returned string is scanned for emails, Indian mobile numbers, PAN, Aadhaar and card numbers, so `SELECT contact_email AS e` still comes back as `d***@example.in`.
- **Hard deny-list:** `--deny-columns contact_phone,dob` makes columns unqueryable, including via `SELECT *` (but `COUNT(*)` still works).
- **Opt-out** with `--allow-pii` only when the data owner allows it.

*Known limitation:* masking cannot stop deliberate partial extraction (e.g. `substr(email, 1, 3)`); use `--deny-columns` for columns that must never leave the server.

## Quickstart

```bash
pip install mcp-sql-analyst            # or run without installing: uvx mcp-sql-analyst --demo
mcp-sql-analyst --demo                 # bundled SaaS dataset (stdio)
mcp-sql-analyst ./sales.csv ./warehouse/ --max-rows 200 --audit-log audit.jsonl
mcp-sql-analyst --demo --transport streamable-http --port 8000   # remote clients
```

### Claude Desktop

Add to `claude_desktop_config.json` (Settings → Developer → Edit config):

```json
{
  "mcpServers": {
    "sql-analyst": {
      "command": "uvx",
      "args": ["mcp-sql-analyst", "--demo", "--audit-log", "/tmp/sql-analyst-audit.jsonl"]
    }
  }
}
```

Replace `--demo` with your own files or folders, e.g. `"/Users/me/data/sales.csv"`.

### Cursor / VS Code

Same `command` and `args` in `.cursor/mcp.json` or `.vscode/mcp.json` under `mcpServers` / `servers`.

### Try it with the MCP Inspector

```bash
npx @modelcontextprotocol/inspector mcp-sql-analyst --demo
```

## Demo dataset

`--demo` exposes a fictional Indian B2B SaaS company (Kirana Ledger), April 2024 to March 2026: **420 customers, 479 subscription periods and 4,425 monthly invoices**, with realistic upgrades, churn and failed payments. It is generated by a seeded script (`scripts/generate_demo_data.py`), so every number is reproducible. Contact details are fake and in the reserved `example.in` domain.

Try asking your assistant: *"Use the saas_metrics_report prompt"*, *"Which segment churns fastest?"* or *"Audit the invoices table for data-quality issues."*

## Configuration

| Flag | Default | Purpose |
|---|---|---|
| `paths…` | | CSV / TSV / Parquet / JSON / JSONL files or folders (each file becomes a table) |
| `--demo` | off | Add the bundled demo dataset |
| `--max-rows` | 500 | Hard cap on rows returned per query (clients can ask for fewer, never more) |
| `--timeout` | 15 s | Per-query time limit (runaway queries are interrupted) |
| `--deny-columns` | | Comma-separated columns that can never be queried |
| `--allow-pii` | off | Return personal data unmasked |
| `--audit-log` | | Append every tool call (tool, SQL, allowed / blocked, latency) to a JSONL file |
| `--transport` | `stdio` | `stdio` or `streamable-http` (`--host`, `--port`) |

## Development

```bash
git clone https://github.com/Tanzil-Ali-Rizvi/mcp-sql-analyst && cd mcp-sql-analyst
pip install -e ".[dev]"
pytest -q                                   # 95 tests: guard, engine, PII, MCP protocol, stdio end-to-end
python -m mcp_sql_analyst.redteam --gate    # security report + gate
```

Built on the **MCP Python SDK v2** (`MCPServer`), DuckDB and sqlglot. Tests drive the server through a real MCP `Client`, both in-process and as a subprocess over stdio, exactly as desktop clients do.

```
src/mcp_sql_analyst/
  guard.py      # layer 1: AST validation, function deny-list, table allow-list
  database.py   # layer 2: read-only locked DuckDB, timeout, row cap, profiling
  pii.py        # personal-data detection and masking
  audit.py      # JSONL audit log
  server.py     # MCP tools, resources, prompts and CLI
  redteam.py    # attack corpus + CI gate
  demo_data/    # bundled SaaS dataset
```

## Roadmap

- [ ] Read-only PostgreSQL and SQLite sources
- [ ] Column-level lineage for masking derived values
- [ ] Per-client API keys and rate limits for the HTTP transport
- [ ] Listing on the official MCP Registry

## License

MIT © 2026 Tanzil Ali Rizvi