当前位置:七道奇文章资讯数据防范MSSQL防范
日期:2011-05-02 15:21:00  来源:本站整理

Decryptencryptedstoredprocedures[MSSQL防范]

赞助商链接



  本文“Decryptencryptedstoredprocedures[MSSQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci837799,00.html绿色c hinaip ower. comVg09F


绿色c hinaip ower. comVg09F

This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption". It is adapted from a script by Joseph Gama and ShoeBoy. There are two versions: one for SP's only and the other one for SP's, triggers and views. For version 1, the input is object name (stored procedure, view or trigger), and for version 2, the input is object name (stored procedure, view or trigger), object type ('T'-trigger, 'P'-stored procedure or 'V'-view). From PlanetSourceCode.com.绿色c hinaip ower. comVg09F

create  PROCEDURE sp_decrypt_sp (@objectName varchar(50))

AS

DECLARE  @OrigSpText1 nvarchar(4000),  @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)

declare  @i int , @t bigint



--get encrypted data

SET @OrigSpText1=(SELECT ctext FROM syscomments  WHERE id = object_id(@objectName))

SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)

EXECUTE (@OrigSpText2)



SET @OrigSpText3=(SELECT ctext FROM syscomments  WHERE id = object_id(@objectName))

SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)



--start counter

SET @i=1

--fill temporary variable

SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))



--loop

WHILE @i<=datalength(@OrigSpText1)/2

BEGIN

--reverse encryption (XOR original+bogus+bogus encrypted)

SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^

                                (UNICODE(substring(@OrigSpText2, @i, 1)) ^

                                UNICODE(substring(@OrigSpText3, @i, 1)))))

 SET @i=@i+1

END

--drop original SP

EXECUTE ('drop PROCEDURE '+ @objectName)

--remove encryption

--preserve case

SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', ')

SET @resultsp=REPLACE((@resultsp),'With Encryption', ')

SET @resultsp=REPLACE((@resultsp),'with encryption', ')

IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0 

  SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', ')

--replace Stored procedure without enryption

execute( @resultsp)

GO

绿色c hinaip ower. comVg09F

Reader Feedback

绿色c hinaip ower. comVg09F

Joakim M. writes: I tried this script with mixed results. It works for some encrypted procedures, but for others I get error meassages like:绿色c hinaip ower. comVg09F

Server: Msg 512, Level 16, State 1, Procedure sp_decrypt_sp, Line 7.

Subquery returned more than 1 value. This is not permitted when the 

subquery follows =, !=, <, <= , >, >= or when the subquery is used as 

an expression.

Karl C writes: I got the same message as Joakim M. but upon further investigation I found that this happens only when stored procedures exceed 4000 characters. When this happens, sql server stores the procedure across multiple rows so you get the error 'subquery returne more than 1 row'. To get around that you can change the statement绿色c hinaip ower. comVg09F

SELECT ctext FROM syscomments WHERE id = object_id(@objectName



to 

SELECT top 1 ctext FROM syscomments WHERE id = object_id(@objectName order by colid

That will get you the first part of the stored procedure, which can't be created because it is missing the end part and is not a valid syntax but you can print @resultsp out to see it.绿色c hinaip ower. comVg09F

For More Information

绿色c hinaip ower. comVg09F

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Best Web Links: SQL Server tips, tutorials, scripts, and more.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Ask the Experts: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  以上是“Decryptencryptedstoredprocedures[MSSQL防范]”的内容,如果你对以上该文章内容感兴趣,你可以看看七道奇为您推荐以下文章:
  • Decryptencryptedstoredprocedures
  • 本文地址: 与您的QQ/BBS好友分享!
    • 好的评价 如果您觉得此文章好,就请您
        0%(0)
    • 差的评价 如果您觉得此文章差,就请您
        0%(0)

    文章评论评论内容只代表网友观点,与本站立场无关!

       评论摘要(共 0 条,得分 0 分,平均 0 分) 查看完整评论
    Copyright © 2020-2022 www.xiamiku.com. All Rights Reserved .