MSSQL: mssql_table_schema tool (parameterised) #328

New issue

Closed

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

david commented

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

Owner

Summary

Register mssql_table_schema: given table_name and optional schema_name (default dbo), return column name, type, nullability, length and a primary key flag using a parameterised query.

Background

Schema tools use fixed, parameterised queries, never string concatenation, and do not pass through the guard. The mssql driver supports typed inputs via request.input(name, type, value). INFORMATION_SCHEMA shows only objects the login can access. Blocked until the Stage 1 gate (#318) passes. TDD with the injected fake pool.

Depends on: #327

Documentation Required

docs/reference/mssql/ — https://raw.githubusercontent.com/tediousjs/node-mssql/master/README.md : node-mssql README, "Input parameters" (request.input, sql.NVarChar) and executing queries. Vendored on main; fetch the URL if absent.

Implementation Details

In the tools module register mssql_table_schema with parameters: Type.Object({ table_name: Type.String(...), schema_name: Type.Optional(Type.String(...)) }). Constant SQL joining INFORMATION_SCHEMA.COLUMNS to primary key info (INFORMATION_SCHEMA.TABLE_CONSTRAINTS + KEY_COLUMN_USAGE where CONSTRAINT_TYPE = 'PRIMARY KEY'), filtered with WHERE c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @table ORDER BY c.ORDINAL_POSITION, selecting COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH and an is_primary_key bit. Bind @schema (default 'dbo') and @table as NVarChar inputs. Never concatenate user input into SQL. Empty result returns a clear "table not found or not accessible" message. Audit the call.

Acceptance Criteria

The SQL text is constant and contains no interpolated user input.
table_name and schema_name are passed as bound parameters; schema_name defaults to dbo.
Output includes column, type, nullability, length and primary key flag.
An input such as x'; DROP TABLE y;-- is passed only as a parameter value (test asserts the SQL string is unchanged).
An unknown table returns a clear message, not an error.

Test Plan

node --test extensions/mssql/tools.test.ts: fake request records input(...) calls and the SQL; assert default schema, explicit schema, injection-style input as a parameter, SQL constant across calls, and empty-result message.

## Summary Register `mssql_table_schema`: given `table_name` and optional `schema_name` (default `dbo`), return column name, type, nullability, length and a primary key flag using a parameterised query. ## Background Schema tools use fixed, parameterised queries, never string concatenation, and do not pass through the guard. The `mssql` driver supports typed inputs via `request.input(name, type, value)`. `INFORMATION_SCHEMA` shows only objects the login can access. **Blocked until the Stage 1 gate (#318) passes.** TDD with the injected fake pool. **Depends on:** #327 ## Documentation Required - `docs/reference/mssql/` — https://raw.githubusercontent.com/tediousjs/node-mssql/master/README.md : node-mssql README, "Input parameters" (`request.input`, `sql.NVarChar`) and executing queries. Vendored on `main`; fetch the URL if absent. ## Implementation Details In the tools module register `mssql_table_schema` with `parameters: Type.Object({ table_name: Type.String(...), schema_name: Type.Optional(Type.String(...)) })`. Constant SQL joining `INFORMATION_SCHEMA.COLUMNS` to primary key info (`INFORMATION_SCHEMA.TABLE_CONSTRAINTS` + `KEY_COLUMN_USAGE` where `CONSTRAINT_TYPE = 'PRIMARY KEY'`), filtered with `WHERE c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @table ORDER BY c.ORDINAL_POSITION`, selecting `COLUMN_NAME`, `DATA_TYPE`, `IS_NULLABLE`, `CHARACTER_MAXIMUM_LENGTH` and an `is_primary_key` bit. Bind `@schema` (default `'dbo'`) and `@table` as NVarChar inputs. Never concatenate user input into SQL. Empty result returns a clear "table not found or not accessible" message. Audit the call. ## Acceptance Criteria - [ ] The SQL text is constant and contains no interpolated user input. - [ ] `table_name` and `schema_name` are passed as bound parameters; `schema_name` defaults to `dbo`. - [ ] Output includes column, type, nullability, length and primary key flag. - [ ] An input such as `x'; DROP TABLE y;--` is passed only as a parameter value (test asserts the SQL string is unchanged). - [ ] An unknown table returns a clear message, not an error. ## Test Plan `node --test extensions/mssql/tools.test.ts`: fake request records `input(...)` calls and the SQL; assert default schema, explicit schema, injection-style input as a parameter, SQL constant across calls, and empty-result message.

david added this to the MSSQL: M7 - Tools milestone

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

david added the

feature

label

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

david referenced this issue

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

MSSQL: index.ts wiring (load env, refuse on invalid limits, pool, shutdown) #329

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 with read-only credentials, a minimal guard and a write-access warning. Stage 2 is unblocked. The scope of this issue is unchanged. The schema tools stay as fixed, parameterised queries that do not pass through the guard.

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 with read-only credentials, a minimal guard and a write-access warning. Stage 2 is unblocked. The scope of this issue is unchanged. The schema tools stay as fixed, parameterised queries that do not pass through the guard.

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:33:48 +00:00

328: MSSQL: mssql_table_schema tool (parameterised) #352

david closed this issue

2026-11-11 00:33:50 +00:00

david commented

2026-11-11 00:33:50 +00:00

Author

Owner