Shulou(Shulou.com)05/31 Report--

SQLSERVER how to decrypt stored procedures, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Stored procedure for encryption test

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=' this user name exists'

ELSE

SET @ MSG=' this username does not exist'

END

Decrypted stored procedure

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 =''

-- extract whether the type of object is a stored procedure or a function, and if it is a trigger, get the name of its parent object

Select @ objtype=type,@parentname=object_name (parent_object_id)

From sys.objects where [object_id] = object_id (@ procedure)

-- extract encrypted imageval records from sys.sysobjvalues

SET @ real_01= (SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =

Object_id (@ procedure) and valclass = 1 order by subobjid)

-- create a temporary table

Create table # output ([ident] [int] IDENTITY (1,1) NOT NULL

[real_decrypt] NVARCHAR (MAX))

-- start a transaction and roll back later

BEGIN TRAN

Change the original stored procedure and replace it with a dash

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

/ * / /'+ REPLICATE (cast ('*'as nvarchar (max)), datalength (@ real_01) / 2-@ procNameLength) +'* /'

EXECUTE (@ fake_01)

-- extract encrypted fake from 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

/ * / /'+ REPLICATE (cast ('*'as nvarchar (max)), datalength (@ real_01) / 2-@ procNameLength) +'* /'

-- start counting

SET @ intProcSpace=1

-populating temporary variables with characters

SET @ real_decrypt_01 = replicate (cast ('A'as nvarchar (max)), (datalength (@ real_01) / 2))

Loop through each variable to create a real variable

-- one byte at a time

SET @ intProcSpace=1

If necessary, iterate through each @ real_xx variable and decrypt

WHILE @ intProcSpace= 0

BEGIN

SELECT @ BasePos = 1

SELECT @ CurrentPos = 1

SELECT @ TextLength = LEN (@ SyscomText)

WHILE @ CurrentPos! = 0

BEGIN

-- enter to find the end of the line

SELECT @ CurrentPos = CHARINDEX (char (13) + char (10), @ SyscomText

@ BasePos)

-- if you find enter,

IF @ CurrentPos! = 0

BEGIN

-- if the new value of the length of @ Lines is larger than the set value, insert the current content of @ Lines and continue

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, Line') + isnull (SUBSTRING (@ SyscomText)

@ BasePos, @ AddOnLen), nasty'))

SELECT @ Line = NULL, @ LineId = @ LineId + 1

@ BasePos = @ BasePos + @ AddOnLen, @ BlankSpaceAdded = 0

END

SELECT @ Line = isnull (@ Line, nasty') +

Isnull (SUBSTRING (@ SyscomText, @ BasePos, @ CurrentPos-@BasePos + @ LFCR), Nissan')

SELECT @ BasePos = @ CurrentPos+2

INSERT # CommentText VALUES (@ LineId, @ Line)

SELECT @ LineId = @ LineId + 1

SELECT @ Line = NULL

END

ELSE

-- if the enter is not found

BEGIN

IF @ BasePos @ DefinedLength

BEGIN

SELECT @ AddOnLen = @ DefinedLength-

(isnull (LEN (@ Line), 0) + @ BlankSpaceAdded)

INSERT # CommentText VALUES

(@ LineId

Isnull (@ Line, Line') + isnull (SUBSTRING (@ SyscomText)

@ BasePos, @ AddOnLen), nasty'))

SELECT @ Line = NULL, @ LineId = @ LineId + 1

@ BasePos = @ BasePos + @ AddOnLen, @ BlankSpaceAdded =

0

END

SELECT @ Line = isnull (@ Line, nasty') +

Isnull (SUBSTRING (@ SyscomText, @ BasePos, @ TextLength-@BasePos+1), nasty')

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

-- end the extraction from sp_helptext

Delete the stored procedure created with dash and rebuild the original stored procedure

ROLLBACK TRAN

DROP TABLE # output

GO

Enable DAC

SP_CONFIGURE 'remote admin connections'

GO

-0: only allow local connections to use DAC,1: allow remote connections to use DAC

SP_CONFIGURE 'remote admin connections', 0

GO

RECONFIGURE WITH OVERRIDE

GO

File-- > New-- > Database engine query

Server name: admin:. Or admin: server name

Log in to perform the decryption operation

USE TEST

EXEC Decryption P_TEST

GO

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.