# dsh-plugin-sql-security-validate

[English](README.md) | [简体中文](README.zh.md)

**AI SQL permission guard** for DeepSeek Harness — a general-purpose permission
layer for agent-driven database access.

It intercepts SQL an agent generates that exceeds its permissions and **refuses
to execute it** rather than silently rewriting it, returning a structured
permission reason to the model. The agent can then drop the offending column,
switch to an allowed aggregation, or explain the limitation to the user in
plain language.

- Plugin ID: `dsh-plugin-sql-security-validate`
- Framework: DeepSeek Harness (Cordis)
- Tested against: DSH 0.2.x
- License: MIT

---

## 1. What it solves

| Problem | What this plugin does |
| --- | --- |
| An agent writes SQL that reads tables or columns it should not | Parses every resource through a real AST; a policy hit refuses execution |
| The error text is vague, so the question-answering loop breaks | The refusal carries `SQL_PERMISSION_DENIED` plus the exact refused resources |
| Silent rewriting makes the agent believe the data is complete | **Never rewrites SQL**; a refusal is explicit |
| Light personal use versus strict enterprise deployment | Every hardening option is off by default; enable what production needs |
| Another plugin overrides the refusal later in the chain | Optional monotonic guard `ctx.tools.guard()` makes the refusal final |

## 2. Scope

1. This plugin guards **only SQL generated by an AI agent**, reaching the
   database through a registered tool.
2. Scope discovery is a first-class concern: an agent that cannot see which
   tables exist cannot write a valid query, so `DESCRIBE` / `SHOW CREATE TABLE`
   are **governed per table** rather than refused outright. You can inspect the
   shape of a table you are allowed to read, and not one you are not.
3. It does **not** handle MCP service-layer, network-layer, or database-account
   authentication — those belong to service security.
4. It does **not** defend against platform-level routes that reach the database
   without passing through a registered tool.

## 3. Installation

### From the plugin market

Install the bundle from the DSH Web GUI under **Plugin Market** or
**Settings → Plugins**, or ask an agent in a session:

```
Install the bundle from C:\path\to\dsh-plugin-sql-security-validate
```

The bundle ships a `cordis.patch.yml` that inserts one row named
`sql-security-validate`; no other configuration is required to start.

### From source

```bash
git clone <repository-url>
cd dsh-plugin-sql-security-validate
node tests/unit.mjs          # verify the checkout is intact
```

Then install the directory as a bundle in DSH.

## 4. Quick start

1. Enable the plugin (it is enabled on install).
2. Open **Settings → SQL 权限校验**.
3. Pick a mode:
   - **Blacklist** (default): everything runs except what you list. Nothing is
     needed to get started; add the tables and columns that must never be read.
   - **Whitelist**: only what you list runs; everything else is refused. No
     external service required — the list lives in this panel.
4. Point the agent at your database and watch the per-turn card at the end of
   each turn: it reports what was refused in that turn and which policy entry
   matched.

### Whitelist example

| Entry | Meaning |
| --- | --- |
| `demo.user` | The `user` table in `demo` — recommend always qualifying |
| `demo.orders` | Same, another table |
| `demo.user_private.phone` | A single column, when only that column may be read |

> Write `database.table`, not a bare `user`. A bare name matches the same-named
> table in **every** database. See §7.

## 5. Configuration

All fields are editable from the settings panel; the same values can be written
into `cordis.patch.yml`.

### Core

| Field | Default | Meaning |
| --- | --- | --- |
| `enable` | `true` | Master switch |
| `securityMode` | `blacklist` | `blacklist` or `whitelist_emulate` |
| `enableStrictGuardMode` | `false` | Install the monotonic guard so a refusal cannot be overridden by another plugin |
| `allowSchemaDiscovery` | `true` | Permit table-less discovery (`SHOW TABLES`, `SHOW DATABASES`). Turn off where table names are confidential |
| `failOnParseError` | `false` | Refuse statements the parser cannot interpret. Safer, but rejects valid legacy MySQL forms |
| `strictDatabaseQualification` | `false` | Whether a bare whitelist grant may reach a qualified resource (see §7) |

### Policy

| Field | Meaning |
| --- | --- |
| `localBlacklist.tables` / `.columns` | Entries refused in blacklist mode |
| `localWhitelist.tables` / `.columns` | Grants allowed in whitelist mode |
| `targetToolPrefixes` | Only tools starting with these prefixes are inspected; MCP tool names are `mcp__<server>__<tool>` |
| `includeNonMcpSqlTools` | Also inspect tools outside those prefixes |
| `sqlParamKeys` | Which argument keys are parsed as SQL |

### Remote policy (optional)

Whitelist mode works fully offline. A central policy service is optional:

| Field | Default | Meaning |
| --- | --- | --- |
| `remotePolicy.enabled` | `false` | Fetch policy from a service |
| `remotePolicy.policyApiUrl` | `''` | Endpoint |
| `remotePolicy.policyApiToken` | `''` | Bearer token |
| `remotePolicy.cacheTtlMs` | `30000` | Cache lifetime |
| `remotePolicy.apiTimeoutMs` | `3000` | Request timeout; a failure **fails closed** |

### Audit

| Field | Default | Meaning |
| --- | --- | --- |
| `auditEnable` | `true` | Record decisions |
| `audit.maxRecords` | `5000` | In-memory ring size |
| `audit.redactSql` | `false` | Replace string literals with `'***'` |
| `audit.logPath` | `''` | Append-only JSONL path; empty means `$DSH_HOME/sql-security-validate/audit.jsonl` |

## 6. How a decision is reached

```
Agent emits a tool call
  → pre-execute hook (or the guard, in strict mode)
    → collect candidate SQL from the configured argument keys
      → parse each through the AST, collecting every table and column
        → merge local policy (and remote, if enabled)
          → a refused resource produces SQL_PERMISSION_DENIED and the call does not run
```

Two properties are deliberate:

- **Nothing is rewritten.** The SQL either runs as written or is refused. A
  rewritten query would let the agent believe its result set is complete.
- **The refusal is informative.** The reason lists the refused tables and
  columns, the security mode, and the policy source, so the model can correct
  itself instead of retrying blindly.

On what a statement is judged by:

- **A `*` projection is refused when a column rule governs one of the tables it
  covers.** There is no column list to compare, so the statement cannot be shown
  to exclude the forbidden column; the refusal names it as `<table>.*`. `COUNT(*)`
  is not this case — it is an aggregate and reveals no column value.
- **Leading comments are stripped before judging.** A statement may open with
  `--`, `#` or `/* */`; the comment carries no semantics and must not change the
  verdict.

## 7. Known boundaries

These are design decisions, not defects. `SECURITY.md` explains the reasoning.

1. **Enforcement scope** — registered tools only; no MCP/network/account-layer
   authentication.
2. **Schema discovery is on by default** — `SHOW TABLES` / `SHOW DATABASES`
   reveal names. `DESCRIBE` / `SHOW CREATE TABLE` remain per-table regardless.
3. **Unparseable statements pass through** by default (`failOnParseError:
   false`).
4. **Audit SQL is plaintext** by default (`audit.redactSql: false`).
5. **A bare whitelist grant spans databases** — `user` matches `demo.user` and
   `mysql.user`. Write `database.table`, or set
   `strictDatabaseQualification: true` to restrict a bare grant to bare
   references.

## 8. Vendored parser

`node-sql-parser` 5.4.0 lives under `vendor/` (2.77 MB, Apache-2.0) instead of
being an npm dependency. The published package is 88 MB, of which about 87 MB is
browser bundles, alternate builds, and source maps that never load at runtime.
Vendoring keeps the plugin at 2.76 MB, pins the exact parser version inside the
repository so it is auditable, and lets the plugin install offline.

The trade-off: **upstream security fixes do not arrive automatically.** See
[`SECURITY.md`](SECURITY.md#third-party-components) for the re-vendoring
procedure.

## 9. Development

```bash
pnpm install                         # only needed for the engine and panel suites
node tests/unit.mjs                  # parser, policy, remote client (49)
node tests/report-vectors.mjs        # first-round security vectors (15)
node tests/subquery-vectors.mjs      # subquery positions (26)
node tests/qualification-vectors.mjs # grant qualification (10)
node tests/engine.mjs                # apply() wiring, dual engines (28)
node tests/panel.mjs                 # settings panel and turn card (15)
```

143 assertions total. `npm test` runs all six in order.

The parser, policy, and vector suites have no dependencies — they run on a bare
clone. `engine.mjs` imports `src/index.js`, which needs
`@deepseek-ai/schemastery`, so `pnpm install` is required before the last two.

### Layout

| Path | Purpose |
| --- | --- |
| `src/index.js` | Host half: config, dual engines, audit route, turn tracking |
| `src/sql-parse.js` | AST-driven resource extraction |
| `src/policy.js` | Blacklist/whitelist matching and grant semantics |
| `src/audit.js` | Bounded ring buffer plus append-only JSONL |
| `src/client.js` | Settings panel and per-turn interception card |
| `vendor/node-sql-parser/` | Vendored parser (see its README) |

## 10. License

MIT — see [`LICENSE`](LICENSE).

The vendored `node-sql-parser` is Apache-2.0 and `big-integer` is MIT; both
licenses ship alongside their code under `vendor/` and are not relicensed by
this project.