您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 預存程序/函數/檢視表/觸發器加密與解密技術深度解析

編輯：關於SqlServer

加密機制：WITH ENCRYPTION

建立或修改物件時加上 WITH ENCRYPTION，SQL Server 會將 syscomments 中的原始碼加密儲存。sp_helptext、Enterprise Manager 將無法檢視。

解密原理：XOR 還原演算法

加密過程實為：

Encrypted = Original XOR Bogus_Header XOR Bogus_Encrypted

其中 Bogus_Header 為已知的標準建立腳本（如 CREATE PROCEDURE ... WITH ENCRYPTION AS --....），Bogus_Encrypted 為對同樣 Bogus_Header 執行加密後的結果。解密公式：

Original = Encrypted XOR Bogus_Header XOR Bogus_Encrypted

完整解密預存程序碼（sp_decrypt）

支援預存程序、函數、檢視表、觸發器；處理多行 syscomments（colid 分段）；自動移除 WITH ENCRYPTION 關鍵字；以交易包裹並最終 ROLLBACK，不破壞原物件。

CREATE PROCEDURE sp_decrypt @objectName sysname
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRAN

    DECLARE @type char(2), @parentid int
    SELECT @type = xtype, @parentid = parent_obj FROM sysobjects WHERE id = OBJECT_ID(@objectName)

    CREATE TABLE #temp (number int, colid int, ctext varbinary(8000), encrypted int, status int)
    INSERT #temp SELECT number, colid, ctext, encrypted, status FROM syscomments WHERE id = OBJECT_ID(@objectName)

    DECLARE @k int = 0, @maxNum int = (SELECT MAX(number) FROM #temp)
    WHILE @k <= @maxNum
    BEGIN
        IF EXISTS (SELECT 1 FROM syscomments WHERE id = OBJECT_ID(@objectName) AND number = @k)
        BEGIN
            -- 1. 建立同名 Bogus 物件（含 WITH ENCRYPTION）
            DECLARE @sql nvarchar(4000)
            -- 依 @type 動態產生 CREATE/ALTER 腳本...
            EXEC (@sql)  -- 產生 Bogus_Encrypted

            -- 2. 逐 colid 還原
            DECLARE @n int = 1, @maxCol int = (SELECT MAX(colid) FROM #temp WHERE number = @k)
            WHILE @n <= @maxCol
            BEGIN
                SELECT @OrigSpText1 = ctext, @encrypted = encrypted, @status = status
                FROM #temp WHERE colid = @n AND number = @k
                SELECT @OrigSpText3 = ctext FROM syscomments
                WHERE id = OBJECT_ID(@objectName) AND colid = @n AND number = @k

                -- 建立 Bogus_Header
                IF @n = 1 SET @OrigSpText2 = 'CREATE PROCEDURE ... WITH ENCRYPTION AS ' + REPLICATE('-', 4000 - LEN(...))
                ELSE SET @OrigSpText2 = REPLICATE('-', 4000)

                -- XOR 還原
                DECLARE @i int = 1, @len int = DATALENGTH(@OrigSpText1)/2
                DECLARE @result nvarchar(4000) = REPLICATE(N'A', @len)
                WHILE @i <= @len
                BEGIN
                    SET @result = STUFF(@result, @i, 1,
                        NCHAR(UNICODE(SUBSTRING(@OrigSpText1, @i, 1)) ^
                              (UNICODE(SUBSTRING(@OrigSpText2, @i, 1)) ^
                               UNICODE(SUBSTRING(@OrigSpText3, @i, 1)))))
                    SET @i = @i + 1
                END

                -- 移除 WITH ENCRYPTION 並輸出
                SET @result = REPLACE(REPLACE(REPLACE(@result, 'WITH ENCRYPTION', ''), 'With Encryption', ''), 'with encryption', '')
                PRINT @result
                SET @n = @n + 1
            END
        END
        SET @k = @k + 1
    END

    DROP TABLE #temp
    ROLLBACK TRAN
END