Skip to main content
Glama
ccervantes369

sql-explorer

sql-explorer

一个 MCP 服务器,让 AI 助手可以用自然语言回答关于 SQLite 数据库的问题——同时既不会损坏数据库,也无法读取你标记为禁止访问的部分。

当被问到 “哪个城市花费最多?” 时,模型会自己发现数据表、读取 schema、编写自己的 SQL,然后给出答案。它永远没有机会去写入、删除或读取被封锁的列。

You:    Which city has spent the most in total?
Claude: Lyon, with 14 orders totalling 2,840.03.

You:    Give me the email and phone of every customer.
Claude: I can't — the server refuses access to customers.email.

为什么存在

把数据库连接交给语言模型确实是一件有风险的事。可能出错的有三种情况:

风险

处理方式

它执行 DELETE、UPDATE 或 DROP

只接受以 SELECT 开头的语句

它读取个人数据

SQLite 授权器 在引擎内部拒绝配置过的列

它返回数百万行

结果最多截断为 500 行,查询超过 5 秒会被中止

第二种情况最有趣。被封锁的列不会从 SQL 文本中被过滤掉——SQLite 在读取任何列之前都会询问“可以吗”,而服务器会给出回答。这意味着,即使一个查询从未在 SELECT 中提及 email,却用它作为过滤条件来一次猜一个地泄露地址,同样会被拒绝:

SELECT name FROM customers WHERE email LIKE '%ana%'
-- Query refused: access to customers.email is prohibited

没有任何换一种措辞能够绕过它,因为检查看的并不是措辞。

Related MCP server: safe-sql-mcp

快速开始

需要 Python 3.12+ 和 uv。

git clone <your-repo-url>
cd mcp_server
uv sync
uv run python scripts/make_sample_db.py   # builds the practice database
uv run pytest                             # 28 tests

要在浏览器里手动手动试一下这些工具(需要 Node.js):

uv run mcp dev src/mcp_server/__init__.py

与 Claude Desktop 配合使用

设置 → 开发者 → 编辑配置,然后添加:

{
  "mcpServers": {
    "sql-explorer": {
      "command": "uv",
      "args": ["run", "--directory", "/absolute/path/to/mcp_server", "mcp-server"],
      "env": {
        "SQL_EXPLORER_DB": "/absolute/path/to/your.db",
        "SQL_EXPLORER_BLOCKED_COLUMNS": "users.password_hash, users.ssn"
      }
    }
  }
}

之后重启应用。在应用运行时编辑文件是没用的——应用退出时会把配置文件覆盖成自己的内容。

配置

变量

默认值

含义

SQL_EXPLORER_DB

本仓库中的 sample.db

要提供数据库的 SQLite 文件

SQL_EXPLORER_BLOCKED_COLUMNS

customers.email, customers.phone

要禁止访问的列,格式为 table.column,逗号分隔

SQL_EXPLORER_TRANSPORT

stdio

stdio 或 streamable-http

SQL_EXPLORER_PORT

8000

监听端口,仅 HTTP 传输使用

SQL_EXPLORER_TOKEN

无

HTTP 传输所需的 Bearer 令牌。没有默认值;没有它,服务器就不会启动

不是 table.column 形状的值,都会让服务器拒绝启动,通过认证配置中的拼写错误要响亮地暴露出来,而不是被静默忽略。

Include Tools

Tools

list_tables() | 每张数据表的名称 | | describe_table(table) | 某张表的列信息:名称、类型、是否必填 | | run_query(sql) | 执行一个 SELECT 并返回列的信息 | | ping() | 存活检查 |

run_query 在结果数量达到上限时会报告 truncated: true,所以部分结果永远不会被误认为完整结果。

ping() | 存活检查 |

(注意上面表格其实应该只有一个 ping 行,但请按原文保留)

Resources

URI

内容

schema://tables

每张表及其列,每行一个

schema://{table}

一张表的详细内容:列名、类型、是否必填

服务器拒绝读取的列会被标记为 [blocked]:

customers(id, name, email [blocked], phone [blocked], city, signup_date)

这是故意的。这种保护不依赖保密性——授权器无论如何都会拒绝——所以列出被封锁的列毫无成本,反而能省去一次注定会被拒绝的 SELECT *。

schema://{table} 是一个模板:一份定义即可为每张表生成一个地址,无论数据库实际包含哪些表。

Prompts

提示词

作用

analyze_table(table)

分析一张表:大小、分布、缺失、离群值

data_quality_report()

检查重复、孤儿记录、不可能的值、可疑的均匀性

提示词返回的是指令,而不是数据。它说明如何正确地使用这个服务器——先读 schema,充分聚合而不是逐行列举,不要伸手去取被封锁的列——这样即使一个不熟悉数据库的用户也能提出有价值的问题。

通过网络使用它

默认情况下,服务器运行在 stdio 上:客户端以本地进程方式启动它,双房通过管道通信。它不需要认证,因为操作系统已经决定了谁可以启动这个进程。

如果将 SQL_EXPLORER_TRANSPORT 设置为 streamable-http,它就会变成一个 Web 服务——而任何能访问端口的人都可以与它对话。因此令牌是强制的:

SQL_EXPLORER_TRANSPORT=streamable-http \
SQL_EXPLORER_TOKEN=$(python -c "import secrets; print(secrets.token_urlsafe(32))") \
uv run mcp-server

每个请求都必须携带令牌:

curl -X POST http://127.0.0.1:8000/mcp \
  -H "Authorization: Bearer $SQL_EXPLORER_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2025-06-18","capabilities":{},"clientInfo":{"name":"curl","version":"1.0"}}}'

任何其他东西都会得到 401,并且请求永远不会到达工具、资源或数据库。

如果没有设置 SQL_EXPLORER_TOKEN,服务器会拒绝启动。 它不会退回“开发模式”,也不会悄悄忽略。在监听一个 127.0.0.1 时,编译时要改安全配置之前,先阅读下面的安全说明。

在把它暴露到网络之前

  • TLS 是不可妥协的。 通过纯 HTTP 发送的 Bearer 令牌,中间任何夹在网络之间的人都可能是明文并重新使用它。请把它放在一个终止 HTTPS 的后者反向代理后面。

  • 一个共享的令牌并不是 OAuth。 根据 MCP 规范,远程服务器要使用 OAuth 2.1,得到用户身份、scopes 和撤销能力。这比一个共享密钥好得多:每个调用者都是同一个人,而轮流用一个密钥,于是所有调用者同时被锁出来。这对于单人尤其可以接受,但对公共部署来说是错误选择。

  • 没有频率限制保障。 这里没有任何机制能阻止某个调用者对着昂贵的查询拼命发送。

  • 不要改动绑定的地址, 默认是 127.0.0.1。如果你改掉它,一定要看到上面的安全风险。

设计说明

为什么 schema 同时提供工具和资源。 dedicated_table 返回结构化后可用于计算的行;schema://customers 返回一个适合人类阅读的页面。相同的信息,两种形态,因为工具和资源的消费方式不同。工具也是更可靠的方式,因为客户端对资源的支持各不相同。

为什么拒绝 SELECT *。 它会被展开成具体列,其中包含被封锁的列,于是授权器会拒绝它。模型只能请求自己想要的列。这会让模型多干一点点,但绝不会造成意外泄漏。

为什么 describe_table 会直接处理参数。 PRAGMA table_info 不能接受绑定参数,所以表名必须先通过真实表列表的校验后再直接拼进语句,专家处理,而不是信任用户的输入。这是一种白名单,而不是 scape 暴露。

注意: PRAGMA table_info 不能接受绑定参数,所以需要把表名插入语句中——但会在拼进去之前先检查它是否出现在真实表清单里。这是白名单,不是转义。

限制

  • 仅支持 SQLite。SQLite 的授权器是它独有的特性,而 PostgreSQL 或 MySQL 没有任何 columnauthorizer 机制,需要另外的方法。

  • 屏蔽的是列而非行,无法配置“只允许用户的那几行”。

  • 5 秒超时是墙钟时间,不是 CPU 时间。

运行测试

uv run pytest -v

三个文件中共有 28 个测试。

  • tests/test_guards.py 覆盖了所有安全护栏:被拒绝的语句、被拒绝的列(包括过滤/不去、只过滤的泄漏攻击)、截断、未知表名、查询超时。

  • tests/test_resources_and_prompts.py 覆盖了 resources 和 prompts,以及被封锁列的 [blocked] 标记,测试只覆盖它们不被修改。

  • tests/test_http_auth.py 覆盖了 HTTP 认证:正确的令牌通过,缺少 header、错误令牌、没有 Bearer 前缀的令牌、不完整的令牌都被拒绝,而且服务器在 http 模式且没有 token 时拒绝启动。每个拒绝都断言没有到达上节点,而不仅是最终状态码 401。

  • tests/conftest.py 如果 sample database 缺失则会创建它,保证测试可以在一份新 clone 中运行。

“防护网测试”和“资源”这两类测试直接调用服务器的函数,而不是通过 MCP 会话,这意味着它们并不会捕获到一个被删除的 @decorator。

Available Tools

4 tools
describe_tableA

Describe the columns of one table: name, type, and whether it is required.

ParametersJSON Schema
NameRequiredDescriptionDefault
tableYes

Output Schema

ParametersJSON Schema
NameRequiredDescription
resultYes

TDQS

A3.6/5.0
Behavior2/5

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

No annotations are provided, so the description must bear the entire burden of disclosing behavioral traits. It does not mention whether the operation is read-only, whether it can fail on non-existent tables, or any side effects. While the tool name and wording imply a harmless read, the description offers no explicit transparency about error handling or assumptions, leaving an agent potentially uninformed.

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?

The description is a single, direct sentence that immediately communicates the core function and output fields. There is no filler or redundancy, and it is appropriately sized for a one-parameter utility tool.

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?

Given the presence of an output schema and the simplicity of the tool, the description covers the essential information: it states the input (table) and the output (column details). It lacks explicit notes on error cases or prerequisites, but for a straightforward metadata query, this is largely sufficient. The existence of an output schema reduces the need to describe return values in detail.

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 schema provides only a parameter name 'table' with type string, and schema description coverage is 0%. The description adds the semantic that the parameter refers to the table whose columns are to be described, which is valuable. However, it does not specify format constraints (e.g., whether it must match an existing table exactly, case sensitivity, or quoting rules). It partially compensates for the schema gap but could be more explicit.

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 states a specific verb (describe) and resource (table columns), and clearly specifies the returned attributes: name, type, and required status. This distinguishes it from sibling tools like list_tables (which lists table names) and run_query (which executes queries). An agent can accurately select this tool for schema inspection without ambiguity.

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

Usage Guidelines3/5

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

The description implies the tool should be used when needing column-level metadata for a specific table, but it does not explicitly state when to prefer it over alternatives or mention exclusions. For example, it does not say 'use run_query for filtering data' or 'use list_tables to see available tables.' The context is clear from the purpose, but explicit routing guidance is missing.

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

list_tablesA

List the names of every table in the database.

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?

With no annotations provided, the description carries the full burden of behavioral disclosure. It states the core function but adds no extra context such as whether the operation is read-only, requires any permissions, or has side effects. While the purpose is clear and it is evidently a passive listing operation, the description does not explicitly guarantee that nothing is mutated or that it is safe to call repeatedly.

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?

The description is a single, front-loaded sentence that states exactly what the tool does with no superfluous content. Every word earns its place, and it is immediately scannable. It achieves maximum conciseness while preserving clarity.

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

Completeness5/5

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

Given that the tool has no parameters and an output schema exists (providing the return format), the description is complete for its purpose. It tells the agent what the tool does (lists table names) and nothing more is required for a successful invocation. There are no configuration details, prerequisites, or edge cases that need to be disclosed.

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 zero parameters, and the schema shows an empty properties object, so there is no parameter information to add. The baseline for 0 parameters is 4, and the description correctly reflects that no arguments are needed. It does not add anything about parameters because there are none, but it also does not create any confusion.

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 ('list') and a precise resource ('names of every table in the database'). It clearly distinguishes itself from siblings: 'ping' (health check), 'describe_table' (specific table schema), and 'run_query' (executes queries). An agent can immediately understand what this tool does and why it differs from the others.

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

Usage Guidelines2/5

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

The description gives no explicit guidance on when to use this tool versus alternatives. It does not mention that this should be used to discover tables before querying or describing them, nor does it note any limitations or exclusions. The usage context is only implied by the sibling names, not stated.

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

pingA

Check that the server is alive.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

Output Schema

ParametersJSON Schema
NameRequiredDescription
resultYes

TDQS

A3.8/5.0
Behavior3/5

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

Annotations are absent, so the description must carry the burden of behavioral disclosure. 'Check that the server is alive' conveys the intent but does not mention whether it performs a network call, what it returns (though output schema exists), or that it is a read-only operation. The description is not misleading, but it adds minimal behavioral detail beyond the basic function.

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?

The description is a single concise clause, front-loading the core purpose with no extraneous words. Every word earns its place, making it highly efficient for an agent to parse.

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

Completeness5/5

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

For a zero-parameter health check with an output schema present, the description is complete. An agent knows that calling the tool requires no inputs and will return a result defined by the schema. No additional context is needed to invoke it correctly.

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 has zero parameters, so the schema carries no parameter information to describe. The baseline is 4, and the description need not add any parameter documentation. It correctly implies no arguments are required.

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?

The description clearly states the verb 'check' and the resource 'server is alive', making the tool's purpose unambiguous. It naturally distinguishes itself from sibling tools (list_tables, describe_table, run_query) which handle database operations, though it does not explicitly name them as alternatives.

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

Usage Guidelines3/5

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

The description provides no explicit guidance on when to use this tool versus the siblings. While it is evident that it is a health check and the siblings are for table/query operations, the description does not state 'use this to verify connectivity before running queries' or any other usage context. It relies on the agent to infer from the tool name and description.

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

run_queryA

Run a read-only SELECT query and return the rows.

At most 500 rows come back; if the query matched more, "truncated" is true and you should add a LIMIT or aggregate instead. Queries running longer than 5 seconds are aborted.

ParametersJSON Schema
NameRequiredDescriptionDefault
sqlYes

Output Schema

ParametersJSON Schema
NameRequiredDescription

No output parameters

TDQS

A3.8/5.0
Behavior4/5

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

With no annotations provided, the description carries the full burden of behavioral disclosure. It discloses that the operation is read-only, caps results at 500 rows, sets the 'truncated' flag when exceeded, and aborts queries longer than 5 seconds. This covers safety, limits, and timeout behavior, which is substantial. It does not describe the exact response structure, but an output schema is present to handle that.

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?

The description is concise and front-loaded: the core action is stated first, followed by two constraint sentences that are directly actionable. Every sentence adds value (payload limit, truncation handling, timeout), with no fluff or repetition. It is well-structured and easy to scan.

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?

The tool has a single parameter, and the description covers key operational aspects: read-only nature, row limit, truncation flag, and timeout. Given an output schema exists (for return format) and the tools is simple, the description is nearly complete. It lacks error-handling details, but those are often covered by the runtime rather than the description. Overall, it adequately equips an agent to invoke it correctly.

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?

Schema description coverage is 0%, so the description must compensate. It implies that the 'sql' parameter is the SELECT query to run, but does not explicitly state 'sql contains the query'. For a single parameter named 'sql', this is reasonably inferred. The description adds minimal further semantics beyond the parameter name, but the connection is clear.

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?

The description clearly states the verb 'Run' and the resource ('read-only SELECT query'), specifying exactly what the tool does. It differentiates itself from siblings like list_tables and describe_table by focusing on arbitrary SELECT queries, though it doesn't explicitly contrast with them. The action and scope are unambiguous.

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

Usage Guidelines3/5

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

The description does not explicitly mention when to use this tool versus alternatives. It does provide practical guidance on handling truncation ('add a LIMIT or aggregate instead') and timeout, which helps the agent use the tool correctly. However, it assumes the agent understands the difference from ping/list_tables/describe_table, providing no explicit selection criteria.

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. 4 tool updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedping
    • First observedrun_query

TDQS

A4/5.0

Scored across 4 tools

Disambiguation5/5

Each tool serves a unique purpose: health check, table enumeration, schema inspection, and query execution. No two tools overlap in functionality, making misselection nearly impossible.

Naming Consistency5/5

All tool names follow a consistent verb_noun pattern (ping, list_tables, describe_table, run_query), using lowercase with underscores throughout. The naming clearly indicates the action and target.

Tool Count5/5

With only 4 tools, the server is tightly scoped for its read-only SQL exploration purpose. Each tool is essential and covers the core workflow without unnecessary bloat.

Completeness4/5

The tool set covers the full exploration lifecycle: check health, list tables, describe schema, and run queries. Minor gaps like database-level metadata or caching are not needed for the stated purpose, so it is nearly complete.

Maintenance

ActivitySlowing
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • F
    license
    Not graded
    quality
    D
    maintenance
    Enables read-only SQL database access for AI assistants, allowing schema exploration and safe query execution without risk of data modification.
    -
  • F
    license
    Not graded
    quality
    D
    maintenance
    Enables AI assistants to query SQL databases safely with read-only access, allowing schema discovery and SELECT queries while blocking writes and DDL operations.
    -
  • A
    license
    Not graded
    quality
    D
    maintenance
    Enables AI assistants to explore and query SQLite databases through read-only tools, with defense-in-depth sandboxing preventing any data modifications.
    MIT