温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

SQLSERVER中怎么解密存储过程

发布时间:2021-08-06 11:03:40 来源:亿速云 阅读:130 作者:Leah 栏目:数据库

SQLSERVER中怎么解密存储过程,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

  加密测试的存储过程

  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) begin="" set="" real_decrypt_01="" intprocspace="@intProcSpace+1" end="" insert="" output="" select="" --select="" real_decrypt="" as="" from="" --="" -------------------------------------="" declare="" dbname="" sysname="" blankspaceadded="0" int="" basepos="" currentpos="" textlength="" lineid="1" addonlen="" lfcr="2" definedlength="255" syscomtext="" line="" create="" table="" commenttext="" text="" collate="" sys.sysobjvalues="" ms_crs_syscom="" cursor="" local="" for="" order="" by="" ident="" read="" only="" open="" fetch="" next="" into="" 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 <= 1="" textlength="" begin="" while="" blankspaceadded=""> @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

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI