AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP.Tests/SmartOps/KpiSqlSecurityValidatorTests.cs

using Admin.NET.Plugin.AiDOP.SmartOps;
using Xunit;

namespace Admin.NET.Plugin.AiDOP.Tests.SmartOps;

/// <summary>KPI 配置 SQL 安全校验（多层非正则）单元测试。覆盖合法查询 + 各类写/危险/绕过。</summary>
public class KpiSqlSecurityValidatorTests
{
    private const string LegalSelect =
        "SELECT AVG(cycle_days) AS metric_value FROM dwd_t8_material_online_cycle WHERE tenant_id=@tenant_id AND cycle_days IS NOT NULL";

    [Fact]
    public void LegalSelect_Valid()
    {
        var r = KpiSqlSecurityValidator.Validate(LegalSelect);
        Assert.True(r.Ok);
        Assert.Contains("dwd_t8_material_online_cycle", r.ReferencedTables);
    }

    [Fact]
    public void LegalWithSelect_Valid()
    {
        var sql = "WITH t AS (SELECT cycle_days FROM dwd_t8_material_online_cycle WHERE tenant_id=@tenant_id) " +
                  "SELECT AVG(cycle_days) AS metric_value FROM mdp_std_t8_kc_tz_head";
        var r = KpiSqlSecurityValidator.Validate(sql);
        Assert.True(r.Ok);
    }

    [Theory]
    [InlineData("INSERT INTO dwd_t8_x(a) VALUES(1)")]
    [InlineData("UPDATE dwd_t8_x SET a=1")]
    [InlineData("DELETE FROM dwd_t8_x")]
    [InlineData("DROP TABLE dwd_t8_x")]
    [InlineData("ALTER TABLE dwd_t8_x ADD c INT")]
    [InlineData("CREATE TABLE t(a INT)")]
    [InlineData("TRUNCATE TABLE dwd_t8_x")]
    [InlineData("REPLACE INTO dwd_t8_x VALUES(1)")]
    [InlineData("MERGE INTO dwd_t8_x")]
    public void WriteStatements_Invalid(string sql)
    {
        var r = KpiSqlSecurityValidator.Validate(sql);
        Assert.False(r.Ok);
    }

    [Theory]
    [InlineData("EXEC sp_x")]
    [InlineData("EXECUTE sp_x")]
    [InlineData("CALL my_proc()")]
    public void ProcCalls_Invalid(string sql)
    {
        Assert.False(KpiSqlSecurityValidator.Validate(sql).Ok);
    }

    [Fact]
    public void MultiStatement_Invalid()
    {
        var r = KpiSqlSecurityValidator.Validate("SELECT 1 AS metric_value FROM dwd_t8_x; SELECT 2");
        Assert.False(r.Ok);
        Assert.Equal("MULTI_STATEMENT", r.ErrorCode);
    }

    [Fact]
    public void CommentHiddenSecondStatement_StrippedThenSingleStatement_Valid()
    {
        // 注释里的第二语句被去掉后应为单条合法 SELECT
        var sql = "SELECT AVG(cycle_days) AS metric_value FROM dwd_t8_material_online_cycle -- ; DROP TABLE x";
        Assert.True(KpiSqlSecurityValidator.Validate(sql).Ok);
    }

    [Fact]
    public void CommentHiddenKeyword_NotFalsePositive()
    {
        var sql = "SELECT AVG(cycle_days) AS metric_value /* delete update drop */ FROM dwd_t8_material_online_cycle";
        Assert.True(KpiSqlSecurityValidator.Validate(sql).Ok);
    }

    [Fact]
    public void KeywordInsideStringLiteral_NotFalsePositive()
    {
        var sql = "SELECT 'DROP TABLE users' AS result_message, 1 AS metric_value FROM dwd_t8_material_online_cycle";
        Assert.True(KpiSqlSecurityValidator.Validate(sql).Ok);
    }

    [Fact]
    public void IllegalTable_Invalid()
    {
        var r = KpiSqlSecurityValidator.Validate("SELECT metric_value FROM sys_user WHERE tenant_id=@tenant_id");
        Assert.False(r.Ok);
        Assert.Equal("TABLE_NOT_ALLOWED", r.ErrorCode);
    }

    [Theory]
    [InlineData("SHOW TABLES")]
    [InlineData("SET @a=1")]
    [InlineData("USE mysql")]
    public void NonSelectStart_Invalid(string sql)
    {
        Assert.False(KpiSqlSecurityValidator.Validate(sql).Ok);
    }

    [Fact]
    public void IntoOutfile_Invalid()
    {
        var r = KpiSqlSecurityValidator.Validate(
            "SELECT metric_value INTO OUTFILE '/tmp/x' FROM dwd_t8_material_online_cycle");
        Assert.False(r.Ok);
    }

    [Fact]
    public void Empty_Invalid()
    {
        Assert.False(KpiSqlSecurityValidator.Validate("   ").Ok);
        Assert.False(KpiSqlSecurityValidator.Validate("-- only comment").Ok);
    }

    // ── AIDOP-KPI-SQL-CONFIG-MIGRATION-EXISTING-1：本批 5 条 CONFIG_SQL 的代表性形态须通过校验 ──

    [Fact]
    public void DailyAvgFulfillment_Valid()
    {
        // S5_L1_002 / S7_L1_002 形态：AVG(rate)[*100] over dwd，逐单比率均值
        var sql = "SELECT ROUND(AVG(fulfillment_rate) * 100, 4) AS metric_value, COUNT(*) AS numerator_value " +
                  "FROM dwd_t8_material_online_fulfillment " +
                  "WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date " +
                  "AND source_ztid = @ztid AND fulfillment_rate IS NOT NULL";
        var r = KpiSqlSecurityValidator.Validate(sql);
        Assert.True(r.Ok);
        Assert.Contains("dwd_t8_material_online_fulfillment", r.ReferencedTables);
    }

    [Fact]
    public void MonthlyEfficiency_NullifAndDateFormat_Valid()
    {
        // S5_L1_003 / S7_L1_003 形态：NULLIF 防除零 + DATE_FORMAT 月份匹配，人效 headcount 空/0→NO_DATA
        var sql = "SELECT ROUND(shipment_qty / NULLIF(warehouse_headcount, 0), 4) AS metric_value, " +
                  "shipment_qty AS numerator_value, warehouse_headcount AS denominator_value, " +
                  "denominator_status AS result_message " +
                  "FROM dwd_t8_finished_warehouse_efficiency " +
                  "WHERE tenant_id = @tenant_id AND factory_id = @factory_id " +
                  "AND biz_month = DATE_FORMAT(@biz_date, '%Y-%m') AND source_ztid = @ztid";
        var r = KpiSqlSecurityValidator.Validate(sql);
        Assert.True(r.Ok);
        Assert.Contains("dwd_t8_finished_warehouse_efficiency", r.ReferencedTables);
    }

    [Fact]
    public void DailyAvgCycle_Valid()
    {
        // S7_L1_001 形态：AVG(cycle_days) 排除 null
        var sql = "SELECT ROUND(AVG(cycle_days), 4) AS metric_value, COUNT(*) AS numerator_value " +
                  "FROM dwd_t8_order_shipment_cycle " +
                  "WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date " +
                  "AND source_ztid = @ztid AND cycle_days IS NOT NULL";
        Assert.True(KpiSqlSecurityValidator.Validate(sql).Ok);
    }
}