萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 動態 SQL 通用 CRUD 儲存程序:風險與替代方案

程式碼展示

原文提供四個動態 SQL 程序,接受資料表名與條件字串,組合執行:

新增

CREATE PROCEDURE usp_add
    @table NVARCHAR(255),
    @values NVARCHAR(MAX) = NULL
AS
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'INSERT INTO ' + @table;
    IF @values IS NOT NULL
        SET @sql = 'INSERT INTO ' + @table + ' VALUES(' + @values + ')';
    EXEC sp_executesql @sql;
    SELECT @@IDENTITY;
GO

刪除

CREATE PROCEDURE usp_delete
    @table NVARCHAR(255),
    @where NVARCHAR(MAX) = NULL
AS
    DECLARE @sql NVARCHAR(MAX) = 'DELETE ' + @table;
    IF @where IS NOT NULL SET @sql += ' WHERE ' + @where;
    EXEC sp_executesql @sql;
GO

修改

CREATE PROCEDURE usp_update
    @table NVARCHAR(255),
    @set NVARCHAR(MAX),
    @where NVARCHAR(MAX) = NULL
AS
    DECLARE @sql NVARCHAR(MAX) = 'UPDATE ' + @table + ' SET ' + @set;
    IF @where IS NOT NULL SET @sql += ' WHERE ' + @where;
    EXEC sp_executesql @sql;
GO

查詢

CREATE PROCEDURE usp_select
    @table NVARCHAR(255),
    @where NVARCHAR(MAX) = NULL
AS
    DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + @table;
    IF @where IS NOT NULL SET @sql += ' WHERE ' + @where;
    EXEC sp_executesql @sql;
GO

嚴重安全風險:SQL 注入

上述程序直接拼接使用者輸入,極易遭受 SQL 注入攻擊:

-- 攻擊範例
EXEC usp_select 'Users', '1=1; DROP TABLE Users; --';

為何不建議使用此模式

  • 無法參數化:資料表/欄位名稱無法使用參數,必須拼接
  • 權限失控:呼叫者需具備目標表的直接 DML 權限,違反最小權限原則
  • 計畫快取汙染:每組不同參數產生獨立計畫,佔用記憶體
  • 除錯困難:動態 SQL 錯誤堆疊不清晰
  • 效能差:無法重用執行計畫,每次重新編譯

安全替代方案

1. 具體化儲存程序(推薦)

CREATE PROCEDURE usp_AddUser
    @Name NVARCHAR(50),
    @Age INT,
    @Salary DECIMAL(10,2)
AS
    INSERT INTO Users (Name, Age, Salary) VALUES (@Name, @Age, @Salary);
    SELECT SCOPE_IDENTITY();

2. 受控動態 SQL:白名單驗證

CREATE PROCEDURE usp_SafeSelect
    @table SYSNAME,          -- sysname = nvarchar(128)
    @where NVARCHAR(MAX) = NULL
AS
    -- 驗證資料表存在且在允許清單
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @table AND schema_id = SCHEMA_ID('dbo'))
        THROW 50001, 'Invalid table', 1;

    -- 簡單 WHERE 條件驗證(示意,實務需完整解析器)
    IF @where IS NOT NULL AND @where LIKE '%;%' THROW 50002, 'Invalid where', 1;

    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.' + QUOTENAME(@table);
    IF @where IS NOT NULL SET @sql += N' WHERE ' + @where;
    EXEC sp_executesql @sql;
GO

3. 使用 VIEW + 參數化查詢

建立檢視表封裝複雜聯結,應用層僅對檢視發送參數化查詢。

4. ORM / 參數化查詢

應用層使用 Entity Framework、Dapper 等 ORM,自動參數化,杜絕注入。

總結

通用動態 CRUD 程序雖看似靈活,實則引入極大安全與維護風險。現代開發應採用具體化儲存程序或參數化 ORM 查詢,若確需動態 SQL,務必實施嚴格白名單驗證與最小權限原則。

copyright © 萬盛學電腦網 all rights reserved