feat(mssql): add Microsoft SQL Server integration by waleedlatif1 · Pull Request #6739 · simstudioai/sim

waleedlatif1 · 2026-08-15T22:56:58Z

Summary

Adds a Microsoft SQL Server integration: 6 tools (query, insert, update, delete, execute raw T-SQL, introspect schema), the block, the icon, and the API-route/contract layer
Connections are pinned to the SSRF-validated IP via tedious's options.connector, matching how the PostgreSQL and MySQL tools close the DNS-rebinding window. server stays the original hostname so SNI and certificate validation are unaffected
Introspection reads foreign keys and indexes through the sys.* catalog views rather than INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, which silently drops any FK that references a unique index instead of a constraint
Parameterized statements throughout (@param1, @param2, …); identifiers are bracket-quoted after a strict character check; WHERE clauses are screened for stacked queries, UNION injection, comment truncation, tautologies, WAITFOR, catalog probes, and xp_/sp_ procedures

Named instances are not supported — please read

This branch deliberately does not support instanceName, and that is a security decision rather than a gap.

Resolving a named instance is a SQL Server Browser UDP/1434 request issued against the hostname, outside the connector. It therefore never goes through the pinned address, and node-mssql additionally deletes port whenever instanceName is set — so there is no configuration in which a named instance stays pinned to the IP the SSRF guard approved. Supporting it would reintroduce exactly the rebinding window the connector exists to close.

Named instances now require a static TCP port. Configure the instance with a fixed port and connect to host + port.

If the team would rather take the exposure than the ergonomic cost, this is a one-commit revert — the restriction is confined to createMSSQLConnection and the block's connection subBlocks.

Dependencies

mssql (12.7.0) and @types/mssql (12.3.0) were added to apps/sim/package.json, and bun.lock carries them plus the tedious → @azure/identity tree they pull in (the lockfile diff is entirely that subtree — no unrelated resolutions moved).

Anyone checking this branch out needs to run bun install before it type-checks or runs; without it app/api/tools/mssql/utils.ts reports Cannot find module 'mssql'.

Type of Change

New feature

Testing

Nothing was executed against a live SQL Server. All verification is doc-derived: every endpoint, catalog view, response field, and driver behavior above is cited against the Microsoft Learn system-view docs and the tedious/node-mssql API docs in TSDoc at the call site. Repo gates pass: lint, check:api-validation, tool-metadata:check, integration-catalog:check, and generate-docs reports no drift.

Before merge this wants one pass against a real instance — in particular the introspection result shape on a low-privilege login, where the metadata-visibility filter returns a silently partial result rather than an error.

Checklist

Code follows project style guidelines
Self-reviewed my changes
Tests added/updated and passing
No new warnings introduced
I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)

Add a Microsoft SQL Server block backed by six tools (query, execute, insert, update, delete, introspect), mirroring the existing PostgreSQL and MySQL integrations. Connections go through the `mssql` (Tedious) driver: `connectionTimeout` is top-level while `encrypt`, `trustServerCertificate`, and `instanceName` live under `options`, and `port` is omitted when a named instance is used. Values are bound as `@paramN` via `request.input()`; no user value is interpolated into SQL. Identifiers are bracket-quoted after validation and WHERE clauses run through the shared injection guard. Introspection reads INFORMATION_SCHEMA plus the `sys.indexes` catalog views for tables, columns, primary keys, foreign keys, and indexes. The icon is a placeholder database cylinder drawn with `currentColor` until the real brand mark lands. Requires `bun install` for the new `mssql` / `@types/mssql` deps.

…g reads Tedious exposes `options.connector`, a hook that replaces its own resolve-and-connect path, so the connection can be pinned to the address `validateDatabaseHost` already approved instead of re-resolving the hostname. `server` stays the hostname because tedious derives the TLS `servername` from it independently of the connector, so SNI and certificate validation survive the pin. This brings MSSQL in line with the PostgreSQL and MySQL tools. Named instances are dropped: tedious resolves them with a UDP SQL Server Browser lookup issued outside the connector, and node-mssql deletes `port` whenever `instanceName` is set, so no configuration leaves a named instance pinned. A named instance is reachable through its static TCP port. Introspection fixes: - index key columns now filter on `key_ordinal > 0`; INCLUDEd columns and partitioning columns both report `0` and were being returned as key columns, ordered ahead of the real ones - foreign keys resolve through `sys.foreign_keys` / `sys.foreign_key_columns` rather than `INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS`, whose join to `TABLE_CONSTRAINTS` has no row when a key references a unique index and so dropped the key entirely - `is_unique` is a `bit`, which tedious maps to a boolean, so it is coerced rather than compared - schemas come from `sys.schemas`, which needs only `public` and carries no metadata-visibility caveat The WHERE-clause guard also covers `WAITFOR TIME`, `OPENQUERY`, `OPENXML`, the legacy `master..sys*` compatibility views, and extended and OLE-automation procedures beyond `xp_cmdshell`. Regenerates the docs and catalog artifacts the icon change left stale.

vercel · 2026-08-15T22:57:04Z

The latest updates on your projects. Learn more about Vercel for GitHub.

1 Skipped Deployment

Project	Deployment	Actions	Updated (UTC)
docs	Skipped		Aug 15, 2026 11:19pm

cursor · 2026-08-15T22:57:10Z

PR Summary

Medium Risk
New database tool surface with user-supplied hosts, credentials, and SQL; SSRF pinning and read-only query gating reduce exposure, but execute still runs arbitrary T-SQL with supplied credentials and WHERE validation is defense-in-depth only.

Overview
Adds a Microsoft SQL Server integration end-to-end: workflow block, six authenticated API routes (query, insert, update, delete, execute, introspect), Zod contracts, integration catalog entry, and docs page.

Query vs execute: The Query operation is restricted to read-only SELECT/WITH via layered lexical checks (no comments, no stacked batches, keyword/procedure screening on masked literals). Execute Raw SQL accepts broader T-SQL with a lighter allowlist. Insert/update/delete use parameterized @paramN values and bracket-quoted identifiers; WHERE clauses get shared SQL guards plus T-SQL-specific screens (statement keywords without requiring semicolons, catalog views, xp_/sp_).

Connections: Uses mssql/tedious with validateDatabaseHost and a custom connector that connects to the resolved IP so DNS rebinding is closed like PostgreSQL/MySQL. Named instances are not supported (Browser UDP bypasses the pin); static TCP ports are required. TLS options map to encrypt and trustServerCertificate enums.

Introspection: Schema metadata uses sys.* catalog views for foreign keys and index key columns so FKs to unique indexes and index ordinals are not silently dropped vs INFORMATION_SCHEMA.

Also adds MicrosoftSqlIcon, exports maskSqlStringLiterals and shared insert/update data schemas for reuse, and depends on mssql 12.7.0.

^{Reviewed by Cursor Bugbot for commit 340ca6b. Configure here.}

…ependency tree

waleedlatif1 · 2026-08-15T22:59:54Z

@greptile

waleedlatif1 · 2026-08-15T22:59:55Z

@cursor review

greptile-apps · 2026-08-15T23:00:54Z

Greptile Summary

The PR adds a complete Microsoft SQL Server integration spanning six tools, authenticated API routes, SSRF-pinned connections, schema introspection, block and registry metadata, documentation, and dependencies. The latest fixes strengthen the SELECT-only and WHERE screens, but lexical gaps still permit hidden statements.

Adds query, insert, update, delete, raw execution, and introspection operations.
Pins database sockets to the SSRF-validated address while retaining the original hostname for TLS.
Registers the MSSQL block, tool contracts, generated metadata, icons, documentation, and lockfile dependencies.
Adds MSSQL-specific identifier and statement validation.

Confidence Score: 3/5

The PR is not yet safe to merge because the Query validator can still hide mutations and the Update/Delete WHERE validator can execute an appended SELECT.

The read-only validator misinterprets quotes inside SQL Server bracketed identifiers and then executes the original batch, while the WHERE screen omits SELECT despite directly interpolating accepted conditions into T-SQL batches.

Files Needing Attention: apps/sim/app/api/tools/mssql/utils.ts, apps/sim/app/api/tools/mssql/query/route.ts, apps/sim/app/api/tools/mssql/update/route.ts, apps/sim/app/api/tools/mssql/delete/route.ts

Important Files Changed

Filename	Overview
apps/sim/app/api/tools/mssql/utils.ts	Implements connection pinning, query construction, validation, execution, and introspection, but its lexical masking and statement list leave two reachable statement-screening gaps.
apps/sim/app/api/tools/mssql/query/route.ts	Correctly invokes read-only validation before connecting, although an accepted masking bypass still reaches execution.
apps/sim/app/api/tools/mssql/update/route.ts	Authenticates and closes connections correctly, but returns recordsets produced by a trailing SELECT accepted through the WHERE screen.
apps/sim/app/api/tools/mssql/delete/route.ts	Shares the update route's validated-WHERE execution path and trailing-recordset behavior.
apps/sim/blocks/blocks/mssql.ts	Defines the MSSQL block operations and maps their parameters to the newly registered tools.
apps/sim/package.json	Adds exact MSSQL runtime and type dependencies with corresponding lockfile resolutions.
bun.lock	Contains the requested mssql, type package, tedious, and transitive dependency resolutions.

Sequence Diagram

sequenceDiagram
  participant Workflow
  participant Tool as MSSQL Tool
  participant Route as Authenticated API Route
  participant Guard as Input Validation
  participant SSRF as Host Validation
  participant SQL as SQL Server
  Workflow->>Tool: Resolved block parameters
  Tool->>Route: Internal authenticated request
  Route->>Guard: Validate query or WHERE clause
  Route->>SSRF: Resolve and approve database host
  SSRF-->>Route: Pinned IP address
  Route->>SQL: Connect through pinned socket
  Route->>SQL: Execute validated T-SQL
  SQL-->>Route: Rows and affected counts
  Route-->>Workflow: Normalized tool response

_{Reviews (5): Last reviewed commit: "fix(mssql): reject SQL comments in the r..." | Re-trigger Greptile}

The block label, tool description, and docs all present Query as SELECT-only while the route ran whatever T-SQL it was given, so an agent picking mssql_query because "it is only a SELECT" could delete rows. Screen the statement for mutating keywords with string literals stripped, which also catches the WITH ... DELETE form that a leading-token check would miss. Also switch the tool barrel to absolute imports per the repo convention.

…ss batch gap The local validateWhereClause re-derived an older copy of the shared patterns and scanned raw text, so it missed a bare 1=1 and false-positived on prose in a quoted value. Delegate to validateSqlWhereClause, which masks string literals first, and keep only the SQL Server surfaces it has no reason to know about. T-SQL needs no statement terminator, so every semicolon-anchored stacked-query check reads straight past `id = 1 DROP TABLE dbo.users`. Screen for a bare statement-introducing keyword to close that; word boundaries leave ordinary column names like updated_at and deleted_at untouched. Export maskSqlStringLiterals so the dialect layer masks the same way the shared guard does rather than carrying a weaker single-quote-only copy.

waleedlatif1 · 2026-08-15T23:07:54Z

@greptile

waleedlatif1 · 2026-08-15T23:07:55Z

@cursor review

…d-only path The previous round left two keyword lists maintained separately, and both were short: DBCC, KILL, CHECKPOINT, USE, and DENY were in neither, so `SELECT 1; DBCC SHRINKDATABASE(...)` and `id = 1 DBCC SHRINKDATABASE(...)` both got through. Collapse them into one MSSQL_STATEMENT_KEYWORDS shared by the query and WHERE screens so a keyword cannot be covered in one place and missed in the other, and add the administrative commands. Also reject any second statement after a semicolon in the Query path outright. That closes SELECT 1; <anything> structurally instead of by naming the anything, so the blacklist no longer has to be exhaustive to hold.

waleedlatif1 · 2026-08-15T23:14:05Z

@greptile

waleedlatif1 · 2026-08-15T23:14:06Z

@cursor review

cursor

✅ Bugbot reviewed your changes and found no new issues!

Comment @cursor review or bugbot run to trigger another review on this PR

^{Reviewed by Cursor Bugbot for commit ecb1dac. Configure here.}

A block comment placed inside a keyword splits it as far as a lexical scan is concerned, so keyword coverage cannot settle whether the server rejoins the halves. Refuse comments in the Query path instead of modelling the tokenizer. A SELECT sent through this operation has no need for one, and Execute Raw SQL still accepts them. Masking leaves comment markers intact, so a literal containing -- still passes.

waleedlatif1 · 2026-08-15T23:19:58Z

@greptile

waleedlatif1 · 2026-08-15T23:19:59Z

@cursor review

cursor

✅ Bugbot reviewed your changes and found no new issues!

Comment @cursor review or bugbot run to trigger another review on this PR

^{Reviewed by Cursor Bugbot for commit 340ca6b. Configure here.}

greptile-apps · 2026-08-15T23:24:18Z

+      error:
+        'The Query operation does not accept SQL comments, because a comment can split a keyword. Use the Execute Raw SQL operation if the statement needs one.',
+    }
+  }


Bracketed identifier hides mutations

When a SELECT contains a single quote inside a bracketed identifier followed by a semicolonless mutation, such as SELECT [a'] DELETE FROM dbo.items, maskSqlStringLiterals treats that quote as the start of a string and hides the remaining batch from these checks. The route then executes the original unmasked query, allowing a mutation through the SELECT-only operation.

greptile-apps · 2026-08-15T23:24:18Z

+    MSSQL_STATEMENT_KEYWORDS.test(masked) ||
+    MSSQL_PROCEDURE_PATTERN.test(masked) ||
+    MSSQL_CATALOG_PATTERNS.some((pattern) => pattern.test(masked))


Trailing SELECT bypasses WHERE screening

When an update or delete condition contains a semicolonless trailing SELECT, such as id = 1 SELECT secret FROM dbo.credentials, the shared guard accepts it because it is neither semicolon-prefixed nor a UNION, and this MSSQL keyword screen omits SELECT. The builder interpolates the condition into the batch and the route returns the trailing statement's recordset, exposing rows from an unrelated readable table.

waleedlatif1 added 3 commits August 15, 2026 15:55

feat(mssql): use the SQL Server brand mark on a white tile

cd26a5f

waleedlatif1 requested a review from a team as a code owner August 15, 2026 22:56

chore(mssql): commit the lockfile entries for mssql and its tedious d…

8768457

…ependency tree