/** * 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'"); }); });