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);
    }
}