apps/api/test/security.test.ts

import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { buildApp } from '../src/app';

// ─── Helpers ──────────────────────────────────────────────────────────────────

function makeJwt(app: any, payload: object) {
    return (app as any).jwt.sign(payload);
}

// ─── Suite ────────────────────────────────────────────────────────────────────

describe('Security — SQL Injection & Role Guards', () => {
    let app: any;

    beforeAll(async () => {
        app = await buildApp();
    });

    afterAll(async () => {
        await app.close();
    });

    // ── Analytics text-to-SQL injection protection (pure logic) ─────────────
    // HTTP integration tests for analytics/query require a real DB org (the
    // injectTenantConfig middleware runs before the route). Pattern validation
    // is tested here as pure unit checks.

    describe('SQL injection DANGEROUS_PATTERNS — unit checks', () => {
        // Mirror the exact patterns from analytics.ts
        const DANGEROUS_PATTERNS = [/\bUNION\b/, /\bINSERT\b/, /\bUPDATE\b/, /\bDELETE\b/, /\bDROP\b/, /\bEXEC\b/, /\bEXECUTE\b/, /--/, /\/\*/, /;\s*SELECT/i];

        it('blocks UNION SELECT', () => {
            const sql = "SELECT * FROM \"User\" WHERE \"organizationId\" = 'x' UNION SELECT * FROM \"User\"";
            expect(DANGEROUS_PATTERNS.some(p => p.test(sql.toUpperCase()))).toBe(true);
        });

        it('blocks DROP TABLE', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('DROP TABLE "User"'.toUpperCase()))).toBe(true);
        });

        it('blocks -- SQL comment', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('SELECT 1 -- bypass filter'))).toBe(true);
        });

        it('blocks /* block comment */', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('SELECT 1 /* comment */'))).toBe(true);
        });

        it('blocks DELETE', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('DELETE FROM "User" WHERE 1=1'.toUpperCase()))).toBe(true);
        });

        it('blocks INSERT', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('INSERT INTO "User" VALUES (1)'.toUpperCase()))).toBe(true);
        });

        it('blocks UPDATE', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('UPDATE "User" SET name=\'x\''.toUpperCase()))).toBe(true);
        });

        it('blocks stacked query with ; SELECT', () => {
            expect(DANGEROUS_PATTERNS.some(p => p.test('SELECT 1; SELECT * FROM "User"'))).toBe(true);
        });

        it('allows safe SELECT without dangerous patterns', () => {
            const sql = 'SELECT id, name FROM "User" WHERE "organizationId" = \'abc\' LIMIT 10';
            expect(DANGEROUS_PATTERNS.some(p => p.test(sql.toUpperCase()))).toBe(false);
        });

        it('allows SELECT with GROUP BY and ORDER BY', () => {
            const sql = 'SELECT feature, COUNT(*) as total FROM "UsageEvent" WHERE "organizationId" = \'abc\' GROUP BY feature ORDER BY total DESC LIMIT 5';
            expect(DANGEROUS_PATTERNS.some(p => p.test(sql.toUpperCase()))).toBe(false);
        });
    });

    // ── Admin route unauthenticated ───────────────────────────────────────────
    // Role guard (STUDENT/ORG_MEMBER → 403) requires a real DB org record because
    // injectTenantConfig runs before the route-level role check. The integration
    // tests in critical-flows.test.ts cover the authenticated path.

    describe('GET /v1/admin/stats — auth check', () => {
        it('returns 401 with no token', async () => {
            const res = await app.inject({ method: 'GET', url: '/v1/admin/stats' });
            expect(res.statusCode).toBe(401);
        });
    });

    // ── Super-admin route authentication & authorization ─────────────────────

    describe('GET /v1/super-admin/platform/stats — auth & role', () => {
        it('returns 401 with no token', async () => {
            const res = await app.inject({ method: 'GET', url: '/v1/super-admin/platform/stats' });
            expect(res.statusCode).toBe(401);
        });

        it('returns 403 for ORG_ADMIN role', async () => {
            const token = makeJwt(app, { id: 'u1', role: 'ORG_ADMIN', organizationId: 'org1' });
            const res = await app.inject({
                method: 'GET',
                url: '/v1/super-admin/platform/stats',
                headers: { Authorization: `Bearer ${token}` },
            });
            expect(res.statusCode).toBe(403);
        });

        it('returns 403 for STUDENT role', async () => {
            const token = makeJwt(app, { id: 'u1', role: 'STUDENT', organizationId: 'org1' });
            const res = await app.inject({
                method: 'GET',
                url: '/v1/super-admin/platform/stats',
                headers: { Authorization: `Bearer ${token}` },
            });
            expect(res.statusCode).toBe(403);
        });
    });

    // ── JWT secret enforcement ────────────────────────────────────────────────

    describe('JWT tampered token', () => {
        it('returns 401 for a tampered JWT', async () => {
            const res = await app.inject({
                method: 'GET',
                url: '/v1/admin/stats',
                headers: { Authorization: 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImZha2UifQ.invalidsignature' },
            });
            expect(res.statusCode).toBe(401);
        });
    });
});