Guard SQL

Raw-SQL content-safety primitive. Gates the residual SQL surface the b.sql builder cannot structurally protect — the operator escape hatches that take a SQL string verbatim: whereRaw / setRaw / fromRaw fragments, operator-supplied single-statement SQL, and migration scripts. Everything b.sql composes by construction (column-membership gate, ?-placeholder binding, dialect-final quoting) is already injection-safe; this guard defends only the bytes a human handed the framework as opaque SQL.

## Tokenizer-first, never regex-over-raw

Every detector runs on a NORMALIZED token stream, not the raw string. Naive regex over raw SQL is bypassable in three ways this guard closes:

1. Comment splitting — LOAD/** + **/_FILE reads as LOAD_FILE to MySQL but LOAD _FILE to a raw-regex scan. The normalizer strips comments and collapses the residue so the keyword detector sees the post-comment token. 2. String-literal smuggling — a keyword inside 'pg_read_file' is data, not a call; the normalizer masks literal + dollar- quoted ($tag$...$tag$) spans so a detector never fires on bytes the engine treats as a value. 3. Encoding bypass — invalid / non-shortest UTF-8 lets a multi- byte sequence decode to an ASCII metacharacter past a byte- level filter (the libpq client-encoding class, CVE-2025-1094, CVSS 8.1, actively exploited via BeyondTrust, public PoC). The encoding gate refuses the bytes before any token scan.

Pipeline: (1) encoding gate → (2) normalizer (comment strip + literal/dollar-quote mask + intra-keyword-comment collapse) → (3) keyword + structural detectors on the normalized stream.

## Context modes

The same byte string means different things depending on where it was handed in, so the gate takes a ctx.mode:

- fragment (default; whereRaw / setRaw / fromRaw) — the bytes must be a single value expression. A top-level ;, any statement-introducing verb, an embedded string literal, or any dangerous token refuses. This is the strictest context because the fragment lands inside a query the framework built. - operator-sql — one complete statement. Stacked statements refuse; the verb may be any single read or write. - migration — a multi-statement DDL script. Multiple statements and comments are permitted (and audited); each statement is re-classified and only the DDL-verb allowlist (CREATE / ALTER / CREATE INDEX / DROP) plus reads pass. The OS-reach floor (file / exec / FDW / privilege-pivot / extension / attach) still refuses — a migration never needs COPY ... PROGRAM or load_extension.

## Universal refuse floor (every profile, like the always-throw classes in guard-filename)

These classes refuse under every profile including permissive — they are structurally unambiguous OS-reach / data-exfiltration / statement-smuggling, and no profile downgrades them:

- Stacked top-level ; (a second statement past the first). - Comment smuggling — an unterminated /* and the MySQL executable-comment form /*!.... - Embedded string literal in fragment mode. - Postgres OS reach — COPY ... PROGRAM, COPY TO/FROM , lo_import / lo_export / lo_get / lo_put / loread / lowrite, pg_read_file / pg_read_binary_file / pg_ls_* / pg_stat_file, adminpack pg_file_write / pg_file_unlink / pg_file_rename, dblink* / postgres_fdw / CREATE SERVER / CREATE SUBSCRIPTION, CREATE EXTENSION, CREATE [OR REPLACE] FUNCTION ... LANGUAGE (plperlu / plpython3u / c), DO blocks, SET ROLE / SET SESSION AUTHORIZATION / SET search_path, ALTER SYSTEM. - SQLite OS reach — ATTACH / DETACH DATABASE, load_extension, PRAGMA writable_schema, PRAGMA trusted_schema=ON, PRAGMA key / PRAGMA rekey, fts3_tokenizer, writefile / readfile / edit, writes to sqlite_master / sqlite_*. - MySQL OS reach — LOAD_FILE, INTO OUTFILE / INTO DUMPFILE, LOAD DATA [LOCAL] INFILE, CREATE FUNCTION ... SONAME, sys_exec / sys_eval / do_system, SET GLOBAL of a sensitive variable (general_log / local_infile / log_bin_trust_function_creators / secure_file_priv). - Cross-dialect — time-based blind probes (SLEEP / pg_sleep / WAITFOR DELAY / BENCHMARK / GET_LOCK) and a set-operation (UNION / INTERSECT / EXCEPT) inside a predicate fragment.

## Profiles

strict (default for request-path whereRaw) refuses the whole floor plus non-UTF-8 plus schema-recon reads (information_schema / performance_schema / mysql. / pg_catalog writes). balanced refuses the RCE / file / exec / FDW / privilege-pivot / stacked / embedded-literal / comment / invalid-encoding classes and audits schema-recon + time-based. permissive audits the keyword families but STILL hard-refuses the stacked-statement, invalid-encoding, and irreducible OS-reach floor — the structurally-unambiguous classes never relax.

## Compliance postures + audit

hipaa / pci-dss / gdpr / soc2 all map to the strict floor. Every decision emits a signed audit entry (PCI-DSS 10.2 / SOC 2 CC7 evidence). Under gdpr the audited fragment body is replaced with a salted hash fingerprint — a raw whereRaw predicate may carry personal data, so the audit records a stable identifier without the plaintext.

## Threat grounding

Encoding-bypass: CVE-2025-1094 (PostgreSQL libpq, CVSS 8.1, KEV / actively exploited via BeyondTrust, public PoC). SQLite memory corruption reachable from crafted SQL: CVE-2025-6965 (CVSS 9.8, active) — the connection-hardening notes pin node:sqlite >= 3.50.2. MySQL LOCAL INFILE client-side file read: CVE-2025-62611. Injection leading to compromise, CISA KEV: CVE-2025-25181. The file / exec / FDW / extension constructs this guard refuses are by-design-dangerous SQL features, not patchable product defects — the defense is refusing them at the raw-SQL boundary, never accepting them from operator-supplied SQL.

Source file is pure ASCII; every attack character (dollar markers, multibyte encoding-bypass bytes, control bytes) is composed from numeric codepoints, never embedded as a literal.

b.guardSql.validate(input, opts?) #

{
  profile:           "strict"|"balanced"|"permissive",
  compliancePosture: "hipaa"|"pci-dss"|"gdpr"|"soc2",
  contextMode:       "fragment"|"operator-sql"|"migration",   // default "fragment"
  allowLiterals:     boolean,    // permit a static '...' literal in a fragment
  maxBytes:          number,     // raw-SQL byte cap (default 1 MiB)
}

Inspect a raw SQL string or Buffer and return { ok, issues }. Each issue carries { code, kind, ruleId, severity, snippet } with severity in "warn"|"high"|"critical". ok is true only when no issue is high or critical. Pure inspection — never throws on input.

The inspection runs three stages: a UTF-8 encoding gate (defends the libpq client-encoding bypass class, CVE-2025-1094), a comment-and- literal normalizer, and keyword + structural detectors on the normalized stream. The detected classes are stacked statements, comment smuggling, embedded string literals (fragment mode), the Postgres / SQLite / MySQL file / exec / FDW / extension / privilege- pivot constructs, time-based probes, schema recon, and set operations inside a predicate.

var rv = b.guardSql.validate("id = ? AND tenant = ?", { profile: "strict" });
rv.ok;                                                // → true

var bad = b.guardSql.validate("1; DROP TABLE users", { profile: "strict" });
bad.ok;                                               // → false
bad.issues.some(function (i) { return i.kind === "stacked-statement"; });  // → true

b.guardSql.sanitize(input, opts?) #

{
  profile:           "strict"|"balanced"|"permissive",
  compliancePosture: "hipaa"|"pci-dss"|"gdpr"|"soc2",
  contextMode:       "fragment"|"operator-sql"|"migration",
}

Return the comment-stripped, literal-masked NORMALIZED form of a raw SQL string — the internal representation the detectors run on, not a "made-safe" query. Hostile SQL is unrepairable: there is no transform that turns COPY ... PROGRAM or a stacked ;DROP into a safe statement, so sanitize never serves its output as a query. Throws GuardSqlError when the input refuses under the resolved profile (invalid encoding, the OS-reach floor, stacked statements), mirroring the entries-class guards whose hostile input has no sanitize action.

Use it to inspect what the tokenizer saw (debugging a false-positive detector, building a redacted audit fingerprint) — not to feed the result back to a driver.

var normalized = b.guardSql.sanitize(
  "id = ? -- note\n AND active = ?",
  { profile: "permissive" });
// → "id = ?  AND active = ?"  (comment stripped)

try {
  b.guardSql.sanitize("SELECT pg_read_file('/etc/passwd')");
} catch (e) {
  e.code;                                             // → "sql.file-access"
}

b.guardSql.gate(opts?) #

{
  profile:           "strict"|"balanced"|"permissive",
  compliancePosture: "hipaa"|"pci-dss"|"gdpr"|"soc2",
  contextMode:       "fragment"|"operator-sql"|"migration",
  name:              string,    // gate identity for audit / observability
}

Build a b.gateContract gate that consumes ctx.sql (or ctx.bytes). Action chain: serve (no SQL or clean) → audit-only (warn-level issues, every reject-class off) → refuse (any critical / high issue, or an explicit refuse action). There is no sanitize action — hostile SQL is unrepairable, so a refusal is the only safe non-serve outcome. The gate honors ctx.mode (one of the context modes) over the opts default, so one gate instance can guard a fragment whereRaw and an operator-sql path with the right strictness per call.

Every decision emits a signed audit entry; under the gdpr posture the audited SQL is replaced with a salted hash fingerprint (a whereRaw predicate may carry personal data).

var sqlGate = b.guardSql.gate({ profile: "strict" });
var verdict = await sqlGate.check({ sql: "id = ?", mode: "fragment" });
verdict.action;                                       // → "serve"

var blocked = await sqlGate.check({ sql: "1; DROP TABLE users" });
blocked.action;                                       // → "refuse"

b.guardSql.compliancePosture(name) #

Look up a compliance-posture overlay by name (one of "hipaa" / "pci-dss" / "gdpr" / "soc2"). Returns a fresh clone of the posture overlay so the caller may mutate it freely without disturbing the shared table. Throws GuardSqlError with code "sql.bad-posture" when the name is not one this guard maps. Wired by gateContract.defineGuard through gateContract.lookupCompliancePosture, so the clone semantics and error code are identical across every guard in the family.

var posture = b.guardSql.compliancePosture("hipaa");
posture;                                             // → overlay clone (mutable)

try {
  b.guardSql.compliancePosture("not-a-regime");
} catch (e) {
  e.code;                                            // → "sql.bad-posture"
}

b.guardSql.buildProfile(opts) #

{
  extends:   string|string[],   // base profile name(s) to compose
  ...:       any guard key,      // inline override of resolved keys
}

Compose a derived profile from one or more named bases plus inline overrides, resolving names through this guard's own PROFILES table. opts.extends is a base profile name ("strict" / "balanced" / "permissive") or an array of names — later entries shadow earlier ones, and inline opts keys win last. Wired by gateContract.defineGuard through gateContract.makeProfileBuilder, so operator-defined profiles stay traceable to a baseline instead of a hand-typed dictionary.

var custom = b.guardSql.buildProfile({ extends: "strict" });
custom;                                              // → composed profile object

b.guardSql.loadRulePack(pack) #

Register an operator-supplied rule pack with this guard's rule-pack registry. The pack is identified by pack.id (a non-empty string) and stored for later dispatch by gates that opt in via opts.rulePackId. Returns the pack unchanged on success; throws GuardSqlError with code "sql.bad-opt" when pack is missing or pack.id is not a non-empty string. Wired by gateContract.defineGuard through gateContract.makeRulePackLoader, so storage shape and validation are identical across the family.

var pack = b.guardSql.loadRulePack({ id: "tenant-policy", rules: [] });
pack.id;                                             // → "tenant-policy"

b.guardSql.resolveOpts(opts?) #

{
  profile:           string,    // one of PROFILES; default this guard's default
  compliancePosture: string,    // overlay one of hipaa/pci-dss/gdpr/soc2
}

Resolve caller opts against this guard's PROFILES + compliance-posture overlays into the fully-defaulted option set the guard runs on — the same resolution validate / sanitize / gate apply internally. Wired by gateContract.defineGuard from the guard's binding config (profiles / postures / defaults / error class), so a guard's bespoke gate calls resolveOpts instead of re-declaring the per-guard resolver wrapper. Throws GuardSqlError with code "sql.bad-opt" / "sql.bad-posture" on an unknown profile or posture name.

var resolved = b.guardSql.resolveOpts({ profile: "strict" });
resolved.profile;                                    // → "strict"

Last updated 2026-08-08T16:39:49.652Z by seeder.