lakshjethani/mcp-safesql

mcp-safesql

Minimal MCP server that exposes safe, read‑only SQL tools with basic PII masking.

It is intended as a starting point for wiring an LLM to your database without giving it full SQL access.

What it does

• Runs read‑only SELECT queries only.
• Restricts access to a whitelist of views (no direct table access).
• Applies simple PII masking to common fields (email, phone, SSN, etc.).
• Caps the number of rows returned.
• Provides a basic, redacted query plan tool for teaching/debugging (sql.explain_safe).

The server exposes two MCP tools:

• sql.query – run a safe SELECT against whitelisted views with masking.
• sql.explain_safe – return a generic, redacted operator outline for a query.

Requirements

• Node.js 18+ (recommended).
• One of:
  • SQLite database file.
  • PostgreSQL database that the server can connect to.

Install

git clone <this-repo-url>
cd mcp-safesql
npm install

Running the server

This project is written in TypeScript and uses tsx to run directly from src/.

SQLite (default)

By default the server expects a SQLite database:

• DB_TYPE=sqlite (default)
• SQLITE_PATH=./example.db (default path, change as needed)
• SAFE_VIEWS=safe_users_v (comma‑separated list of allowed views)
• MAX_ROWS=200 (row cap; optional)

Example:

export DB_TYPE=sqlite
export SQLITE_PATH=/path/to/your.db
export SAFE_VIEWS="safe_users_v,report_active_users_v"

npx tsx src/server.ts

Make sure the views listed in SAFE_VIEWS exist in your database. Only those views can be queried.

PostgreSQL

To use PostgreSQL instead:

• DB_TYPE=postgres
• PG_CONNECTION_STRING=postgres://user:pass@host:5432/dbname
• SAFE_VIEWS – comma‑separated list of allowed views (schema-qualified if needed, e.g. public.safe_users_v)
• MAX_ROWS – optional row cap (default 200)

Example:

export DB_TYPE=postgres
export PG_CONNECTION_STRING="postgres://user:pass@localhost:5432/mydb"
export SAFE_VIEWS="public.safe_users_v,public.safe_orders_v"

npx tsx src/server.ts

Environment configuration

The server reads these environment variables:

MCP tools

sql.query

• Description: Run a read‑only SELECT on whitelisted views; masks PII; caps rows.

• Input schema:

  {
    "sql": "SELECT * FROM safe_users_v"
  }
  

• Behavior:

  • Rejects anything that is not a single SELECT statement.
  • Rejects queries that reference tables/views not in SAFE_VIEWS.
  • Wraps the query in a subselect and applies a LIMIT if none is present.
  • Masks values that look like email/phone/SSN or come from suspiciously named columns.

sql.explain_safe

• Description: Show a redacted query plan (generic operator tree + rough estimates), without revealing table/index names.

• Input schema:

  {
    "sql": "SELECT * FROM safe_users_v WHERE email LIKE '%@example.com'"
  }
  

• Behavior:

  • Applies the same SELECT‑only and whitelist checks as sql.query.
  • Returns a simple textual tree of operators (e.g., Select → Scan) suitable for teaching or quick inspection.

Using with an MCP client

Exact configuration depends on your MCP‑aware client. In general you will:

1. Ensure the environment variables above are set.

2. Configure your client to launch the server with a command like:

   npx tsx src/server.ts
   

3. Register the server under the name mcp-safesql in your client’s MCP configuration.

Consult your MCP client’s documentation for the precise config file format and where to put it.

Development

• Main entry: src/server.ts
• TypeScript config: tsconfig.json

You can also compile to JavaScript if you prefer:

npx tsc
node dist/server.js

This repo is intentionally minimal and is meant as a safe starting point you can adapt for your own schema and policies.