SQL Server 通用 CRUD 儲存程序：動態 SQL 實作與 SQL 注入防範

前言

專案初期常需快速建立資料表的增刪改查介面。使用**動態 SQL** 撰寫通用 CRUD 程序可減少重複程式碼，但若防禦不當極易造成 **SQL 注入** 風險。本文提供四支通用程序範本，並詳細說明安全強化要點。

一、通用新增程序

CREATE PROCEDURE usp_Add
    @TableName NVARCHAR(255),
    @Values NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX) = N'INSERT INTO ' + QUOTENAME(@TableName);
    
    IF @Values IS NOT NULL
        SET @SQL += N' VALUES (' + @Values + N')';
    
    EXEC sp_executesql @SQL;
    SELECT SCOPE_IDENTITY() AS NewIdentity;  -- 回傳新增身分識別值
END
GO

-- 呼叫範例：值部分需自行處理字串逸出與型別轉換
EXEC usp_Add N'Stock', N'''台積電'', 20, 300';

二、通用刪除程序

CREATE PROCEDURE usp_Delete
    @TableName NVARCHAR(255),
    @WhereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX) = N'DELETE FROM ' + QUOTENAME(@TableName);
    
    IF @WhereClause IS NOT NULL
        SET @SQL += N' WHERE ' + @WhereClause;
    
    EXEC sp_executesql @SQL;
END
GO

EXEC usp_Delete N'Stock', N'Id = 1';

三、通用修改程序

CREATE PROCEDURE usp_Update
    @TableName NVARCHAR(255),
    @SetClause NVARCHAR(MAX),
    @WhereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX) = N'UPDATE ' + QUOTENAME(@TableName) + N' SET ' + @SetClause;
    
    IF @WhereClause IS NOT NULL
        SET @SQL += N' WHERE ' + @WhereClause;
    
    EXEC sp_executesql @SQL;
END
GO

EXEC usp_Update N'Stock', N'StockName = N''騰訊股份''', N'Id = 2';

四、通用查詢程序

CREATE PROCEDURE usp_Select
    @TableName NVARCHAR(255),
    @WhereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName);
    
    IF @WhereClause IS NOT NULL
        SET @SQL += N' WHERE ' + @WhereClause;
    
    EXEC sp_executesql @SQL;
END
GO

EXEC usp_Select N'Stock', N'Id = 1';

五、關鍵安全問題：SQL 注入風險分析

上述程序**直接拼接使用者輸入**至動態 SQL，極度危險：

-- 惡意呼叫範例
EXEC usp_Select N'Stock', N'1=1; DROP TABLE Stock; --';
-- 實際執行：SELECT * FROM [Stock] WHERE 1=1; DROP TABLE Stock; --

六、安全強化方案（三選一）

方案 A：參數化動態 SQL（推薦，相容性最好）

CREATE PROCEDURE usp_Select_Safe
    @TableName NVARCHAR(255),
    @Id INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE Id = @Id';
    EXEC sp_executesql @SQL, N'@Id INT', @Id = @Id;
END

• 資料表名仍需動態拼接（無法參數化），必須用 QUOTENAME 防注入。
• 所有**資料值**一律使用參數化。

方案 B：白名單驗證資料表名

IF @TableName NOT IN (N'Stock', N'Order', N'Customer')
BEGIN
    RAISERROR('Invalid table name', 16, 1);
    RETURN;
END

方案 C：改用 ORM / 參數化存儲程序

現代開發建議放棄通用 CRUD 程序，改用：

• Entity Framework / Dapper 等 ORM/Micro-ORM
• 針對每個實體撰寫專用參數化程序
• 資料庫層僅提供精確、安全的介面

七、動態 SQL 除錯技巧

DECLARE @SQL NVARCHAR(MAX) = ...;
PRINT @SQL;  -- 或 SELECT @SQL;
-- 複製輸出至新視窗執行，觀察實際 SQL 與執行計畫

八、總結

通用 CRUD 程序雖減少程式碼量，但**安全性成本極高**。生產環境**強烈建議**採用方案 C（專用參數化程序 + ORM），若必須保留動態 SQL，請務必實施方案 A + B 雙重防護。