0pt/tests/analytics/query-compiler-injection.test.ts

/**
 * BI-P011's adversarial list, at the compiler layer:
 *
 *   tenant A query B dataset · tenant A forged dataset ID · raw SQL attempt
 *   SQL injection · dimension identifier injection · filter injection
 *
 * These are the cases that never reach the network. The cases that DO reach a
 * real server — and the with-fix/without-fix proof BI-P011 actually demands —
 * are tests/integration/analytics-tenant-isolation.integration.test.ts.
 */
import { describe, it, expect } from 'vitest';
import {
  assertSafeIdentifier,
  compileMetricQuery,
  compileProjectionQuery,
  dimensionCatalogFrom,
  restrictedTenantUserDdl,
} from '@/src/analytics/engine/query-compiler';

const catalog = dimensionCatalogFrom({ severity: 'severity', make: 'vehicle_make' });

const HOSTILE = [
  "x'; DROP TABLE opt_analytics_event; --",
  '1 OR 1=1',
  'tenant_id',
  '*/ OR 1=1 /*',
  'severity UNION ALL SELECT tenant_id FROM opt_analytics_event',
  'dim_values[1]',
  '__proto__',
  'constructor',
  'sever ity',
  'sévérité',
  'a'.repeat(200),
  '',
];

describe('BI-P011 injection surfaces at the compiler', () => {
  it('dimension identifier injection: hostile group_by names are refused, not escaped', () => {
    for (const bad of HOSTILE) {
      expect(
        () =>
          compileMetricQuery(
            { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum', group_by: [bad] },
            'opt_analytics',
            catalog,
            1000,
          ),
        `group_by "${bad}" was not refused`,
      ).toThrowError(/unknown group_by dimension/);
    }
  });

  it('filter injection: hostile filter KEYS are refused by catalog resolution', () => {
    for (const bad of HOSTILE) {
      expect(
        () =>
          compileMetricQuery(
            { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum', filters: { [bad]: 'v' } },
            'opt_analytics',
            catalog,
            1000,
          ),
        `filter key "${bad}" was not refused`,
      ).toThrowError(/unknown filter dimension/);
    }
  });

  it('prototype-chain keys resolve to undefined rather than to Object.prototype members', () => {
    // A naive `catalog[name]` lookup would resolve "constructor" to a
    // function and "toString" to a method; the Map-based catalog must not.
    for (const key of ['constructor', 'toString', 'hasOwnProperty', '__proto__', 'valueOf']) {
      expect(catalog.resolve(key)).toBeUndefined();
    }
  });

  it('filter VALUES are never spliced, however hostile', () => {
    const compiled = compileMetricQuery(
      {
        tenant_id: 't_alpha',
        metric: 'm',
        aggregate: 'sum',
        filters: { make: "volvo'; DROP TABLE opt_analytics_event; --" },
      },
      'opt_analytics',
      catalog,
      1000,
    );
    expect(compiled.sql).not.toContain('DROP TABLE');
    expect(compiled.sql).not.toContain('volvo');
    expect(compiled.params.param_f0v).toBe("volvo'; DROP TABLE opt_analytics_event; --");
    // The physical column name came from the catalog, not the caller.
    expect(compiled.params.param_f0n).toBe('vehicle_make');
  });

  it('metric and dataset VALUES are never spliced either', () => {
    const compiled = compileMetricQuery(
      {
        tenant_id: 't_alpha',
        metric: "m'; SELECT 1; --",
        dataset_id: "ds'; SELECT 1; --",
        aggregate: 'sum',
      },
      'opt_analytics',
      catalog,
      1000,
    );
    expect(compiled.sql).not.toContain('SELECT 1');
    expect(compiled.sql.match(/SELECT/g)).toHaveLength(1);
  });

  it('raw SQL attempt: there is no request shape that reaches a second table', () => {
    const compiled = compileMetricQuery(
      { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum' },
      'opt_analytics',
      catalog,
      1000,
    );
    expect(compiled.sql).not.toMatch(/\bUNION\b/i);
    expect(compiled.sql).not.toMatch(/\bJOIN\b/i);
    expect((compiled.sql.match(/\bFROM\b/gi) ?? []).length).toBe(1);
    expect(compiled.table).toBe('opt_analytics_event');
  });

  it('a forged table name is refused rather than reaching the FROM clause', () => {
    expect(() =>
      compileMetricQuery(
        { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum', table: 'system.tables' as never },
        'opt_analytics',
        catalog,
        1000,
      ),
    ).toThrowError(/invalid table identifier/);
  });

  it('an unsupported aggregate is refused rather than being interpolated', () => {
    expect(() =>
      compileMetricQuery(
        { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum(1),tenant_id' as never },
        'opt_analytics',
        catalog,
        1000,
      ),
    ).toThrowError(/unsupported aggregate/);
  });

  it('group-by cardinality is capped so a caller cannot explode the GROUP BY', () => {
    const many = Array.from({ length: 9 }, () => 'severity');
    expect(() =>
      compileMetricQuery(
        { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum', group_by: many },
        'opt_analytics',
        catalog,
        1000,
      ),
    ).toThrowError(/at most 8 group-by/);
  });

  it('LIMIT is clamped to the governance ceiling, and never below 1', () => {
    const big = compileMetricQuery(
      { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum', limit: 10_000_000 },
      'opt_analytics',
      catalog,
      500,
    );
    expect(big.sql).toContain('LIMIT 500');
    const zero = compileMetricQuery(
      { tenant_id: 't_alpha', metric: 'm', aggregate: 'sum', limit: 0 },
      'opt_analytics',
      catalog,
      500,
    );
    expect(zero.sql).toContain('LIMIT 1');
  });

  it('the projection compiler forces the tenant and parameterizes entity keys', () => {
    const compiled = compileProjectionQuery(
      { tenant_id: 't_alpha', dataset_id: 'ds_x', entity_keys: ["a'; DROP", 'b'] },
      'opt_analytics',
      100,
    );
    expect(compiled.sql).toMatch(/WHERE tenant_id = \{tenant:String\}/);
    expect(compiled.sql).not.toContain('DROP');
    expect(compiled.params.param_entityKeys).toBe(JSON.stringify(["a'; DROP", 'b']));
  });

  it('assertSafeIdentifier accepts real identifiers and rejects everything else', () => {
    for (const good of ['severity', 'vehicle_make', '_x', 'a1_b2']) {
      expect(assertSafeIdentifier(good, 'test')).toBe(good);
    }
    for (const bad of ['1abc', 'a-b', 'a b', 'a;b', '', 'a'.repeat(64)]) {
      expect(() => assertSafeIdentifier(bad, 'test')).toThrowError(/invalid test identifier/);
    }
  });

  it('restrictedTenantUserDdl refuses a tenant id or password it cannot safely embed', () => {
    expect(() =>
      restrictedTenantUserDdl({
        db: 'opt_analytics',
        tenantId: "t_alpha' OR '1'='1",
        userName: 'opt_t_alpha',
        password: 'pw',
        tables: ['opt_analytics_event'],
      }),
    ).toThrowError(/not policy-safe/);

    expect(() =>
      restrictedTenantUserDdl({
        db: 'opt_analytics',
        tenantId: 't_alpha',
        userName: 'opt_t_alpha',
        password: "pw' OR '1'='1",
        tables: ['opt_analytics_event'],
      }),
    ).toThrowError(/cannot be embedded/);

    const stmts = restrictedTenantUserDdl({
      db: 'opt_analytics',
      tenantId: 't_alpha',
      userName: 'opt_t_alpha',
      password: 'safe_pw_123',
      tables: ['opt_analytics_event'],
    });
    expect(stmts.join(' ')).toContain('CREATE ROW POLICY IF NOT EXISTS');
    expect(stmts.join(' ')).toContain("USING tenant_id = 't_alpha'");
  });
});