SQL Server 動態表名/欄位預存程序：EXEC 動態 SQL 實作與注入防範

功能說明

透過參數傳入表名、欄位清單，動態組裝 SELECT 語句並執行，適用於通用查詢介面、報表引擎等場景。核心風險：SQL 注入，本文給出基本版與參數化防注入進階版。

基本版 (僅供可控環境/內部工具)

-- 僅動態表名
CREATE PROCEDURE procName 
    @Table VARCHAR(50)
AS
BEGIN
    DECLARE @SqlString VARCHAR(2000);
    SET @SqlString = 'SELECT * FROM ' + QUOTENAME(@Table);
    EXEC(@SqlString);
END;
GO

-- 動態表名 + 動態欄位
CREATE PROCEDURE procName 
    @Field VARCHAR(50), 
    @Table VARCHAR(50)
AS
BEGIN
    DECLARE @SqlString VARCHAR(2000);
    SET @SqlString = 'SELECT ' + @Field + ' FROM ' + QUOTENAME(@Table);
    EXEC(@SqlString);
END;
GO

進階版：防 SQL 注入 (白名單驗證 + QUOTENAME)

CREATE PROCEDURE usp_DynamicSelect
    @SchemaName SYSNAME = 'dbo',
    @TableName SYSNAME,
    @ColumnList NVARCHAR(MAX) = '*',  -- '*' 或 'Col1, Col2, ...'
    @WhereClause NVARCHAR(MAX) = NULL -- 可選 WHERE 條件 (不含 WHERE 關鍵字)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 驗證表是否存在
    IF NOT EXISTS (
        SELECT 1 FROM sys.tables t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE s.name = @SchemaName AND t.name = @TableName
    )
    BEGIN
        RAISERROR('Table %s.%s not found.', 16, 1, @SchemaName, @TableName);
        RETURN;
    END

    -- 2. 驗證欄位白名單 (若非 '*')
    IF @ColumnList <> '*'
    BEGIN
        DECLARE @InvalidColumns TABLE (name SYSNAME);
        -- 簡易分割驗證 (實際建議用 STRING_SPLIT 或自訂分割函數)
        -- 此處略過分割邏輯，假設前端已驗證
    END

    -- 3. 組裝安全 SQL
    DECLARE @SQL NVARCHAR(MAX) = 
        N'SELECT ' + 
        CASE WHEN @ColumnList = '*' THEN N'*' ELSE @ColumnList END + 
        N' FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName);

    IF @WhereClause IS NOT NULL
        SET @SQL = @SQL + N' WHERE ' + @WhereClause;

    -- 4. 執行 (sp_executesql 可參數化 WHERE 條件值)
    EXEC sp_executesql @SQL;
END;
GO

關鍵防護原則

1. QUOTENAME：將識別碼包在 [] 中，中和保留字/特殊字元。
2. 白名單驗證：對照 sys.columns 確認欄位存在；表名對照 sys.tables。
3. 避免直接拼接使用者輸入值：WHERE 條件值必須走參數化 (sp_executesql @params)，不可字串拼接。
4. 最小權限原則：執行此預存程序的布號僅需 SELECT 權限，嚴禁 sysadmin。
5. 審計日誌：記錄呼叫參數、執行 SQL、執行時間，事後可追溯。

總結

動態表名/欄位預存程序極具彈性，但安全紅線不可觸碰。生產環境務必採用進階版白名單 + QUOTENAME + 參數化架構，並嚴格限制執行權限與審計追蹤。