解密SQL SERVER 2005加密存储过程，函数

yt-summer

www.diy567.com累了，去上面休息一下下,QQ空间，美文，非主流，网络日记，搞笑短信，祝福短信，热门短信，有意思啊
在SQL SERVER 2005中必须用专用管理连接才可以查看过程过程中用到的表
EG：sqlcmd -A
1>use test
2>go
1>sp_decrypt 'p_testa'
2>go
Text
----------------------
Create procedure P_testa
with encryption
as
select * from test
createPROCEDURE[dbo].[sp_decrypt]
(@proceduresysname=NULL)
AS
SETNOCOUNTON
BEGIN
DECLARE@intProcSpacebigint,@tbigint,@maxColIDsmallint,@intEncrypted
tinyint,@procNameLengthint
select@maxColID=max(subobjid),@intEncrypted=imagevalFROM
sys.sysobjvaluesWHEREobjid=object_id(@procedure)
GROUPBYimageval

select@procNameLength=datalength(@procedure)+29
DECLARE@real_01nvarchar(max)
DECLARE@fake_01nvarchar(max)
DECLARE@fake_encrypt_01nvarchar(max)
DECLARE@real_decrypt_01nvarchar(max),@real_decrypt_01anvarchar(max)
declare@objtypevarchar(2),@ParentNamenvarchar(max)
select@real_decrypt_01a=''
--提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称
select@objtype=type,@parentname=object_name(parent_object_id)
fromsys.objectswhere[object_id]=object_id(@procedure)
--从sys.sysobjvalues里提出加密的imageval记录
SET@real_01=(SELECTtop1imagevalFROMsys.sysobjvaluesWHEREobjid=
object_id(@procedure)andvalclass=1orderbysubobjid)
--创建一个临时表
createtable#output([ident][int]IDENTITY(1,1)NOTNULL,
[real_decrypt]NVARCHAR(MAX))
--开始一个事务，稍后回滚
BEGINTRAN
--更改原始的存储过程，用短横线替换
if@objtype='P'
SET@fake_01='ALTERPROCEDURE'+@procedure+'WITHENCRYPTIONAS
'+REPLICATE('-',40003-@procNameLength)
elseif@objtype='FN'
SET@fake_01='ALTERFUNCTION'+@procedure+'()RETURNSINTWITHENCRYPTIONASBEGINRETURN1
/*'+REPLICATE('*',datalength(@real_01)/2-@procNameLength)+'*/END'
elseif@objtype='V'
SET@fake_01='ALTERview'+@procedure+'WITHENCRYPTIONASselect1ascol
/*'+REPLICATE('*',datalength(@real_01)/2-@procNameLength)+'*/'
elseif@objtype='TR'
SET@fake_01='ALTERtrigger'+@procedure+'ON'+@parentname+'WITHENCRYPTIONAFTERINSERTASRAISERROR(''N'',16,10)
/*'+REPLICATE('*',datalength(@real_01)/2-@procNameLength)+'*/'
EXECUTE(@fake_01)
--从sys.sysobjvalues里提出加密的假的
SET@fake_encrypt_01=(SELECTtop1imagevalFROMsys.sysobjvaluesWHEREobjid=
object_id(@procedure)andvalclass=1orderbysubobjid)
if@objtype='P'
SET@fake_01='CreatePROCEDURE'+@procedure+'WITHENCRYPTIONAS
'+REPLICATE('-',40003-@procNameLength)
elseif@objtype='FN'
SET@fake_01='CREATEFUNCTION'+@procedure+'()RETURNSINTWITHENCRYPTIONASBEGINRETURN1
/*'+REPLICATE('*',datalength(@real_01)/2-@procNameLength)+'*/END'
elseif@objtype='V'
SET@fake_01='Createview'+@procedure+'WITHENCRYPTIONASselect1ascol
/*'+REPLICATE('*',datalength(@real_01)/2-@procNameLength)+'*/'
elseif@objtype='TR'
SET@fake_01='Createtrigger'+@procedure+'ON'+@parentname+'WITHENCRYPTIONAFTERINSERTASRAISERROR(''N'',16,10)
/*'+REPLICATE('*',datalength(@real_01)/2-@procNameLength)+'*/'
--开始计数
SET@intProcSpace=1
--使用字符填充临时变量
SET@real_decrypt_01=replicate(N'A',(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
--selectreal_decryptAS'#outputchek'from#output--测试
---------------------------------------
--开始从sp_helptext提取
---------------------------------------
declare@dbnamesysname
,@BlankSpaceAddedint
,@BasePosint
,@CurrentPosint
,@TextLengthint
,@LineIdint
,@AddOnLenint
,@LFCRint--回车换行的长度
,@DefinedLengthint
,@SyscomTextnvarchar(4000)
,@Linenvarchar(255)
Select@DefinedLength=255
SELECT@BlankSpaceAdded=0--跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATETABLE#CommentText
(LineIdint
,Textnvarchar(255)collatedatabase_default)
--使用#output代替sys.sysobjvalues
DECLAREms_crs_syscomCURSORLOCAL
FORSELECTreal_decryptfrom#output
ORDERBYident
FORREADONLY
--获取文本
SELECT@LFCR=2
SELECT@LineId=1
OPENms_crs_syscom
FETCHNEXTFROMms_crs_syscominto@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#CommentTextVALUES
(@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#CommentTextVALUES(@LineId,@Line)
SELECT