Decrypt encrypted stored procedures

原创 2003年02月07日 12:54:00

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci837799,00.html


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.

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

Reader Feedback

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

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

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.

For More Information

  • 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.

stored procedures 存储过程介绍

什么是存储过程呢? The stored procedures is a set of sqls which have been compiled and stored in the web...
  • sbitswc
  • sbitswc
  • 2013年03月04日 22:50
  • 595

Stored Procedure存储过程基础知识详解

存储过程是做项目的必备技术,只要你面试找工作,数据库及存储过程也是必考的,下面一起来了解存储过程的简单基本技术知识: 一、存储过程基本概念? 存储过程就是作为可执行对象存放在数据库中的一个或多个S...
  • u013519551
  • u013519551
  • 2017年05月19日 15:04
  • 512

HTTPS到底是个啥玩意儿?

https到底对http干了什么?
  • zgwangbo
  • zgwangbo
  • 2016年03月14日 21:09
  • 4858

Hive文件格式(表STORE AS 的四种类型)

hive文件存储格式包括以下几类: 1、TEXTFILE 2、SEQUENCEFILE 3、RCFILE 4、ORCFILE(0.11以后出现) 其中TEXTFILE为默认格式,建表时不指定...
  • kexinmei
  • kexinmei
  • 2014年12月26日 14:24
  • 4105

Stored Outline存储提纲介绍

Stored Outline存储提纲
  • f88520402
  • f88520402
  • 2013年12月09日 00:13
  • 1549

TLS/SSL 实例分析

TLS/SSL 示例分析 简介 TLS/SSL 协议并且 在 kali linux 下用 wireshark抓取 curl https://www.baidu.com 的数据 详细分析这个会话的数据,...
  • zhenyangzhijia
  • zhenyangzhijia
  • 2016年01月26日 16:48
  • 464

恢复勒索病毒“永恒之蓝”中招的文件,ooops,your files have been encrypted!解决方案

以下内容包含普通电脑解决方案、云服务器解决方案、以及预防方法、文件日常免费自动备份方案     半夜被运维值班人员的紧急电话惊醒,阿里云ECS服务器业务停止了,迷迷糊糊打开手机远程桌面,无法连接。无...
  • wjp20042005
  • wjp20042005
  • 2017年05月13日 16:48
  • 3109

Hive-RCFile文件存储格式

RCFile的文件存储格式,可以和ORC格式进行对比
  • dabokele
  • dabokele
  • 2016年06月03日 23:14
  • 1668

SQL Server中存储过程Stored Procedure创建及C#调用

存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL语句;可供应用程序直接调用。使用存储过程有以下几个优点: 1、执行速度比普通的SQL语句快 2、便于集中控制 3、可以降低网络的通...
  • tanzhangwen
  • tanzhangwen
  • 2015年02月25日 15:36
  • 3342

JAVA之——DES加密解密(插曲)

直接上代码,大家都懂的 package com.cdsmartlink.des; import java.security.*; import javax.crypto.Cipher; import ...
  • l1028386804
  • l1028386804
  • 2015年05月27日 17:29
  • 1142
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Decrypt encrypted stored procedures
举报原因:
原因补充:

(最多只允许输入30个字)