Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
22.5 KB
Raw Blame
#!/usr/bin/env bun
/**
 * Engine-sql dynamic-SQL guard (refactor wave 1, A2b / CQ3 / EO8 / EO17;
 * docs/ci-guards.md#engine-sql-dynamic-sql).
 *
 * In src/core/engine-sql/** every value reaches SQL as a bound parameter
 * (`sqlFragment`), and only constant text is spliced. This guard parses each
 * file (TypeScript compiler API) and fails on:
 *
 *   1. `trustedSql(arg)` where arg is not trusted text (below).
 *   2. An untagged template literal or `+` concatenation passed as the SQL
 *      argument of `.query(` / `.unsafe(` / `.executeRaw(` (first argument)
 *      or `executeRawJsonb(` (second), directly or through a local variable,
 *      with a substitution / operand that is not trusted text.
 *   3. A literal `$<digit>` (or a `$` right before a substitution) inside a
 *      composed string: a template with substitutions, any `sqlFragment`
 *      template, or an operand of a `+` concatenation. Placeholders are
 *      numbered by renderFragment; a static string passed as-is may carry them.
 *   4. An expanded list: `IN (` right before a substitution or a
 *      non-literal `+` operand. Lists bind as `= ANY($n::type[])`.
 *
 * Trusted text is: a string literal; an identifier or member / element
 * access whose root is a `const` in this file initialized with trusted text
 * or an `as const` object / array literal, or a CONSTANT_ALLOWLIST name; a
 * call to a VETTED_BUILDERS entry; a template whose substitutions are all
 * trusted or are numeric expressions the same function checked earlier with
 * `Number.isFinite(<same expression>)` (a `+` chain under the same rule); a
 * conditional whose branches are both trusted.
 *
 * Seam: GBRAIN_GUARD_ROOT (fixture tree root).
 */
import { existsSync, readdirSync, readFileSync, statSync } from 'node:fs';
import { join, relative } from 'node:path';
import ts from 'typescript';

const ROOT = process.env.GBRAIN_GUARD_ROOT ?? join(import.meta.dir, '..');
const SCAN_DIR = 'src/core/engine-sql';
const ANCHOR = 'docs/ci-guards.md#engine-sql-dynamic-sql';

const EXEMPT_FILES: Record<string, string> = {
  'fragment.ts': 'the renderer itself: it writes the $n placeholders and splices trustedSql text by design',
};

const CONSTANT_ALLOWLIST: Record<string, string> = {
  ENRICH_ORDER_SQL: 'src/core/types.ts: ORDER BY text keyed by the EnrichCandidatesOpts order union (whitelisted enum)',
  SOURCE_CONFIG_OBJECT_SQL: 'src/core/source-config-sql.ts: static sources.config coercion expression (no input)',
  EMBED_SKIP_FILTER_FRAGMENT: 'src/core/embed-skip.ts: constant embed_skip predicate over alias p',
  QUARANTINE_FILTER_FRAGMENT: 'src/core/quarantine.ts: constant quarantine visibility predicate over the pages alias p',
  PAGE_SORT_SQL: 'src/core/types.ts: ORDER BY text keyed by the PageFilters sort union (whitelisted enum)',
  TEMPORAL_LINK_SELECT_SQL: 'src/core/link-validity.ts: constant select list over the lr_t relationship-state join',
  RELEASE_STAGED_CTE: 'src/core/persistence/limits.ts: constant CTE releasing upload reservations of a preceding gone CTE (no input)',
};

const VETTED_BUILDERS: Record<string, string> = {
  pageReadFilter: 'src/core/search/read-policy-sql.ts: binds scope values as params, splices only the caller alias',
  buildHardExcludeClause: 'src/core/search/sql-ranking.ts: LIKE-escaped and SQL-escaped slug prefix literals (buildLikePrefixLiteral), the ranked-search hard-exclude clause',
  buildRecencyComponentSql: 'src/core/search/sql-ranking.ts: inlines LIKE literals + numeric coefficients from the decay map, as master (planner behavior)',
  privatePagesFilterFragment: 'src/core/search/private-visibility.ts: constant visibility predicate over a caller alias',
  privatePagesFilterForCaller: 'src/core/search/private-visibility.ts: privatePagesFilterFragment ORed with the private-readable source ids, each checked by isValidSourceId (alphabet [a-z0-9-], no quote) before it is quoted in',
  privateLinkOriginFilterForCaller: 'src/core/search/private-visibility.ts: constant link-origin predicate ORed with isValidSourceId-checked private-readable source ids',
  privateTimelineEventFilterForCaller: 'src/core/search/private-visibility.ts: constant timeline-event predicate ORed with isValidSourceId-checked private-readable source ids',
  orPrivateReadableSource: 'src/core/search/private-visibility.ts: wraps a caller predicate with isValidSourceId-checked private-readable source ids quoted as literals',
  replacedBecauseSql: 'src/core/engine-sql/pages.ts: constant GBRA-217 reason projection over a caller alias, with callerReadersAllowedSql on the successor (identities checked by validIdentity before they are quoted in); the boolean picks one of two constant texts',
  validDateSql: 'src/core/valid-time.ts: constant GBRA-217 date normaliser over a caller column expression; engine-sql passes only constant pv.frontmatter keys',
  quarantineFilterFragment: 'src/core/quarantine.ts: constant quarantine predicate over a caller alias',
  guestSourceExclusionSql: 'src/core/guests/derived.ts: GBRA-504 constant guest-source NOT EXISTS predicate (guests/note.ts guestSourceSql) over a caller alias, or TRUE for a producer that takes guest input; the producer key is a typed union member; no values',
  currentCodeEdgeFilter: 'src/core/code-intel/read-scope.ts: constant current-edge predicate over a caller alias',
  buildCJKKeywordSql: 'src/core/search/cjk-keyword-sql.ts: binds the query as params; shared by both engines',
  currentTextProjectionFilter: 'src/core/search/safe-chunks.ts: constant projection predicate over a caller alias',
  servedTextProjectionFilter: 'src/core/search/safe-chunks.ts: constant projection predicate over a caller alias, ORed with the constant v153 served-index marker (#9192)',
  chunkVisibleFilter: 'src/core/search/safe-chunks.ts: constant projection + live body generation predicate over caller page/chunk aliases (Wave B)',
  chunkGenerationFilter: 'src/core/search/safe-chunks.ts: constant live body generation predicate over caller page/chunk aliases (Wave B)',
  chunkManifestJoin: 'src/core/search/safe-chunks.ts: constant LEFT JOIN onto the live page_body_manifest row over caller aliases (Wave B)',
  chunkDocumentOrder: 'src/core/search/safe-chunks.ts: constant ORDER BY column list over caller chunk/manifest aliases (Wave B)',
  quoteIdentifier: 'src/core/search/embedding-column.ts: double-quoted identifier (embedded quotes doubled); callers pass registry-resolved or COLUMN_NAME_REGEX-checked embedding column names',
  vectorCastSuffix: 'src/core/search/embedding-column.ts: constant ::vector / ::halfvec cast suffix from the resolved column type',
  safeChunksFilter: 'src/core/search/safe-chunks.ts: constant safe-chunk predicate over a caller alias',
  pageBudgetLimitedFilter: 'src/core/page-size-policy.ts: constant held-or-capped budget predicate over a caller page alias; no values',
  movableChunkSql: 'src/core/search/safe-chunks.ts: constant vector-matches-its-text predicate over a caller-quoted embedding column; no values',
  bodyWriteChunkVersion: 'src/core/search/safe-chunks.ts: constant chunker-version CASE over caller column expressions (or master\'s literal $1/$2 bind reuse); no values',
  privateLinkOriginFilterFragment: 'src/core/search/private-visibility.ts: constant link-origin visibility predicate over a caller alias',
  privateTimelineEventFilterFragment: 'src/core/search/private-visibility.ts: constant timeline-event visibility predicate over a caller alias',
  privateSnapshotFilterFragment: 'src/core/search/private-visibility.ts: constant snapshot visibility predicate over a caller alias',
  rederiveHiddenSql: 'src/core/eligibility/sql.ts: constant needs_rederive NOT EXISTS predicate over a literal table name and caller alias; no values',
  deletedPageProjectionHiddenSql: 'src/core/eligibility/sql.ts: constant soft-deleted-page NOT EXISTS predicate over a literal table name and caller alias; no values',
  privateFactFilterForCaller: 'src/core/search/private-visibility.ts: privateProvenanceFilterFragment ANDed with privateEntityFilterForCaller (the caller page policy over a constant entity-page alias); no values',
  privateProvenanceFilterFragment: 'src/core/search/private-visibility.ts: constant fact-provenance visibility predicate over a caller alias',
  readersAllowedSql: 'src/core/search/private-visibility.ts: GBRA-218 readers predicate over a caller alias; splices only identities that pass validIdentity (member:/group: plus [a-z0-9._-]), else none',
  callerReadersAllowedSql: 'src/core/search/private-visibility.ts: GBRA-231 readersAllowedSql for the current caller, one conjunct per audience identity; each identity passes validIdentity',
  privacyForCaller: 'src/core/search/private-visibility.ts: the caller privacy arm (TRUE, or the given predicate ORed with private-readable source ids checked by isValidSourceId); callers pass a constant alias and a vetted predicate builder',
  factVisibilitySql: 'src/core/access/member-facts.ts: visibility values filtered to the constant set world/private; widened only by memberFactAdmitSql, whose member id passed ACCESS_ID_RE (no quote) and whose page test is privatePagesFilterForCaller; no values',
  onOwnedMemoryPageSql: 'src/core/access/member-facts.ts: constant memory/members/ prefix and memory/scope slug test over a code-provided facts alias; no values',
  privateProvenanceFilterUnderPolicy: 'src/core/search/private-visibility.ts: constant fact-provenance predicate (readers part with no identity, or the caller identity on its own member page whose id passed ACCESS_ID_RE; privacy part dropped only under an operator relaxation); no values',
  liveTilingSql: 'src/core/page-body/schema.ts: constant LATERAL aggregate of the live manifest rows of a caller page alias (count, covered bytes, gapless); no values (GST-1209)',
  untiledSql: 'src/core/page-body/schema.ts: constant predicate over caller page and tiling aliases; no values (GST-1209)',
  stagingNeedsTextSql: 'src/core/page-body/schema.ts: constant live-staging text-reference EXISTS predicate over a caller text alias; no values (Wave B purge)',
  atomRetirementPurgeHold: 'src/core/persistence/atom-retirement-hold.ts: constant #7013 restorable-atom purge-hold predicate over a caller pages alias; no values',
  derivedRetirementPurgeHold: 'src/core/persistence/derived-retirement-hold.ts: constant GBRA-161 derived-retirement purge-hold predicate over a caller pages alias; no values',
  derivedOriginPurgeHold: 'src/core/persistence/derived-origin-hold.ts: constant GST-2689 parked-origin purge-hold predicate over a caller pages alias; no values',
  rankingLinkSql: 'src/core/link-ranking-policy.ts: constant non-ranking link-type predicate (code-constant type names) over a caller alias',
  retiredPageSql: 'src/core/entities/resolve.ts: constant retired-page frontmatter predicate over a caller alias; no values',
  vectorLiteralSql: 'src/core/engine-sql/facts.ts: master\'s inlined vector literal; toPgVectorLiteral output (numbers joined by commas) + a ::vector/::halfvec constant',
  temporalLinkJoinSql: 'src/core/link-validity.ts: constant LEFT JOIN onto link_relationships over a caller alias; scope is one of two literals',
  projectionEligibleSql: 'src/core/eligibility/sql.ts: read-eligibility predicate over a caller alias; splices only closed tier and reason-family vocabularies (no values)',
  pageEligibleSql: 'src/core/eligibility/sql.ts: page trust-floor and activation predicate over a caller alias; closed vocabularies only',
  relationshipFilterSql: 'src/core/link-validity.ts: relationship-state probe over a caller alias; dates pass isCalendarDate and inline as DATE literals, status/scope are literals',
};

const SQL_ARG_METHODS: Record<string, number> = { query: 0, unsafe: 0, executeRaw: 0 };
const SQL_ARG_FUNCTIONS: Record<string, number> = { executeRawJsonb: 1 };

const FIX = {
  trusted: "bind the value through sqlFragment (`${value}`), or splice only a string literal, an `as const` allowlist member, a VETTED_BUILDERS call or a Number.isFinite-checked number",
  composed: 'build the statement with sqlFragment and run it with executor.run(fragment)',
  placeholder: 'drop the hand-written $n: interpolate the value in sqlFragment and let renderFragment number it',
  list: 'bind the array as one parameter: `= ANY(${ids}::text[])` in sqlFragment (renders `= ANY($n::text[])`)',
};

function tsFiles(dir: string): string[] {
  if (!existsSync(dir)) return [];
  const out: string[] = [];
  for (const entry of readdirSync(dir).sort()) {
    const full = join(dir, entry);
    if (statSync(full).isDirectory()) out.push(...tsFiles(full));
    else if (entry.endsWith('.ts') && !entry.endsWith('.d.ts')) out.push(full);
  }
  return out;
}

function unwrap(expr: ts.Expression): ts.Expression {
  let e = expr;
  while (ts.isParenthesizedExpression(e) || ts.isAsExpression(e) || ts.isNonNullExpression(e) || ts.isSatisfiesExpression(e)) {
    if (ts.isAsExpression(e) && isConstAssertion(e)) return e;
    e = e.expression;
  }
  return e;
}

function isConstAssertion(e: ts.AsExpression): boolean {
  return ts.isTypeReferenceNode(e.type) && ts.isIdentifier(e.type.typeName) && e.type.typeName.text === 'const';
}

function rootIdentifier(expr: ts.Expression): ts.Identifier | null {
  let e = unwrap(expr);
  while (ts.isPropertyAccessExpression(e) || ts.isElementAccessExpression(e)) e = unwrap(e.expression);
  return ts.isIdentifier(e) ? e : null;
}

function enclosingFunction(node: ts.Node): ts.Node | undefined {
  for (let n = node.parent; n; n = n.parent) if (ts.isFunctionLike(n)) return n;
  return undefined;
}

function isNumericGuarded(expr: ts.Expression, sf: ts.SourceFile): boolean {
  const e = unwrap(expr);
  if (ts.isNumericLiteral(e)) return true;
  if (!ts.isIdentifier(e) && !ts.isPropertyAccessExpression(e)) return false;
  const fn = enclosingFunction(e);
  if (!fn) return false;
  const target = e.getText(sf);
  let found = false;
  const visit = (n: ts.Node): void => {
    if (found || n.getStart(sf) >= e.getStart(sf)) return;
    if (
      ts.isCallExpression(n) && n.arguments.length === 1 && n.expression.getText(sf) === 'Number.isFinite'
      && unwrap(n.arguments[0]).getText(sf) === target
    ) {
      found = true;
      return;
    }
    ts.forEachChild(n, visit);
  };
  visit(fn);
  return found;
}

class FileScan {
  readonly violations: string[] = [];
  private readonly consts = new Map<string, ts.VariableDeclaration[]>();
  private readonly lets = new Map<string, ts.VariableDeclaration[]>();

  constructor(private readonly sf: ts.SourceFile, private readonly rel: string) {
    const collect = (n: ts.Node): void => {
      if (ts.isVariableDeclaration(n) && ts.isIdentifier(n.name) && ts.isVariableDeclarationList(n.parent)) {
        const map = n.parent.flags & ts.NodeFlags.Const ? this.consts : this.lets;
        map.set(n.name.text, [...(map.get(n.name.text) ?? []), n]);
      }
      ts.forEachChild(n, collect);
    };
    collect(sf);
  }

  private fail(node: ts.Node, what: string, fix: string): void {
    const line = this.sf.getLineAndCharacterOfPosition(node.getStart(this.sf)).line + 1;
    this.violations.push(`FAIL: ${this.rel}:${line} ${what}\n      Fix: ${fix}`);
  }

  private text(node: ts.Node): string {
    return node.getText(this.sf).replace(/\s+/g, ' ').slice(0, 80);
  }

  private declarationFor(id: ts.Identifier, map: Map<string, ts.VariableDeclaration[]>): ts.VariableDeclaration | undefined {
    const decls = map.get(id.text);
    if (!decls) return undefined;
    const scopeOf = (n: ts.Node) => enclosingFunction(n) ?? this.sf;
    const scopes: ts.Node[] = [];
    for (let s: ts.Node | undefined = scopeOf(id); s; s = s === this.sf ? undefined : scopeOf(s)) scopes.push(s);
    for (const scope of scopes) {
      const hit = decls.find((d) => scopeOf(d) === scope && d.getStart(this.sf) < id.getStart(this.sf));
      if (hit) return hit;
    }
    return undefined;
  }

  isTrusted(expr: ts.Expression, seen = new Set<ts.Node>()): boolean {
    const e = unwrap(expr);
    if (ts.isStringLiteral(e) || ts.isNoSubstitutionTemplateLiteral(e)) return true;
    if (ts.isAsExpression(e)) return ts.isObjectLiteralExpression(unwrap(e.expression)) || ts.isArrayLiteralExpression(unwrap(e.expression));
    if (ts.isConditionalExpression(e)) return this.isTrusted(e.whenTrue, seen) && this.isTrusted(e.whenFalse, seen);
    if (ts.isTemplateExpression(e)) return e.templateSpans.every((s) => this.isTrusted(s.expression, seen) || isNumericGuarded(s.expression, this.sf));
    if (ts.isCallExpression(e)) return ts.isIdentifier(e.expression) && e.expression.text in VETTED_BUILDERS;
    if (ts.isBinaryExpression(e) && e.operatorToken.kind === ts.SyntaxKind.PlusToken) {
      return plusOperands(e).every((o) => this.isTrusted(o, seen) || isNumericGuarded(o, this.sf));
    }
    const root = rootIdentifier(e);
    if (!root || (root !== e && !ts.isPropertyAccessExpression(e) && !ts.isElementAccessExpression(e))) return false;
    if (root.text in CONSTANT_ALLOWLIST) return true;
    const decl = this.declarationFor(root, this.consts);
    if (!decl?.initializer || seen.has(decl)) return false;
    seen.add(decl);
    return this.isTrusted(decl.initializer, seen);
  }

  private checkComposedSql(arg: ts.Expression, call: ts.CallExpression, seen = new Set<ts.Node>()): void {
    const e = unwrap(arg);
    if (ts.isTemplateExpression(e)) {
      for (const span of e.templateSpans) {
        if (!this.isTrusted(span.expression) && !isNumericGuarded(span.expression, this.sf)) {
          this.fail(span.expression, `untrusted \${${this.text(span.expression)}} in SQL passed to ${this.text(call.expression)}()`, FIX.composed);
        }
      }
      return;
    }
    if (ts.isBinaryExpression(e) && e.operatorToken.kind === ts.SyntaxKind.PlusToken) {
      for (const operand of plusOperands(e)) {
        if (!this.isTrusted(operand) && !isNumericGuarded(operand, this.sf)) {
          this.fail(operand, `untrusted operand '${this.text(operand)}' concatenated into SQL passed to ${this.text(call.expression)}()`, FIX.composed);
        }
      }
      return;
    }
    if (!ts.isIdentifier(e)) return;
    const decl = this.declarationFor(e, this.consts) ?? this.declarationFor(e, this.lets);
    if (!decl || seen.has(decl)) return;
    seen.add(decl);
    if (decl.initializer) this.checkComposedSql(decl.initializer, call, seen);
    const fn = enclosingFunction(decl) ?? this.sf;
    const visit = (n: ts.Node): void => {
      if (
        ts.isBinaryExpression(n) && ts.isIdentifier(n.left) && n.left.text === e.text
        && (n.operatorToken.kind === ts.SyntaxKind.EqualsToken || n.operatorToken.kind === ts.SyntaxKind.PlusEqualsToken)
      ) {
        if (n.operatorToken.kind === ts.SyntaxKind.PlusEqualsToken && !this.isTrusted(n.right) && !isNumericGuarded(n.right, this.sf)) {
          this.fail(n.right, `untrusted '${this.text(n.right)}' appended to SQL passed to ${this.text(call.expression)}()`, FIX.composed);
        } else this.checkComposedSql(n.right, call, seen);
      }
      ts.forEachChild(n, visit);
    };
    visit(fn);
  }

  private checkTemplateText(node: ts.TemplateExpression | ts.NoSubstitutionTemplateLiteral, composed: boolean): void {
    const pieces = ts.isTemplateExpression(node) ? [node.head, ...node.templateSpans.map((s) => s.literal)] : [node];
    pieces.forEach((piece, i) => {
      const beforeSub = i < pieces.length - 1;
      if (composed && (/\$\d/.test(piece.text) || (beforeSub && piece.text.endsWith('$')))) {
        this.fail(piece, 'literal $<digit> placeholder in a composed SQL string', FIX.placeholder);
      }
      if (beforeSub && /\bIN\s*\(\s*$/i.test(piece.text)) this.fail(piece, 'expanded IN (...) list built from a substitution', FIX.list);
    });
  }

  private checkConcat(node: ts.BinaryExpression): void {
    const operands = plusOperands(node);
    operands.forEach((operand, i) => {
      if (!ts.isStringLiteral(operand) && !ts.isNoSubstitutionTemplateLiteral(operand)) return;
      if (/\$\d/.test(operand.text)) this.fail(operand, 'literal $<digit> placeholder in a concatenated SQL string', FIX.placeholder);
      const next = operands[i + 1];
      if (next && !ts.isStringLiteral(next) && !ts.isNoSubstitutionTemplateLiteral(next) && /\bIN\s*\(\s*$/i.test(operand.text)) {
        this.fail(operand, 'expanded IN (...) list built by concatenation', FIX.list);
      }
    });
  }

  run(): void {
    const visit = (n: ts.Node): void => {
      if (ts.isTaggedTemplateExpression(n)) {
        const isFragment = ts.isIdentifier(n.tag) && n.tag.text === 'sqlFragment';
        this.checkTemplateText(n.template, isFragment);
      } else if (ts.isTemplateExpression(n) && !ts.isTaggedTemplateExpression(n.parent)) {
        this.checkTemplateText(n, true);
      } else if (ts.isBinaryExpression(n) && n.operatorToken.kind === ts.SyntaxKind.PlusToken
        && !(ts.isBinaryExpression(n.parent) && n.parent.operatorToken.kind === ts.SyntaxKind.PlusToken)) {
        this.checkConcat(n);
      }
      if (ts.isCallExpression(n)) {
        const callee = n.expression;
        if (ts.isIdentifier(callee) && callee.text === 'trustedSql') {
          const arg = n.arguments[0];
          if (!arg || !this.isTrusted(arg)) {
            this.fail(n, `trustedSql(${arg ? this.text(arg) : ''}) splices text that is not constant`, FIX.trusted);
          }
        }
        const index = ts.isPropertyAccessExpression(callee)
          ? SQL_ARG_METHODS[callee.name.text]
          : ts.isIdentifier(callee) ? SQL_ARG_FUNCTIONS[callee.text] : undefined;
        const sqlArg = index === undefined ? undefined : n.arguments[index];
        if (sqlArg) this.checkComposedSql(sqlArg, n);
      }
      ts.forEachChild(n, visit);
    };
    visit(this.sf);
  }
}

function plusOperands(node: ts.Expression): ts.Expression[] {
  const e = unwrap(node);
  if (ts.isBinaryExpression(e) && e.operatorToken.kind === ts.SyntaxKind.PlusToken) return [...plusOperands(e.left), ...plusOperands(e.right)];
  return [e];
}

const files = tsFiles(join(ROOT, SCAN_DIR)).filter((abs) => !(relative(join(ROOT, SCAN_DIR), abs).replace(/\\/g, '/') in EXEMPT_FILES));
const violations: string[] = [];
for (const abs of files) {
  const rel = relative(ROOT, abs).replace(/\\/g, '/');
  const scan = new FileScan(ts.createSourceFile(abs, readFileSync(abs, 'utf8'), ts.ScriptTarget.Latest, true, ts.ScriptKind.TS), rel);
  scan.run();
  violations.push(...scan.violations);
}

if (violations.length) {
  for (const v of violations) console.error(v);
  console.error('Why:  engine-sql binds every value as a parameter and splices only constant text; composed $n, expanded IN lists and raw interpolation are how injection and unbounded prepared-statement caches start (CQ3 / EO8).');
  console.error(`See:  ${ANCHOR}`);
  console.error(`check-engine-sql-dynamic: ${violations.length} violation(s)`);
  process.exit(1);
}
console.log(`check-engine-sql-dynamic: ok (${files.length} files)`);