編寫安全的 Transact-SQL：最小權限、參數化與防注入實戰

核心原則：纏繞式防禦

安全 T-SQL 非單一技巧，而是**開發、部署、執行**三階段的纏繞防禦：

1. 開發期：參數化、最小權限、靜態分析、代碼審查。
2. 部署期：簽署模組、加密、權限收斂、稽核基線。
3. 執行期：動態管理檢視監控、擴充事件審計、資料遮罩、進階威脅防護。

1. 參數化查詢：防注入基石

-- ❌ 錯誤：字串拼接 (SQL Injection 風險)
DECLARE @SQL nvarchar(max) = N'SELECT * FROM Users WHERE UserName = ''' + @UserName + '''';
EXEC (@SQL);

-- ✅ 正確：sp_executesql 參數化 (計畫重用、型別安全、防注入)
DECLARE @SQL nvarchar(max) = N'SELECT * FROM Users WHERE UserName = @UserName';
EXEC sp_executesql @SQL, N'@UserName nvarchar(50)', @UserName = @UserName;

-- ✅ 正確：預存程序參數 (最佳封裝)
CREATE PROCEDURE dbo.usp_GetUser @UserName nvarchar(50)
AS SELECT * FROM Users WHERE UserName = @UserName;

**鐵律**：所有動態 SQL、應用程式呼叫、跨資料庫查詢，**絕對禁止**字串拼接使用者輸入。

2. 最小權限原則 (Least Privilege)

3. 危險功能封存與審計

• 停用/移除擴展預存程序：xp_cmdshell, xp_regread, xp_regwrite, sp_OA* 等。EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
• CLR 嚴格安全性 (SQL 2017+)：EXEC sp_configure 'clr strict security', 1; RECONFIGURE; 所有組件需簽署/信任。
• 啟用 SQL Server Audit / Extended Events**：記錄失敗登入、權限變更、DDL、敏感資料存取、動態 SQL 執行 (sql_statement_completed 含參數值)。
• 進階威脅防護 (ATP)**：Azure SQL / SQL 2019+ 內建 SQL 注入、異常存取模式、暴力破解偵測與警示。

4. 開發流程整合

1. 靜態程式碼分析 (SAST)**：CI/CD 管線整合 SqlCodeGuard, TSQLLint, SonarQube (TSQL Plugin) 掃描：動態 SQL 拼接、硬編碼密碼、缺少 TRY/CATCH、未參數化。
2. 單元測試 (tSQLt)**：測試注入嘗試被正確拒絕、權限邊界正確、遮罩生效。
3. 代碼審查清單**：每個 PR 必檢：參數化、權限、錯誤處理、敏感資料、加密、日誌不洩露。

5. 實戰範本：安全預存程序模板

CREATE PROCEDURE [dbo].[usp_SecureTemplate]
    @Param1 int,
    @Param2 nvarchar(100)
WITH EXECUTE AS CALLER  -- 或指定低權限 Proxy 使用者
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;  -- 錯誤即回滾、不回傳行數
    BEGIN TRY
        -- 1. 輸入驗證 (白名單/範圍/長度)
        IF @Param1 <= 0 THROW 50001, N'Invalid Param1', 1;
        IF LEN(@Param2) > 100 THROW 50002, N'Param2 too long', 1;

        -- 2. 核心邏輯 (參數化、無動態 SQL)
        SELECT Col1, Col2
        FROM dbo.SensitiveTable
        WHERE ID = @Param1 AND Code = @Param2;  -- 索引求值

        -- 3. 審計日誌 (非同步寫入 Service Broker / 彈性資料表)
        INSERT INTO dbo.AuditLog (ProcName, ParamJSON, UserName, EventTime)
        VALUES (OBJECT_NAME(@@PROCID), (SELECT @Param1 AS P1, @Param2 AS P2 FOR JSON PATH), SUSER_SNAME(), SYSDATETIME());
    END TRY
    BEGIN CATCH
        -- 統一錯誤處理：記錄、不洩露堆疊、回傳友善代碼
        DECLARE @ErrMsg nvarchar(4000) = ERROR_MESSAGE();
        INSERT INTO dbo.ErrorLog (ProcName, ErrorMsg, ErrorTime) VALUES (OBJECT_NAME(@@PROCID), @ErrMsg, SYSDATETIME());
        THROW;  -- 重新拋出保留錯誤資訊
    END CATCH
END

總結：安全 T-SQL 檢查清單

[ ] 所有動態 SQL 使用 sp_executesql 參數化
[ ] 應用帳號最小權限、角色管理、模組簽署提權
[ ] 敏感欄位動態遮罩 / Always Encrypted
[ ] xp_cmdshell / sp_OA* / 不必要擴展程序 已停用
[ ] CLR 嚴格安全性啟用
[ ] SQL Audit / Extended Events 記錄關鍵事件
[ ] CI/CD 整合 SAST (TSQLLint/SonarQube) + tSQLt 單元測試
[ ] 代碼審查強制檢查參數化、權限、錯誤處理、敏感資料
[ ] 錯誤訊息不回傳前端、統一友善代碼
[ ] 定期滲透測試、ATP 警示訂閱