DEV Community

Tom Zafred
Tom Zafred

Posted on Edited on

The Polite Catastrophe of 'Read-Only': Connecting Coding Agents to a 300-Table Legacy SQL Database

There is a particular, quiet melancholy in watching a sixty-line script bring a fifteen-year-old enterprise database to its knees without modifying a single byte.

It nearly always begins on a Tuesday morning. An enthusiastic developer downloads an open-source Model Context Protocol (MCP) database wrapper, connects it to a 312-table SQL Server monolith whose original architects retired during the coalition government to press artisanal cider in Somerset, and reassures management that the experiment is entirely harmless. After all, the database login sits strictly inside db_datareader. Every incoming query passes a cheerful regular expression checking that the string begins with SELECT.

Within four minutes, three separate operational incidents unfold in stately, unblemished silence:

  1. Before answering a routine question about specimen rack capacity, the agent pulls INFORMATION_SCHEMA, ingesting 433,876 bytes (108,469 tokens) of Data Definition Language - roughly equivalent to tipping the entire contents of Companies House onto a barrister's desk and asking whether Tuesday's milk bill has been settled.
  2. Processor cores 0 through 7 pin themselves at 100% utilisation because the Node.js driver bound a fourteen-character ASCII barcode as UTF-16 NVARCHAR(4000), prompting SQL Server to up-convert twenty million indexed VARCHAR(50) rows at runtime with polite, unhurried malice.
  3. Most impressively, an unindexed exploratory join across an audit ledger acquires 5,000 shared page locks, triggers lock escalation to a table-wide freeze (TABLOCK S), and locks forty laboratory technicians out of recording -80 deg C cryo-vial movements. Nothing was mutated. Nobody could work.

First-order advice on database MCP tooling - splitting schema dumps into lazy list_tables() and describe_table() calls, enforcing SELECT TOP 100, and revoking write privileges - is already documented across vendor blogs. Those measures prevent juvenile vandalism. What fells mature production estates are second-order database mechanics: archaeological husk tables, multi-turn discovery drag, driver type-precedence traps, and read-only lock escalation.


1. Archaeological Strata and the 12-Turn Guessing Tax

Community tutorials routinely prescribe progressive lazy-loading: call list_tables() first, then describe_table(name) only on relevant entities. On a greenfield SaaS app with twelve tidy tables named users and invoices, this behaves admirably.

Mature institutional databases do not have twelve tidy tables. They resemble geological cliff faces.

Alongside 267 active operational tables, a fifteen-year-old schema preserves 45 fossilised remnants of forgotten consultancy engagements: dbo.tbl_Archive_Dave_DoNotTouch_2011, dbo.tbl_Specimen_Backup_PreMigration_2013, and the ever-reassuring dbo.tmp_Import_LegacyBoxes_FINAL_v2. These tables possess immaculate primary keys, plausible column names, foreign-key constraints, and precisely zero live rows. Nobody drops them because nobody remembers whether an overnight reporting job from 2014 still inspects their metadata.

Handing an agent a flat list_tables() array of 312 cryptic names without row-cardinality telemetry triggers two expensive failures:

  • Siren Joins on Dead Husks: The model picks tbl_Specimen_Backup_PreMigration_2013 because its name looks clearer than tbl_SPEC_Aliquot_Entity_014, writes a syntactically flawless join, receives 0 rows, and burns four turns diagnosing an imaginary outage.
  • Cumulative KV-Cache Replay: Playing battleships with describe_table() across eleven candidate tables emits 40,802 bytes of JSON. Because every subsequent turn replays prior tool outputs across the context window, that polite lazy loop accumulates 346,060 bytes (86,515 tokens) of transcript drag over 12 round-trips.

The Countermeasure: 1-Call 3-Zoom Relational Subgraph Cards

Our gateway compiles metadata from sys.dm_db_partition_stats and sys.foreign_key_columns (or pg_stat_user_tables and pg_constraint on PostgreSQL) into a three-tier hierarchy: Zoom 0 groups active tables by domain while quarantining zero-row husks out of sight; Zoom 1 emits primary keys, foreign-key edges, index flags, row counts, and character encodings (VARCHAR vs NVARCHAR) for an entire connected subgraph in one turn; Zoom 2 exposes view DDL only when explicitly authorised.

# [ZOOM_1_SUBGRAPH: domain=INV_Custody | active_tables=6 | skipped_zero_row_husks=45]
dbo.tbl_INV_Custody_Entity_001 [rows=1250]   :: Entity_001Id(PK:INT), ExternalCode(IX:VARCHAR(50)), VolumeUnit(NVARCHAR(32)), CreatedUtc(IX:DATETIME2(3))
dbo.tbl_INV_Custody_Entity_010 [rows=336041] :: Entity_010Id(PK:INT), ExternalCode(IX:VARCHAR(50)), ParentRefId(FK->Entity_009:IX:INT), VolumeUnit(NVARCHAR(32))
Enter fullscreen mode Exit fullscreen mode

In 1,987 bytes (497 tokens), the agent receives the live join graph, spots multi-hundred-thousand-row tables before writing careless scans, and skips forty-five empty decoys entirely.


2. The CONVERT_IMPLICIT Diplomatic Incident

Suppose the agent now writes a clean, parameterised lookup against an indexed VARCHAR(50) barcode column:

SELECT TOP (25) ExternalCode, VolumeUnit, CreatedUtc
FROM dbo.tbl_INV_Custody_Entity_001
WHERE ExternalCode = @code;
Enter fullscreen mode Exit fullscreen mode

Every security reviewer applauds the parameter binding. Every fan in the server rack immediately spins to takeoff thrust.

JavaScript (node-mssql / tedious), Python (pyodbc), and most generic MCP wrappers bind string parameters as UTF-16 NVARCHAR(4000) whenever explicit column types are omitted. Under SQL Server's type-precedence rules, VARCHAR ranks below NVARCHAR. Forbidden from narrowing UTF-16 input down to 8-bit Code Page 1252, the optimiser rewrites the predicate across the index:

|--Index Scan(OBJECT:([dbo].[tbl_INV_Custody_Entity_001].[IX_ExternalCode]),
     WHERE:(CONVERT_IMPLICIT(nvarchar(50), [ExternalCode], 0) = [@code]))
Enter fullscreen mode Exit fullscreen mode

Rather than narrowing one parameter once, SQL Server widens every indexed row at runtime, degrading a 0.2ms Index Seek into a multi-million-row Index Scan. Conversely, forcing all strings to VARCHAR quietly vandalises UTF-16 scientific units (NCHAR(181) / U+00B5 micro-litres or -80 deg C), breaking ALCOA+ data integrity.

The Countermeasure: Schema-Typed Parameter Coercion

Holding the Zoom-1 column dictionary in memory allows the gateway to inspect target column metadata before dispatch and coerce each parameter to the exact schema encoding and length ceiling:

function coerceBoundParameters(boundParams, columnSchemaMap) {
  const coerced = {};
  for (const [name, spec] of Object.entries(boundParams || {})) {
    const col = columnSchemaMap[spec.column];
    const val = String(spec.value ?? '');
    if (!col || val.length > col.maxLen) throw new Error(`PARAM_BOUNDS_BLOCKED: ${name}`);
    if (!col.isUnicode && /[^\x00-\x7F]/.test(val)) {
      throw new Error(`NON_ASCII_ON_VARCHAR_INDEX_BLOCKED: ${name}`);
    }
    coerced[name] = { sqlType: `${col.isUnicode ? 'NVARCHAR' : 'VARCHAR'}(${col.maxLen})`, value: val };
  }
  return coerced;
}
Enter fullscreen mode Exit fullscreen mode

Index seeks stay 0.2ms index seeks, Unicode columns retain full UTF-16 fidelity, and oversized payloads fail before touching the socket.


3. Why db_datareader Is a Polite Museum Guard

Treating db_datareader (or pg_read_all_data) as a complete security boundary is rather like hiring a courteous museum attendant who forbids visitors from stealing the oil paintings, yet cheerfully permits them to wedge the fire exits shut on a bank holiday.

Even with hardware DENY on INSERT, UPDATE, DELETE, and EXEC, a valid SELECT can still destabilise production via four side channels:

  • Shared-Lock Escalation: Without READ_COMMITTED_SNAPSHOT (RCSI), a poorly selective SELECT acquiring 5,000 shared locks escalates to TABLOCK S, stalling live customer INSERT and UPDATE commits.
  • Ad-Hoc Rowset SSRF: OPENROWSET and OPENDATASOURCE coerce SQL Server into outbound network calls and NTLM relay inside internal VLANs.
  • Recursive CTE Bombs: OPTION (MAXRECURSION 0) passes naive SELECT regexes while pinning CPU cores until timeout.
  • Metadata Secret Scraping: Broad VIEW DEFINITION allows SELECT OBJECT_DEFINITION(object_id) FROM sys.procedures, exposing legacy credentials foolishly hardcoded in 2016.

Architectural Invariant: Pair row-versioned snapshot isolation (READ_COMMITTED_SNAPSHOT ON, or PostgreSQL default_transaction_read_only = on) with SET LOCK_TIMEOUT 2000, SET DEADLOCK_PRIORITY LOW so the agent is always sacrificed ahead of human operators, and pre-flight AST rejection of side-channel constructs.

const BLOCKED_VECTORS = [
  [/;/,                                                         'MULTI_STATEMENT_BATCH_BLOCKED'],
  [/^(?!SELECT\b|WITH\b)/i,                                     'NON_SELECT_ROOT_BLOCKED'],
  [/\bINTO\s+[#@A-Za-z_[\]]/i,                                  'SELECT_INTO_MUTATION_BLOCKED'],
  [/\b(?:OPENROWSET|OPENDATASOURCE|OPENQUERY|xp_dirtree)\b/i,   'EXTERNAL_ROWSET_SSRF_BLOCKED'],
  [/\bWAITFOR\s+(?:DELAY|TIME)\b/i,                             'WAITFOR_POOL_EXHAUSTION_BLOCKED'],
  [/\b(?:OBJECT_DEFINITION|sys\.sql_modules|sp_helptext)\b/i,   'PROC_SECRET_SCRAPE_BLOCKED'],
  [/\bWITH\s*\(\s*(?:TABLOCKX|UPDLOCK|XLOCK|HOLDLOCK|NOLOCK)\b/i,'UNSAFE_LOCK_OR_DIRTY_READ_BLOCKED']
];

function gateReadOnlyQuery(sqlText, params, schemaMap) {
  const clean = sqlText.replace(/--[^\n]*|\/\*[\s\S]*?\*\//g, ' ').replace(/N?'(?:''|[^'])*'/g, "''").trim().replace(/;\s*$/, '');
  for (const [pattern, code] of BLOCKED_VECTORS) {
    if (pattern.test(clean)) return { allowed: false, code };
  }
  if (!/\b(?:TOP\s*\(?\s*\d+|FETCH\s+NEXT\s+\d+\s+ROWS)\b/i.test(clean)) {
    return { allowed: false, code: 'MISSING_ROW_CAP_BLOCKED' };
  }
  return {
    allowed: true,
    preamble: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET LOCK_TIMEOUT 2000; SET DEADLOCK_PRIORITY LOW;',
    bindings: coerceBoundParameters(params, schemaMap)
  };
}
Enter fullscreen mode Exit fullscreen mode

Notice that NOLOCK is banned alongside TABLOCKX. Just as agents fabricate test passes without cryptographic execution receipts, permitting dirty reads (NOLOCK) lets an agent ingest uncommitted numbers that subsequently roll back, polluting its reasoning context with phantom inventory.


4. Measured Scorecard (312-Table Schema & 12-Vector Suite)

Evaluated deterministically on Node.js v24 across a 312-table legacy schema (267 populated tables plus 45 zero-row husks) and 60,000 adversarial pre-flight inspections (5,000 x 12 bypass and type-mismatch vectors):

Architectural Strategy Turns Transcript Payload (Bytes / Tokens) Zero-Row Husks Exposed Pre-Flight Gate (p50 / p95) Production Lock & Scan Safety
Raw INFORMATION_SCHEMA Dump 1 433,876 B / 108,469 tok 45 (100% noise) None Vulnerable (CONVERT_IMPLICIT + TABLOCK S)
Naive Lazy describe_table Loop 12 346,060 B / 86,515 tok (cumulative) 45 (3 queried) None Vulnerable (OPENROWSET, CTE, WAITFOR open)
3-Zoom Subgraph + Typed AST Gateway 1 1,987 B / 497 tok (-99.5%) 0 (quarantined) 0.0007ms / 0.0011ms Enforced (VARCHAR coerced, LOCK_TIMEOUT 2000, 12/12 halted)

5. Production Scope Boundary

Connecting coding agents to a decade-old relational estate requires treating the MCP server as a typed, isolation-aware firewall: quarantine zero-row schema archaeology at Zoom 0, coerce VARCHAR vs NVARCHAR bindings from column metadata, enforce RCSI with DEADLOCK_PRIORITY LOW, and authenticate remote gateways via OAuth 2.1 PKCE rather than static plaintext bearer keys.

  • Production Architecture & Gateway Engineering: Engineered by Tom Zafred at Biomed Systems Limited (UK), where these SQL Server isolation, UTF-16 scientific-unit, and zero-lockout controls underpin our regulated biobanking LIMS (biobanking.co.uk) alongside turnkey read-only SQL Server and PostgreSQL MCP gateways for enterprise engineering teams.
  • Open-Source Reference Runtime: RFC-001: Autonomous Developer Workbench, 3-Zoom SQLite Vault & Receipt Engine (workbench-guard)

If your own production database still harbours a cherished DoNotDelete_2011 fossil table or a memorable NOLOCK misadventure, the comments below are open for archaeological comparison.

Top comments (6)

Collapse
 
x1y1co profile image
X1Y1 •

One Postgres caveat on the invariant: default_transaction_read_only = on is a default, not a boundary. Any session can run SET default_transaction_read_only = off or BEGIN READ WRITE, so it only stops an agent that isn't trying. The grants on the role are what actually hold.

For the lock problem, the cheaper fix, if the estate has one, is pointing the agent at a readable secondary. SQL Server maps every query on an Always On readable secondary to snapshot isolation, even when another isolation level is set explicitly, so the agent's shared locks never reach the primary. The cost is some replication lag, which matters a lot less for "how many racks are free" than forty technicians locked out.

Collapse
 
tomzafred profile image
Tom Zafred •

On PostgreSQL, default_transaction_read_only = on is indeed a USERSET session default rather than a hard boundary, since a session can issue SET default_transaction_read_only = off or BEGIN READ WRITE. The real perimeter is least-privilege role grants (GRANT SELECT on a dedicated role with PUBLIC revoked), role-pinned statement_timeout and lock_timeout, plus rejecting session-state mutators in the gateway.

Routing agents to an Always On readable secondary is the cleanest lock isolation where Availability Groups exist, since SQL Server forces secondary reads into SNAPSHOT isolation. Two legacy-estate constraints still bite in practice: single-instance Standard Edition estates lack AG secondaries, and CONVERT_IMPLICIT scans or replication lag on live custody checks still require typed parameter coercion and RCSI with DEADLOCK_PRIORITY LOW.

Collapse
 
mariosfong profile image
Mario Fang •

The lock escalation part is what nobody believes until it happens to them. I run an ERP on SQL Server, a couple hundred tables, and a read-only reporting login took purchasing down for about 20 minutes one morning. No writes at all, just a join across an audit table that grabbed enough page locks to escalate and froze the people entering orders.

What fixed it for us: reports moved to a readable secondary, and that login got a hard MAXDOP 2 plus a 30 second query timeout. READ_COMMITTED_SNAPSHOT helped with the reader/writer blocking too, but it just moves the pain into tempdb, so we watch version store growth now instead of lock waits.

The discovery tax is the part I still handle by hand. Curious whether you found anything better than curating the describe_table list yourself.

Collapse
 
tomzafred profile image
Tom Zafred •

Offloading reports to a readable secondary with snapshot isolation and tempdb monitoring is the cleanest way to stop reader freezes; a Resource Governor workload group can also enforce MAX_DOP (overriding query hints) and REQUEST_MAX_CPU_TIME_SEC server-side.

To automate the describe_table allowlist on a multi-hundred-table ERP, run a daily metadata job rather than granting extra privileges to db_datareader (these views require VIEW SERVER STATE, VIEW DATABASE STATE, and VIEW DEFINITION):

SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS tbl, SUM(ps.row_count) AS rows
FROM sys.tables t JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id AND ps.index_id IN (0, 1)
WHERE t.is_ms_shipped = 0 AND (EXISTS (
    SELECT 1 FROM sys.dm_db_index_usage_stats u WHERE u.object_id = t.object_id AND u.database_id = DB_ID()
    AND COALESCE(u.user_seeks, 0) + COALESCE(u.user_scans, 0) + COALESCE(u.user_lookups, 0) + COALESCE(u.user_updates, 0) > 0)
  OR EXISTS (SELECT 1 FROM sys.sql_expression_dependencies d WHERE d.referenced_id = t.object_id))
GROUP BY t.schema_id, t.name HAVING SUM(ps.row_count) > 0 ORDER BY rows DESC;
Enter fullscreen mode Exit fullscreen mode

Because sys.dm_db_index_usage_stats is replica-local (omitting primary writes if run only on the secondary) and resets on restarts, pairing it with sys.sql_expression_dependencies retains proc- and view-referenced tables; caching 1-hop sys.foreign_key_columns subgraphs alongside it eliminates most describe_table round-trips.

Collapse
 
skillselion profile image
Skillselion •

Running the gate code from section 3 locally shows a bypass. The comment stripper runs before the string-literal stripper, so a -- inside a string hides the rest of the line from the gate. SELECT TOP 1 a FROM t WHERE b = '--'; DROP TABLE t comes back allowed: true, with the cleaned text ending at WHERE b = '. SQL Server reads '--' as a string and would run the DROP, assuming the original text is what gets executed. Separately, WITH c AS (SELECT TOP 1 a FROM t) DELETE FROM c passes both the root check and the TOP check, because nothing blocks DELETE. Your DENY grants stop both, but the text says AST rejection and the code is regexes. Would you tokenize strings and comments in one pass, or describe it as a lint layer on top of permissions?

Collapse
 
tomzafred profile image
Tom Zafred •

Both bypasses are real in that abridged snippet: stripping -- comments before '...' literals lets '--'; DROP TABLE t slip through, and WITH ... DELETE passes WITH\b because DML/DDL verbs (INSERT, UPDATE, DELETE, MERGE, DROP, ALTER, TRUNCATE, CREATE, EXEC) are missing from the blocklist.

Calling a condensed regex pre-filter an "AST" in Section 3 was an overstatement. Even single-pass regex alternation misses T-SQL nested /* /* */ */ comments and quoted identifiers ([...] / "..."). In production, I pair a real SQL lexer/AST parser (ScriptDom or libpg_query enforcing a single SelectStatement root) strictly as a lock, DoS, and intent lint layer on top of db_datareader and DENY grants, never instead of them. I will patch the Section 3 snippet and wording.