GPU_GUARD_MONOREPO/packages/backend/test/mysql_sql_guard.test.ts

import {
  extractMysqlTables,
  validateMysqlReadOnlySql,
} from '../src/modules/netaclaw/service/mysql_query.js';

describe('mysql sql guard', () => {
  const baseOptions = {
    allowedTables: ['customers', 'orders', 'order_items', 'products'],
    maxJoinTables: 4,
    maxRows: 100,
  };

  it('accepts SELECT with explicit JOIN condition and extracts normalized tables', () => {
    const sql =
      'SELECT c.id, o.total FROM `Customers` c JOIN `Orders` o ON o.customer_id = c.id LIMIT 20';

    const result = validateMysqlReadOnlySql(sql, baseOptions);

    expect(result).toMatchObject({
      ok: true,
      tables: ['customers', 'orders'],
      limitedSql: sql,
      appendedLimit: false,
    });
    expect(extractMysqlTables(sql)).toEqual(['customers', 'orders']);
  });

  it('accepts a single trailing semicolon and normalizes it out', () => {
    const result = validateMysqlReadOnlySql('SELECT * FROM customers LIMIT 10;', baseOptions);

    expect(result).toMatchObject({
      ok: true,
      tables: ['customers'],
      limitedSql: 'SELECT * FROM customers LIMIT 10',
      appendedLimit: false,
    });
  });

  it.each([
    ['SHOW TABLES', 'metadata_sql_denied'],
    ['DESCRIBE customers', 'metadata_sql_denied'],
    ['EXPLAIN SELECT * FROM customers', 'metadata_sql_denied'],
    ['UPDATE customers SET name = "x"', 'dml_sql_denied'],
    ['DELETE FROM customers', 'dml_sql_denied'],
    ['DROP TABLE customers', 'ddl_sql_denied'],
    ['SELECT * FROM customers; SELECT * FROM orders', 'multiple_statements_denied'],
    ['SELECT * FROM customers -- trailing comment', 'comments_denied'],
    ['WITH c AS (SELECT * FROM customers) SELECT * FROM c', 'cte_denied'],
    ['SELECT * FROM customers UNION SELECT * FROM orders', 'union_denied'],
    ['SELECT @rownum := @rownum + 1 FROM customers', 'user_variable_denied'],
    ['SELECT SLEEP(1) FROM customers', 'dangerous_function_denied'],
    ["SELECT GET_LOCK('neta_lock', 30) FROM customers LIMIT 1", 'dangerous_function_denied'],
    ["SELECT RELEASE_LOCK('neta_lock') FROM customers LIMIT 1", 'dangerous_function_denied'],
    ["SELECT IS_FREE_LOCK('neta_lock') FROM customers LIMIT 1", 'dangerous_function_denied'],
    ["SELECT IS_USED_LOCK('neta_lock') FROM customers LIMIT 1", 'dangerous_function_denied'],
    ['SELECT * FROM customers INTO OUTFILE "/tmp/customers.csv"', 'file_output_denied'],
  ])('rejects unsafe SQL: %s', (sql, reason) => {
    expect(validateMysqlReadOnlySql(sql, baseOptions)).toMatchObject({
      ok: false,
      reason,
    });
  });

  it('rejects unallowed tables', () => {
    expect(
      validateMysqlReadOnlySql('SELECT * FROM payments LIMIT 10', baseOptions)
    ).toMatchObject({
      ok: false,
      reason: 'table_not_allowed',
      tables: ['payments'],
    });
  });

  it('rejects schema-qualified table references', () => {
    expect(
      validateMysqlReadOnlySql('SELECT * FROM otherdb.customers LIMIT 10', baseOptions)
    ).toMatchObject({
      ok: false,
      reason: 'schema_qualified_table_denied',
    });
  });

  it('rejects blocked tables', () => {
    expect(
      validateMysqlReadOnlySql('SELECT * FROM orders LIMIT 10', {
        ...baseOptions,
        blockedTables: ['orders'],
      })
    ).toMatchObject({
      ok: false,
      reason: 'table_blocked',
      tables: ['orders'],
    });
  });

  it('rejects JOIN without ON or USING', () => {
    expect(
      validateMysqlReadOnlySql(
        'SELECT * FROM customers JOIN orders WHERE customers.id = orders.customer_id LIMIT 10',
        baseOptions
      )
    ).toMatchObject({
      ok: false,
      reason: 'join_condition_required',
    });
  });

  it('rejects too many joined tables', () => {
    expect(
      validateMysqlReadOnlySql(
        'SELECT * FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id LIMIT 10',
        { ...baseOptions, maxJoinTables: 2 }
      )
    ).toMatchObject({
      ok: false,
      reason: 'too_many_join_tables',
      tables: ['customers', 'orders', 'order_items', 'products'],
    });
  });

  it('defaults maxJoinTables to 4', () => {
    const options = {
      allowedTables: ['customers', 'orders', 'order_items', 'products', 'regions'],
      maxRows: 100,
    };

    expect(
      validateMysqlReadOnlySql(
        'SELECT * FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id LIMIT 10',
        options
      )
    ).toMatchObject({
      ok: true,
      tables: ['customers', 'orders', 'order_items', 'products'],
    });

    expect(
      validateMysqlReadOnlySql(
        'SELECT * FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id JOIN regions ON regions.id = customers.region_id LIMIT 10',
        options
      )
    ).toMatchObject({
      ok: false,
      reason: 'too_many_join_tables',
      tables: ['customers', 'orders', 'order_items', 'products', 'regions'],
    });
  });

  it('rejects later JOIN missing its own ON or USING', () => {
    expect(
      validateMysqlReadOnlySql(
        'SELECT * FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items WHERE order_items.order_id = orders.id LIMIT 10',
        baseOptions
      )
    ).toMatchObject({
      ok: false,
      reason: 'join_condition_required',
    });
  });

  it('rejects explicit LIMIT above maxRows', () => {
    expect(
      validateMysqlReadOnlySql('SELECT * FROM customers LIMIT 101', baseOptions)
    ).toMatchObject({
      ok: false,
      reason: 'limit_exceeds_max_rows',
    });
  });

  it('defaults maxRows to 200', () => {
    const options = {
      allowedTables: ['customers'],
      maxJoinTables: 4,
    };

    expect(
      validateMysqlReadOnlySql('SELECT * FROM customers LIMIT 200', options)
    ).toMatchObject({
      ok: true,
      tables: ['customers'],
    });

    expect(
      validateMysqlReadOnlySql('SELECT * FROM customers LIMIT 201', options)
    ).toMatchObject({
      ok: false,
      reason: 'limit_exceeds_max_rows',
      tables: ['customers'],
    });
  });

  it('rejects masked column references', () => {
    expect(
      validateMysqlReadOnlySql('SELECT phone FROM customers LIMIT 10', {
        ...baseOptions,
        maskedColumns: { 'customers.phone': 'partial' },
      })
    ).toMatchObject({
      ok: false,
      reason: 'masked_column_denied',
      tables: ['customers'],
    });
  });

  it.each([
    'SELECT orders.phone FROM customers JOIN orders ON orders.customer_id = customers.id LIMIT 10',
    "SELECT id FROM customers WHERE note = 'phone' LIMIT 10",
  ])('does not reject safe non-customers masked-column text: %s', sql => {
    expect(
      validateMysqlReadOnlySql(sql, {
        ...baseOptions,
        maskedColumns: { 'customers.phone': 'partial' },
      })
    ).toMatchObject({
      ok: true,
    });
  });

  it.each([
    'SELECT customers.phone FROM customers LIMIT 10',
    'SELECT c.phone FROM customers c LIMIT 10',
    'SELECT phone FROM customers LIMIT 10',
  ])('rejects actual masked column references: %s', sql => {
    expect(
      validateMysqlReadOnlySql(sql, {
        ...baseOptions,
        maskedColumns: { 'customers.phone': 'partial' },
      })
    ).toMatchObject({
      ok: false,
      reason: 'masked_column_denied',
      tables: ['customers'],
    });
  });

  it.each([
    'SELECT * FROM customers LIMIT 10',
    'SELECT customers.* FROM customers LIMIT 10',
    'SELECT c.* FROM customers c LIMIT 10',
    'SELECT c2.* FROM customers c1 JOIN customers c2 ON c2.referrer_id = c1.id LIMIT 10',
    'SELECT `c-1`.* FROM customers AS `c-1` LIMIT 1',
  ])('rejects wildcard projection when referenced table has masked columns: %s', sql => {
    expect(
      validateMysqlReadOnlySql(sql, {
        ...baseOptions,
        maskedColumns: { 'customers.phone': 'partial' },
      })
    ).toMatchObject({
      ok: false,
      reason: 'masked_column_denied',
      tables: ['customers'],
    });
  });

  it('rejects derived table projections when derived query could expose masked columns', () => {
    expect(
      validateMysqlReadOnlySql('SELECT x.* FROM (SELECT * FROM customers) x LIMIT 1', {
        ...baseOptions,
        maskedColumns: { 'customers.phone': 'partial' },
      })
    ).toMatchObject({
      ok: false,
      reason: 'derived_table_denied',
      tables: ['customers'],
    });
  });

  it.each([
    'SELECT id FROM customers WHERE id = 1 FOR UPDATE',
    'SELECT id FROM customers WHERE id = 1 FOR SHARE',
    'SELECT id FROM customers WHERE id = 1 LOCK IN SHARE MODE',
  ])('rejects locking read SQL: %s', sql => {
    expect(validateMysqlReadOnlySql(sql, baseOptions)).toMatchObject({
      ok: false,
      reason: 'locking_read_denied',
      tables: ['customers'],
    });
  });

  it.each([
    'SELECT * FROM customers LIMIT 10 OFFSET 1000',
    'SELECT * FROM customers LIMIT 1000, 10',
  ])('rejects offset limits: %s', sql => {
    expect(validateMysqlReadOnlySql(sql, baseOptions)).toMatchObject({
      ok: false,
      reason: 'offset_denied',
      tables: ['customers'],
    });
  });

  it('wraps select without LIMIT in a bounded query', () => {
    expect(
      validateMysqlReadOnlySql('SELECT * FROM customers', baseOptions)
    ).toMatchObject({
      ok: true,
      limitedSql:
        'SELECT * FROM (SELECT * FROM customers) AS neta_limited_query LIMIT ?',
      appendedLimit: true,
    });
  });

  it.each([
    "SELECT id FROM customers WHERE note = 'limit 1'",
    'SELECT id FROM customers WHERE id IN (SELECT customer_id FROM orders LIMIT 1)',
  ])('ignores non-top-level LIMIT and adds an outer bounded query: %s', sql => {
    expect(validateMysqlReadOnlySql(sql, baseOptions)).toMatchObject({
      ok: true,
      limitedSql: `SELECT * FROM (${sql}) AS neta_limited_query LIMIT ?`,
      appendedLimit: true,
    });
  });

  it('rejects no-table selects when allowedTables is configured', () => {
    expect(validateMysqlReadOnlySql('SELECT DATABASE()', baseOptions)).toMatchObject({
      ok: false,
      reason: 'table_not_allowed',
      tables: [],
    });
  });
});