Show and tell: Hardening TypeScript MCP Database Tools — Walkthrough on Read-Only Guardrails, CTE Defenses & Stdio Isolation

[A7mdMe]

Context & Maintainer Disclosure

Maintainer of MCP Production Kit (an open-source TypeScript Model Context Protocol server starter kit).

As more TypeScript developers connect AI agents (Claude Desktop, Cursor, Cline, OpenWebUI) to relational databases via Model Context Protocol (MCP) tools, standard tutorials suggest passing raw SQL straight to a database connector tool. In development this works, but in production it exposes the database to severe risks: hallucinated mutations, prompt-injected DDL, and context exhaustion.

We wanted to share a concrete end-to-end walkthrough on how to harden TypeScript MCP database tools, along with verified test benchmarks and architectural boundaries.

---

1. Three Hidden Failure Modes in Agent Database Tools

1. Stacked Multi-Statement Injections:
   An agent reading untrusted web scrape data or customer tickets can be tricked by indirect prompt injection into running:

   SELECT id, name FROM users; DROP TABLE audit_log; --

2. Data-Modifying Common Table Expressions (CTEs):
   A naive prefix regex checking only for ^SELECT will be bypassed by a data-modifying CTE:

   WITH deleted_rows AS (
     DELETE FROM orders WHERE status = 'pending' RETURNING id
   )
   SELECT count(*) FROM deleted_rows;

   Syntactically, the query starts with WITH, yet it executes a destructive mutation.
3. Stdio Protocol Framing Corruption:
   When database client libraries or ORMs print debug logs or query execution times via console.log, stdout is contaminated with raw strings. Under MCP's stdio transport, this corrupts JSON-RPC 2.0 message framing and crashes the client connection.

---

2. Layered Defense Architecture

+-------------------------------------------------------------+
|                 AI Agent (Claude / Cursor)                  |
+-------------------------------------------------------------+
                              |
                     tools/call "query_db"
                              |
+-------------------------------------------------------------+
|               MCP Server Application Layer                  |
|                                                             |
|  1. Comment Stripping (-- and /* */)                        |
|  2. String Literal Normalization ('...' -> '')              |
|  3. Multi-Statement Rejection (blocks semicolon stacking)   |
|  4. Strict Prefix Whitelist (SELECT or WITH only)           |
|  5. 16-Keyword Mutation Blacklist Check                     |
|  6. Row Limit Clamping (maxRows = 100)                      |
|  7. Parameterized Value Binding ($1, $2...)                 |
+-------------------------------------------------------------+
                              |
                    Sanitized Single Query
                              |
+-------------------------------------------------------------+
|                Database Server (PostgreSQL)                 |
|                                                             |
|  - Role: read_only_agent (GRANT SELECT ON ALL TABLES)       |
|  - statement_timeout = '5000' (5s kill-switch)              |
+-------------------------------------------------------------+

(Note: This application-level guardrail uses token & regex sanitization, not a full SQL AST compiler, and is engineered specifically to accompany database-level read-only permissions like GRANT SELECT).

---

3. Core Implementation: Safe Query Inspection

Here is the exact TypeScript inspection routine:

const PROHIBITED_KEYWORDS = [
  'DROP', 'DELETE', 'TRUNCATE', 'ALTER', 'UPDATE', 'INSERT',
  'CREATE', 'REPLACE', 'GRANT', 'REVOKE', 'COPY', 'CALL',
  'EXECUTE', 'LOCK', 'VACUUM', 'REINDEX'
];

export function sanitizeSqlForInspection(sql: string): string {
  return sql
    .replace(/--.*$/gm, ' ')              // 1. Strip single-line comments
    .replace(/\/\*[\s\S]*?\*\//g, ' ')   // 2. Strip multi-line comments
    .replace(/'(?:''|[^'])*'/g, "''")     // 3. Normalize string literals
    .trim();
}

export function validateReadOnlyQuery(rawSql: string): { safe: boolean; sanitizedSql: string; reason?: string } {
  const sanitized = sanitizeSqlForInspection(rawSql);
  if (!sanitized) return { safe: false, sanitizedSql: '', reason: 'Empty SQL query' };

  // Reject multiple statements separated by semicolon
  const statements = sanitized.split(';').map(s => s.trim()).filter(Boolean);
  if (statements.length > 1) {
    return { safe: false, sanitizedSql: sanitized, reason: 'Multiple SQL statements rejected' };
  }

  const queryToInspect = statements[0];

  // Must begin with SELECT or WITH
  if (!/^SELECT\b/i.test(queryToInspect) && !/^WITH\b/i.test(queryToInspect)) {
    return { safe: false, sanitizedSql: sanitized, reason: "Query must start with 'SELECT' or 'WITH'" };
  }

  // Check prohibited mutation keywords across token stream
  for (const keyword of PROHIBITED_KEYWORDS) {
    if (new RegExp(`\\b${keyword}\\b`, 'i').test(queryToInspect)) {
      return { safe: false, sanitizedSql: sanitized, reason: `Prohibited keyword '${keyword}' detected` };
    }
  }

  return { safe: true, sanitizedSql: sanitized };
}

---

4. Verified Automated Test Matrix

We run an automated test harness covering both benign queries and common injection patterns:

• SELECT id, name FROM users WHERE active = true -> PASS
• SELECT * FROM orders WHERE user_id = $1 -> PASS
• WITH t AS (SELECT count(*) as c FROM orders) SELECT * FROM t -> PASS
• SELECT * FROM audit_log WHERE action = 'DROP TABLE' -> PASS (string literals normalized)
• DELETE FROM users WHERE id = 1 -> BLOCKED
• DROP TABLE analytics_events -> BLOCKED
• SELECT * FROM users; DROP TABLE users; -> BLOCKED (multiple statements rejected)
• WITH d AS (DELETE FROM users RETURNING *) SELECT * FROM d -> BLOCKED (CTE mutation detected)
• /* comment */ DROP TABLE users -> BLOCKED (comment stripped)

---

5. Architectural Comparison: Free Core vs. Pro Pack Add-on

• Open-Source Core (MIT): github.com/A7mdMe/mcp-production-kit
  Includes the full JSON-RPC 2.0 dual transport (stdio + SSE), zero-leak stderr streaming, diagnostic system telemetry, SSRF-guarded HTTP fetch tool, in-memory KV state store, and standard Dockerfile.
• Commercial Pro Pack ($19 one-time on Whop): whop.com/checkout/plan_eiKHeRpooY3yc
  For engineering teams deploying to cloud production, includes pre-packaged postgres_connector.ts, Cloudflare Workers serverless edge template, and hardened multi-container Docker Compose with 26 passing automated tests.

---

Questions & Technical Objections for the Community

We would love feedback and critique from TypeScript database engineers:

1. What edge cases in PostgreSQL (e.g., dollar-quoted strings $$...$$, custom procedural functions, or transaction control like ROLLBACK) do you feel need deeper handling beyond regex tokenization?
2. When exposing Drizzle schemas or PostgreSQL tables to LLMs, do you prefer runtime tool-level sanitization, schema reflection with prepared statements, or strict database user permissions?

Full walkthrough guide: examples/SAFE_POSTGRES_MCP_WALKTHROUGH.md