如何解密SQL2005视图和存储过程

SQLServer2005里使用with encryption选项创建的存储过程仍然和sqlserver2000里一样，都是使用XOR进行了的加密。和2000不一样的是，在2005的系统表syscomments里已经查不到加密过的密文了。要查密文必须使用DAC（专用管理员连接）连接到数据库后，在系统表sys.sysobjvalues查询，该表的列imageval存储了相应的密文。具体可以使用下面的查询：



SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) AND

valclass = 1 AND subobjid = 1



下面是解密的存储过程，具体代码如下(这是版本4.0，最新的，修正很长的存储过程解密出来是空白的问题)：



Create PROCEDURE [dbo].[sp__windbi$decrypt]

(@procedure sysname = NULL, @revfl int = 1)

AS

/*

王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com

调用形式为：

exec dbo.sp__windbi$decrypt @procedure,0

如果第二个参数使用1的话，会给出该存储过程的一些提示。

--版本4.0  修正存储过程过长解密出来是空白的问题

*/

SET NOCOUNT ON

IF @revfl = 1

BEGIN

PRINT '警告：该存储过程会删除并重建原始的存储过程。'

PRINT ' 在运行该存储过程之前确保你的数据库有一个备份。'

PRINT ' 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'

PRINT ' 为了运行这个存储过程，将参数@refl的值更改为0。'

RETURN 0

END

DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int

select @maxColID = max(subobjid) FROM

sys.sysobjvalues WHERE objid = object_id(@procedure)

--select @maxColID as 'Rows in sys.sysobjvalues'

select @procNameLength = datalength(@procedure) + 29

DECLARE @real_01 nvarchar(max)

DECLARE @fake_01 nvarchar(max)

DECLARE @fake_encrypt_01 nvarchar(max)

DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)

declare @objtype varchar(2),@ParentName nvarchar(max)

select @real_decrypt_01a = ''

-- 提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称

select @objtype=type,@parentname=object_name(parent_object_id)

from sys.objects where [object_id]=object_id(@procedure)

-- 从sys.sysobjvalues里提出加密的imageval记录

SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =

object_id(@procedure) and valclass = 1 order by subobjid)



--创建一个临时表

create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,

[real_decrypt] NVARCHAR(MAX) )

--开始一个事务，稍后回滚

BEGIN TRAN

--更改原始的存储过程，用短横线替换

if @objtype='P'

  SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'

else if @objtype='FN'

  SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'

else if @objtype='V'

  SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'

else if @objtype='TR'

  SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'

EXECUTE (@fake_01)

--从sys.sysobjvalues里提出加密的假的

SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =

object_id(@procedure) and valclass = 1 order by subobjid )

if @objtype='P'

  SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'

else if @objtype='FN'

  SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'

else if @objtype='V'

  SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'

else if @objtype='TR'

  SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)

  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'

--开始计数

SET @intProcSpace=1

--使用字符填充临时变量

SET @real_decrypt_01 = replicate(cast('A'as nvarchar(max)), (datalength(@real_01) /2 ))

--循环设置每一个变量，创建真正的变量

--每次一个字节

SET @intProcSpace=1

--如有必要，遍历每个@real_xx变量并解密

WHILE @intProcSpace<=(datalength(@real_01)/2)

BEGIN

--真的和假的和加密的假的进行异或处理

SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,

NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^

(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^

UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))

SET @intProcSpace=@intProcSpace+1

END





--通过sp_helptext逻辑向表#output里插入变量

insert #output (real_decrypt) select @real_decrypt_01

--select real_decrypt AS '#output chek' from #output --测试

-- -------------------------------------

--开始从sp_helptext提取

-- -------------------------------------

declare @dbname sysname

,@BlankSpaceAdded int

,@BasePos int

,@CurrentPos int

,@TextLength int

,@LineId int

,@AddOnLen int

,@LFCR int --回车换行的长度

,@DefinedLength int

,@SyscomText nvarchar(max)

,@Line nvarchar(255)

Select @DefinedLength = 255

SELECT @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格

CREATE TABLE #CommentText

(LineId int

,Text nvarchar(255) collate database_default)

--使用#output代替sys.sysobjvalues

DECLARE ms_crs_syscom CURSOR LOCAL

FOR SELECT real_decrypt from #output

ORDER BY ident

FOR READ ONLY

--获取文本

SELECT @LFCR = 2

SELECT @LineId = 1

OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0

BEGIN

SELECT @BasePos = 1

SELECT @CurrentPos = 1

SELECT @TextLength = LEN(@SyscomText)

WHILE @CurrentPos != 0

BEGIN

--通过回车查找行的结束

SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,

@BasePos)

--如果找到回车

IF @CurrentPos != 0

BEGIN

--如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +

@CurrentPos-@BasePos + @LFCR) > @DefinedLength

BEGIN

SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +

@BlankSpaceAdded)

INSERT #CommentText VALUES

( @LineId,

isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,

@BasePos, @AddOnLen), N''))

SELECT @Line = NULL, @LineId = @LineId + 1,

@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0

END

SELECT @Line = isnull(@Line, N'') +

isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')

SELECT @BasePos = @CurrentPos+2

INSERT #CommentText VALUES( @LineId, @Line )

SELECT @LineId = @LineId + 1

SELECT @Line = NULL

END

ELSE

--如果回车没找到

BEGIN

IF @BasePos <= @TextLength

BEGIN

--如果@Lines长度的新值大于定义的长度

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +

@TextLength-@BasePos+1 ) > @DefinedLength

BEGIN

SELECT @AddOnLen = @DefinedLength -

(isnull(LEN(@Line),0) + @BlankSpaceAdded)

INSERT #CommentText VALUES

( @LineId,

isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,

@BasePos, @AddOnLen), N''))

SELECT @Line = NULL, @LineId = @LineId + 1,

@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =

0

END

SELECT @Line = isnull(@Line, N'') +

isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')

if LEN(@Line) < @DefinedLength and charindex(' ',

@SyscomText, @TextLength+1 ) > 0

BEGIN

SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1

END

END

END

END

FETCH NEXT FROM ms_crs_syscom into @SyscomText

END

IF @Line is NOT NULL

INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE ms_crs_syscom

DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText

-- -------------------------------------

--结束从 sp_helptext提取

-- -------------------------------------

--删除用短横线创建的存储过程并重建原始的存储过程

ROLLBACK TRAN

DROP TABLE #output

go

sp_helptext '存储过程' --查看存储过程代码

/*================================================================================== 



NAME:          Decrypt SQL 2005 stored procedures, functions, views, and triggers 



DESCRIPTION:   HEADS UP: In order to run this script you must log in 

                        to the server in DAC mode: To do so, type 

                        ADMIN:<SQLInstanceName> as your server name and use the "sa" 

                        or any other server admin user with the appropriate password. 

                        CAUTION! DAC (dedicated admin access) will kick out all other 

                        server users. 

                        The script below accepts an object (schema name + object name) 

                        that were created using the WITH ENCRYPTION option and returns 

                        the decrypted script that creates the object. This script 

                        is useful to decrypt stored procedures, views, functions, 

                        and triggers that were created WITH ENCRYPTION. 

                        The algorithm used below is the following: 

                        1. Check that the object exists and that it is encrypted. 

                        2. In order to decrypt the object, the script ALTER (!!!) it 

                        and later restores the object to its original one. This is 

                        required as part of the decryption process: The object 

                        is altered to contain dummy text (the ALTER uses WITH ENCRYPTION) 

                        and then compared to the CREATE statement of the same dummy 

                        content. 

                        Note: The object is altered in a transaction, which is rolled 

                        back immediately after the object is changed to restore 

                        all previous settings. 

                        3. A XOR operation between the original binary stream of the 

                        enrypted object with the binary representation of the dummy 

                        object and the binary version of the object in clear-text 

                        is used to decrypt the original object.



USER PARAMETERS:        @ObjectOwnerOrSchema 

                        @ObjectName 



RESULTSET:              NA 



RESULTSET SORT:         NA 



USING TABLES/VIEWS:     sys.sysobjvalues 

                        syscomments 



REVISIONS 



DATE         DEVELOPER          DESCRIPTION OF REVISION             VERSION 

=========    ===============    =================================   =========== 

01/01/2007   Omri Bahat         Initial release                     1.00 



================================================================================== 

Copyright  SQL Farms Solutions, www.sqlfarms.com. All rights reserved. 

This code can be used only for non-redistributable purposes. 

The code can be used for free as long as this copyright notice is not removed. 

==================================================================================*/ 



DECLARE @ObjectOwnerOrSchema NVARCHAR(128) 

DECLARE @ObjectName NVARCHAR(128) 



SET @ObjectOwnerOrSchema = 'dbo' 

SET @ObjectName = 'myproc' 



DECLARE @i INT 

DECLARE @ObjectDataLength INT 

DECLARE @ContentOfEncryptedObject NVARCHAR(MAX) 

DECLARE @ContentOfDecryptedObject NVARCHAR(MAX) 

DECLARE @ContentOfFakeObject NVARCHAR(MAX) 

DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX) 

DECLARE @ObjectType NVARCHAR(128) 

DECLARE @ObjectID INT 



SET NOCOUNT ON 



SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']') 



-- Check that the provided object exists in the database. 

IF @ObjectID IS NULL 

BEGIN 

    RAISERROR('The object name or schema provided does not exist in the database', 16, 1) 

    RETURN 

END 



-- Check that the provided object is encrypted. 

IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1) 

BEGIN 

    RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1) 

    RETURN 

END 



-- Determine the type of the object 

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL 

    SET @ObjectType = 'PROCEDURE' 

ELSE 

    IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL 

        SET @ObjectType = 'TRIGGER' 

    ELSE 

        IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL 

            SET @ObjectType = 'VIEW' 

        ELSE 

            SET @ObjectType = 'FUNCTION' 



-- Get the binary representation of the object- syscomments no longer holds 

-- the content of encrypted object. 

SELECT TOP 1 @ContentOfEncryptedObject = imageval 

FROM sys.sysobjvalues 

WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']') 

        AND valclass = 1 and subobjid = 1 



SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2 



-- We need to alter the existing object and make it into a dummy object 

-- in order to decrypt its content. This is done in a transaction 

-- (which is later rolled back) to ensure that all changes have a minimal 

-- impact on the database. 

SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS' 



WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength 

BEGIN 

        IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength 

                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000) 

        ELSE 

                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) 

END 



-- Since we need to alter the object in order to decrypt it, this is done 

-- in a transaction 

SET XACT_ABORT OFF 

BEGIN TRAN 



EXEC(@ContentOfFakeObject) 



IF @@ERROR <> 0 

        ROLLBACK TRAN 



-- Get the encrypted content of the new "fake" object. 

SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval 

FROM sys.sysobjvalues 

WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']') 

        AND valclass = 1 and subobjid = 1 



IF @@TRANCOUNT > 0 

        ROLLBACK TRAN 



-- Generate a CREATE script for the dummy object text. 

SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS' 



WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength 

BEGIN 

        IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength 

                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000) 

        ELSE 

                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) 

END 



SET @i = 1 



--Fill the variable that holds the decrypted data with a filler character 

SET @ContentOfDecryptedObject = N'' 



WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength 

BEGIN 

        IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength 

                SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000) 

        ELSE 

                SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2)) 

END 



WHILE @i <= @ObjectDataLength 

BEGIN 

        --xor real & fake & fake encrypted 

        SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1, 

                NCHAR( 

                        UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^ 

                        ( 

                                UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ 

                                UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1)) 

                        ))) 



        SET @i = @i + 1 

END 



-- PRINT the content of the decrypted object 

PRINT(@ContentOfDecryptedObject)



分享到：