SQLSERVER对加密的存储过程、视图、触发器进行解密(推荐)
程序员文章站
2022-05-09 08:10:41
加密测试的存储过程
if exists(select 1 from sysobjects where type='p' and name='p_test')
drop...
加密测试的存储过程
if exists(select 1 from sysobjects where type='p' and name='p_test') drop procedure p_test go create procedure p_test(@username varchar(20),@msg varchar(20) output) with encryption as begin if(select count(1) from custs where name=@username)>0 set @msg='此用户名存在' else set @msg='此用户名不存在' end
解密的存储过程
create procedure decryption(@procedure sysname = null) as set nocount on 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 启用dac sp_configure 'remote admin connections'; go ----0:仅允许本地连接使用 dac,1:允许远程连接使用 dac sp_configure 'remote admin connections', 0; go reconfigure with override; go
文件——>新建——>数据库引擎查询
服务器名称:admin:.或者admin:服务器名称
登录进去执行解密操作
use test exec decryption p_test go
为了安全起见,不要在正式环境进行解密,避免在解密过程中将原存储过程损坏!
总结
以上所述是小编给大家介绍的sqlserver对加密的存储过程、视图、触发器进行解密,希望对大家有所帮助
上一篇: 谷歌:准备了在VR系统里打字的Daydream键盘应用
下一篇: 解析光纤通道存储网络的分区问题