MSSQL: mssql_query and mssql_query_one tools #326

New issue

Closed

opened 2026-11-10 08:21:08 +00:00 by david · 2 comments

david commented

2026-11-10 08:21:08 +00:00

Owner

Summary

Background

extensions/postgres/index.ts exports registerDbTools(pi, pool) and registers tools with pi.registerTool({ name, label, description, parameters: Type.Object({ sql: Type.String(...) }), async execute(_id, params) {...} }) (Type from typebox), returning { content: [{type:"text", text}], details }. Tests inject a fake pool. Mirror that for mssql with the mssql_ prefix (avoids clashing with the postgres db_ tools). A rejected query must never reach the pool. Every rejected and executed query is audited. Blocked until the Stage 1 gate (#318) passes. TDD.

Depends on: #325 (also #322 guard and #323 audit)

Documentation Required

docs/reference/mssql/ — https://raw.githubusercontent.com/tediousjs/node-mssql/master/README.md : node-mssql README (pool and request usage). Vendored on main; fetch the URL if absent.

Implementation Details

Create extensions/mssql/tools.ts exporting registerMssqlTools(pi, pool, { settings, audit }). For mssql_query, execute does: validateSql(sql); if rejected → audit {outcome:"rejected", reason} and return a tool error/text containing the reason (do not run anything); else runQuery(pool, sql, { maxRows, timeoutMs }), audit {outcome:"executed", rowCount, durationMs, truncated}, return rows as pretty JSON, with a clear note such as Results truncated at N rows (MSSQL_MAX_ROWS). when truncated, and details: { rowCount, truncated }. On driver or timeout error, audit {outcome:"error", reason} (message without credentials) and return the error text. mssql_query_one uses the identical path and returns the first row as JSON, or (no rows returned). Share one helper so both tools cannot diverge. Execute the approved SQL text unchanged.

Acceptance Criteria

A rejected query never calls pool.request()/pool.query() (fake pool asserts zero calls).
Rejections and executions are each written to the audit log with the specified fields.
mssql_query returns JSON rows and includes a truncation note when truncated is true.
mssql_query_one returns the first row, or (no rows returned).
Errors are reported without the password or connection URL.
Both tools share the guard, runner and audit path.

Test Plan

node --test extensions/mssql/tools.test.ts with a fake pi (captures registerTool calls) and fake pool: allowed query, rejected query (assert zero pool calls), truncation note, empty result for query_one, driver error, audit entries recorded.

## Summary Register `mssql_query` and `mssql_query_one`: guard, then runner, then audit, then a result with a truncation note. ## Background `extensions/postgres/index.ts` exports `registerDbTools(pi, pool)` and registers tools with `pi.registerTool({ name, label, description, parameters: Type.Object({ sql: Type.String(...) }), async execute(_id, params) {...} })` (`Type` from `typebox`), returning `{ content: [{type:"text", text}], details }`. Tests inject a fake pool. Mirror that for mssql with the `mssql_` prefix (avoids clashing with the postgres `db_` tools). A rejected query must never reach the pool. Every rejected and executed query is audited. **Blocked until the Stage 1 gate (#318) passes.** TDD. **Depends on:** #325 (also #322 guard and #323 audit) ## Documentation Required - `docs/reference/mssql/` — https://raw.githubusercontent.com/tediousjs/node-mssql/master/README.md : node-mssql README (pool and request usage). Vendored on `main`; fetch the URL if absent. ## Implementation Details Create `extensions/mssql/tools.ts` exporting `registerMssqlTools(pi, pool, { settings, audit })`. For `mssql_query`, `execute` does: `validateSql(sql)`; if rejected → audit `{outcome:"rejected", reason}` and return a tool error/text containing the reason (do not run anything); else `runQuery(pool, sql, { maxRows, timeoutMs })`, audit `{outcome:"executed", rowCount, durationMs, truncated}`, return rows as pretty JSON, with a clear note such as `Results truncated at N rows (MSSQL_MAX_ROWS).` when truncated, and `details: { rowCount, truncated }`. On driver or timeout error, audit `{outcome:"error", reason}` (message without credentials) and return the error text. `mssql_query_one` uses the identical path and returns the first row as JSON, or `(no rows returned)`. Share one helper so both tools cannot diverge. Execute the approved SQL text unchanged. ## Acceptance Criteria - [ ] A rejected query never calls `pool.request()`/`pool.query()` (fake pool asserts zero calls). - [ ] Rejections and executions are each written to the audit log with the specified fields. - [ ] `mssql_query` returns JSON rows and includes a truncation note when `truncated` is true. - [ ] `mssql_query_one` returns the first row, or `(no rows returned)`. - [ ] Errors are reported without the password or connection URL. - [ ] Both tools share the guard, runner and audit path. ## Test Plan `node --test extensions/mssql/tools.test.ts` with a fake pi (captures `registerTool` calls) and fake pool: allowed query, rejected query (assert zero pool calls), truncation note, empty result for `query_one`, driver error, audit entries recorded.

david added this to the MSSQL: M7 - Tools milestone

2026-11-10 08:21:08 +00:00

david added the

feature

label

2026-11-10 08:21:08 +00:00

david referenced this issue

2026-11-10 08:21:21 +00:00

MSSQL: mssql_list_tables tool #327

david commented

2026-11-10 23:44:17 +00:00

Author

Owner

Update: the "Blocked until the Stage 1 gate (#318) passes" note is superseded. The gate resolved as STOP, and docs/adr/0002-mssql-read-only-credentials-with-minimal-guard.md replaced the parser design. Stage 2 is unblocked. The flow is unchanged (guard, runner, audit), but the guard (#322) is now a minimal accident check rather than a parser allowlist. Read "guard" and "rejected" with that meaning. A rejected query still must not reach the pool.

Update: the "Blocked until the Stage 1 gate (#318) passes" note is superseded. The gate resolved as STOP, and `docs/adr/0002-mssql-read-only-credentials-with-minimal-guard.md` replaced the parser design. Stage 2 is unblocked. The flow is unchanged (guard, runner, audit), but the guard (#322) is now a minimal accident check rather than a parser allowlist. Read "guard" and "rejected" with that meaning. A rejected query still must not reach the pool.

david referenced this issue

2026-11-10 23:46:59 +00:00

MSSQL spike: write RESULT.md, apply the gate, open the Stage 1 PR #318

david referenced this issue

2026-11-11 00:20:08 +00:00

326: MSSQL: mssql_query and mssql_query_one tools #350

david closed this issue

2026-11-11 00:20:10 +00:00

david commented

2026-11-11 00:20:10 +00:00

Author

Owner