MSSQL spike: candidate A guard using node-sql-parser #315

New issue

Closed

opened 2026-11-10 08:18:20 +00:00 by david · 1 comment

david commented

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

Owner

Summary

Implement candidate A of the read-only guard: node-sql-parser with the transactsql dialect, allowing exactly one SELECT or WITH ... SELECT.

Background

The MSSQL extension's only write block is a T-SQL parser allowlist; it fails closed. The spike scores this candidate against the reject and accept corpora. This code lives only in extensions/mssql/spike/ and is not registered in package.json or as a tool. Install the dependency locally for the spike only if the dependency check (earlier issue) found no blocker.

Depends on: #314

Documentation Required

Vendored under docs/reference/node-sql-parser/ (on main). Read before starting; if absent on your branch, fetch the URL.

https://raw.githubusercontent.com/taozhi8833998/node-sql-parser/master/README.md : README: transactsql dialect ({ database: 'transactsql' }), astify, AST node shapes.

Implementation Details

Create extensions/mssql/spike/candidate-a.mjs exporting validateSql(sql) -> { allowed: boolean, reason?: string }. Parse with Parser.astify(sql, { database: 'transactsql' }). Rules (strict allowlist, fail closed):

Any parse error or thrown exception → reject.
Result must be exactly one statement (the AST may be an array: reject length ≠ 1).
The statement type must be select (CTEs appear on a with property of a select; inspect every CTE body and require each to be a select too).
Reject SELECT ... INTO, EXEC/EXECUTE, and any OPENROWSET/OPENQUERY/OPENDATASOURCE function or table reference anywhere in the AST (walk it recursively, including subqueries and FROM items).
Never rewrite the SQL.
Add the dependency with npm install --no-save node-sql-parser (or document the install in a spike README) so the spike does not touch root package.json.

Acceptance Criteria

candidate-a.mjs exports validateSql with the signature above.
Parse errors and non-single-statement input are rejected.
Non-select statements, SELECT INTO, EXEC and the three OPEN* functions are rejected.
A CTE containing a non-select body is rejected.
The root package.json is unchanged.

Test Plan

Run node -e against a handful of cases: SELECT 1 (allowed), UPDATE t SET a=1 (rejected), SELECT 1; DROP TABLE t (rejected), SELECT * INTO x FROM y (rejected), malformed SQL (rejected). Full scoring comes in the runner issue.

## Summary Implement candidate A of the read-only guard: `node-sql-parser` with the `transactsql` dialect, allowing exactly one `SELECT` or `WITH ... SELECT`. ## Background The MSSQL extension's only write block is a T-SQL parser allowlist; it fails closed. The spike scores this candidate against the reject and accept corpora. This code lives only in `extensions/mssql/spike/` and is not registered in `package.json` or as a tool. Install the dependency locally for the spike only if the dependency check (earlier issue) found no blocker. **Depends on:** #314 ## Documentation Required Vendored under `docs/reference/node-sql-parser/` (on `main`). Read before starting; if absent on your branch, fetch the URL. - https://raw.githubusercontent.com/taozhi8833998/node-sql-parser/master/README.md : README: `transactsql` dialect (`{ database: 'transactsql' }`), `astify`, AST node shapes. ## Implementation Details Create `extensions/mssql/spike/candidate-a.mjs` exporting `validateSql(sql) -> { allowed: boolean, reason?: string }`. Parse with `Parser.astify(sql, { database: 'transactsql' })`. Rules (strict allowlist, fail closed): - Any parse error or thrown exception → reject. - Result must be exactly one statement (the AST may be an array: reject length ≠ 1). - The statement type must be `select` (CTEs appear on a `with` property of a select; inspect every CTE body and require each to be a `select` too). - Reject `SELECT ... INTO`, `EXEC`/`EXECUTE`, and any `OPENROWSET`/`OPENQUERY`/`OPENDATASOURCE` function or table reference anywhere in the AST (walk it recursively, including subqueries and `FROM` items). - Never rewrite the SQL. Add the dependency with `npm install --no-save node-sql-parser` (or document the install in a spike README) so the spike does not touch root `package.json`. ## Acceptance Criteria - [ ] `candidate-a.mjs` exports `validateSql` with the signature above. - [ ] Parse errors and non-single-statement input are rejected. - [ ] Non-`select` statements, `SELECT INTO`, `EXEC` and the three `OPEN*` functions are rejected. - [ ] A CTE containing a non-select body is rejected. - [ ] The root `package.json` is unchanged. ## Test Plan Run `node -e` against a handful of cases: `SELECT 1` (allowed), `UPDATE t SET a=1` (rejected), `SELECT 1; DROP TABLE t` (rejected), `SELECT * INTO x FROM y` (rejected), malformed SQL (rejected). Full scoring comes in the runner issue.

david added this to the MSSQL: M2 - Spike corpora and candidates milestone

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

david added the

feature

label

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

david referenced this issue

2026-11-10 08:18:37 +00:00

MSSQL spike: candidate B guard using the ANTLR4 T-SQL grammar #316

david referenced this issue

2026-11-10 22:59:35 +00:00

315: MSSQL spike: candidate A guard using node-sql-parser #337