SQL server encrypts and decrypts stored procedures and functions

ENCRYPTION Keyword: ENCRYPTION

CREATE procedure dbo. sp_EytTest </p> <p> @ para varchar (50) <br/> with encryption <br/> as <br/> begin <br/> select @ para <br/> end <br/> go <br/>

 

Decryption:

The stored process of decryption is from the network and needs to be used under DAC.

Decrypt SQL server 2000:

Create PROCEDURE [dbo]. [sp_decrypt] (@ objectname varchar (50 )) <br/> AS <br/> begin <br/> set nocount on <br/> begin tran <br/> declare @ objectname1 varchar (100 ), @ orgvarbin varbinary (8000) <br/> declare @ sql1 nvarchar (4000), @ sql2 varchar (8000), @ sql3 nvarchar (4000), @ sql4 nvarchar (4000) <br/> DECLARE @ OrigSpText1 nvarchar (4000), @ OrigSpText2 nvarchar (4000), @ OrigSpText3 nvarchar (4000), @ resultsp nvarchar (4000) <br/> declare @ I int, @ status int, @ type varchar (10), @ parentid int <br/> declare @ colid int, @ n int, @ q int, @ j int, @ k int, @ encrypted int, @ number int <br/> select @ type = xtype, @ parentid = parent_obj from sysobjects where id = object_id (@ objectname) <br/> create table # temp (number int, colid int, ctext varbinary (8000), encrypted int, status int) <br/> insert # temp SELECT number, colid, ctext, encrypted, status FROM syscomments WHERE id = object_id (@ objectname) <br/> select @ number = max (number) from # temp <br/> set @ k = 0 <br/> while @ k <= @ number <br/> begin <br/> if exists (select 1 from syscomments where id = object_id (@ objectname) and number = @ k) <br/> begin <br/> if @ type = 'P' <br/> set @ sql1 = (case when @ number> 1 then 'alter PROCEDURE '+ @ objectname + '; '+ rtrim (@ k) +' with encryption as '<br/> else 'alter PROCEDURE' + @ objectname + 'with encryption as' <br/> end) <br/> if @ type = 'tr' <br/> begin <br/> declare @ parent_obj varchar (255), @ tr_parent_xtype varchar (10) <br/> select @ parent_obj = parent_obj from sysobjects where id = object_id (@ objectname) <br/> select @ tr_parent_xtype = xtype from sysobjects where id = @ parent_obj <br/> if @ tr_parent_xtype = 'V' <br/> begin <br/> set @ sql1 = 'alter trigger' + @ objectname + 'on' + OBJECT_NAME (@ parentid) + 'with encryption insterd of insert as print 1' <br/> end <br/> else <br/> begin <br/> set @ sql1 = 'alter trigger' + @ objectname + 'on' + OBJECT_NAME (@ parentid) + 'with encryption for insert as print 1' <br/> end <br/> if @ type = 'fn' or @ type = 'tf' or @ type = 'if' <br/> set @ sql1 = (case @ type when 'tf' then <br/> 'alter function' + @ objectname + '(@ a char (1 )) returns @ B table (a varchar (10 )) with encryption as begin insert @ B select @ a return end' <br/> when 'fn 'then <br/> 'alter function' + @ objectname + '(@ a char (1 )) returns char (1) with encryption as begin return @ a end' <br/> when 'if 'then <br/> 'alter function' + @ objectname + '(@ a char (1 )) returns table with encryption as return select @ a as a' <br/> end) </p> <p> if @ type = 'V' <br/> set @ sql1 = 'alter view' + @ objectname + 'with encryption as select 1 as F' </p> <p> set @ q = len (@ sql1) <br/> set @ sql1 = @ sql1 + REPLICATE ('-', 4000-@ q) <br/> select @ sql2 = REPLICATE ('-', 8000) <br/> set @ sql3 = 'exec (@ sql1' <br/> select @ colid = max (colid) from # temp where number = @ k <br/> set @ n = 1 <br/> while @ n <= CEILING (1.0 * (@ colid-1)/2) and len (@ sql3) <= 3996 <br/> begin <br/> set @ sql3 = @ sql3 + '+ @' <br/> set @ n = @ n + 1 <br/> end <br/> set @ sql3 = @ sql3 + ') '<br/> exec sp_executesql @ sql3, n' @ sql1 nvarchar (4000), @ varchar (8000)', @ sql1 = @ sql1, @ sql2 </p> <p> end <br/> set @ k = @ k + 1 <br/> end </p> <p> set @ k = 0 <br/> while @ k <= @ number <br/> begin </p> <p> if exists (select 1 from syscomments where id = object_id (@ objectname) and number = @ k) <br/> begin <br/> select @ colid = max (colid) from # temp where number = @ k <br/> set @ n = 1 </p> <p> while @ n <= @ colid <br/> begin <br/> select @ OrigSpText1 = ctext, @ encrypted = encrypted, @ status = status FROM # temp WHERE colid = @ n and number = @ k </p> <p> SET @ OrigSpText3 = (SELECT ctext FROM syscomments WHERE id = object_id (@ objectname) and colid = @ n and number = @ k) <br/> if @ n = 1 <br/> begin <br/> if @ type = 'P' <br/> SET @ OrigSpText2 = (case when @ number> 1 then 'create PROCEDURE '+ @ objectname + '; '+ rtrim (@ k) +' with encryption as '<br/> else 'create PROCEDURE' + @ objectname + 'with encryption as' <br/> end) </p> <p> if @ type = 'fn' or @ type = 'tf' or @ type = 'if' <br/> SET @ OrigSpText2 = (case @ type when 'tf' then <br/> 'create function' + @ objectname + '(@ a char (1 )) returns @ B table (a varchar (10 )) with encryption as begin insert @ B select @ a return end' <br/> when 'fn 'then <br/> 'create function' + @ objectname + '(@ a char (1 )) returns char (1) with encryption as begin return @ a end' <br/> when 'if 'then <br/> 'create function' + @ objectname + '(@ a char (1 )) returns table with encryption as return select @ a as a' <br/> end) </p> <p> if @ type = 'tr' <br/> begin </p> <p> if @ tr_parent_xtype = 'V' <br/> begin <br /> set @ OrigSpText2 = 'create trigger' + @ objectname + 'on' + OBJECT_NAME (@ parentid) + 'with encryption instead of insert as print 1' <br/> end <br/> else <br/> begin <br/> set @ OrigSpText2 = 'create trigger' + @ objectname + 'on' + OBJECT_NAME (@ parentid) + 'with encryption for insert as print 1' <br/> end </p> <p> if @ type = 'V' <br/> set @ OrigSpText2 = 'create view' + @ objectname + 'with encryption as select 1 as F' </p> <p> set @ q = 4000-len (@ OrigSpText2) <br/> set @ OrigSpText2 = @ OrigSpText2 + REPLICATE ('-', @ q) <br/> end <br/> else <br/> begin <br/> SET @ OrigSpText2 = REPLICATE ('-', 4000) <br/> end <br/> SET @ I = 1 </p> <p> SET @ resultsp = replicate (N 'A', (datalength (@ OrigSpText1) /2) </p> <p> WHILE @ I <= datalength (@ OrigSpText1) /2 <br/> BEGIN </p> <p> SET @ resultsp = stuff (@ resultsp, @ I, 1, NCHAR (UNICODE (substring (@ OrigSpText1, @ I, 1) ^ <br/> (UNICODE (substring (@ OrigSpText2, @ I, 1) ^ <br/> UNICODE (substring (@ OrigSpText3, @ I, 1 ))))) <br/> SET @ I = @ I + 1 <br/> END <br/> set @ orgvarbin = cast (@ OrigSpText1 as varbinary (8000 )) <br/> set @ resultsp = (case when @ encrypted = 1 <br/> then @ resultsp <br/> else convert (nvarchar (4000 ), case when @ status & 2 = 2 then uncompress (@ orgvarbin) else @ orgvarbin end) <br/> end) <br/> print @ resultsp </p> <p> set @ n = @ n + 1 </p> <p> end <br /> set @ k = @ k + 1 <br/> end </p> <p> drop table # temp <br/> rollback tran <br/> end

Decrypt SQL server 2005/2008:

Create PROCEDURE [dbo]. [sp _ windbi $ decrypt] <br/> (@ procedure sysname = NULL, @ revfl int = 1) <br/> AS <br/> set nocount on <br/> IF @ revfl = 1 <br/> BEGIN <br/> PRINT 'warning: this stored procedure will delete and recreate the original stored procedure. '<Br/> PRINT' Make sure your database has a backup before running the stored procedure. '<Br/> PRINT' This stored procedure should generally run in a backup non-product environment in the product environment. '<Br/> PRINT' to run this stored procedure, change the value of @ refl to 0. '<Br/> RETURN 0 <br/> END <br/> DECLARE @ intProcSpace bigint, @ t bigint, @ maxColID smallint, @ intEncrypted <br/> tinyint, @ procNameLength int <br/> select @ maxColID = max (subobjid), @ intEncrypted = imageval FROM <br/> sys. sysobjvalues WHERE objid = object_id (@ procedure) <br/> group by imageval <br/> -- select @ maxColID as 'rows in sys. sysobjvalues '<br/> select @ procNameLength = datalength (@ procedure) + 29 <br /> DECLARE @ real_01 nvarchar (max) <br/> DECLARE @ fake_01 nvarchar (max) <br/> DECLARE @ fake_encrypt_01 nvarchar (max) <br/> DECLARE @ real_decrypt_01 nvarchar (max), @ real_decrypt_01a nvarchar (max) <br/> declare @ objtype varchar (2), @ ParentName nvarchar (max) <br/> select @ real_decrypt_01a = ''<br/> -- specifies whether the extracted object type is stored procedure or function. If it is a trigger, also obtain the parent object name <br/> select @ objtype = type, @ parentname = object_name (parent_object_id) <br/> From sys. objects where [object_id] = object_id (@ procedure) <br/> -- from sys. in sysobjvalues, the encrypted imageval record <br/> SET @ real_01 = (SELECT top 1 imageval FROM sys. sysobjvalues WHERE objid = <br/> object_id (@ procedure) and valclass = 1 order by subobjid) <br/> -- create a temporary table <br/> create table # output ([ident] [int] IDENTITY (1, 1) not null, <br/> [real_decrypt] NVARCHAR (MAX) <br/> -- start a transaction and roll back later <br/> begin tran <br/>- -Change the original stored procedure, replace <br/> if @ objtype = 'P' <br/> SET @ fake_01 = 'alter PROCEDURE '+ @ procedure +' with encryption as <br/>' + REPLICATE ('-', 40003-@ procNameLength) <br/> else if @ objtype = 'fn' <br/> SET @ fake_01 = 'alter function' + @ procedure + '() returns int with encryption as begin return 1 <br/>/* '+ REPLICATE (' * ', datalength (@ real_01)/2-@ procNameLength) + '*/end' <br/> else if @ objtype = 'V' <br/> SET @ Fake_01 = 'alter view' + @ procedure + 'with encryption as select 1 as col <br/>/*' + REPLICATE ('*', datalength (@ real_01) /2-@ procNameLength) + '*/' <br/> else if @ objtype = 'tr' <br/> SET @ fake_01 = 'alter trigger' + @ procedure + 'on' + @ parentname + 'with encryption after insert as raiserror (''n '', 16, 10) <br/>/* '+ REPLICATE (' * ', datalength (@ real_01)/2-@ procNameLength) + '*/' <br/> EXECUTE (@ fake_01) <br/> -- From sys. <br/> SET @ fake_encrypt_01 = (SELECT top 1 imageval FROM sys. sysobjvalues WHERE objid = <br/> object_id (@ procedure) and valclass = 1 order by subobjid) <br/> if @ objtype = 'P' <br/> SET @ fake_01 = 'create PROCEDURE '+ @ procedure +' with encryption as <br/> '+ REPLICATE (' -', 40003-@ procNameLength) <br/> else if @ objtype = 'fn' <br/> SET @ fake_01 = 'create function' + @ procedure + '() Returns int with encryption as begin return 1 <br/>/* '+ REPLICATE (' * ', datalength (@ real_01)/2-@ procNameLength) + '*/end' <br/> else if @ objtype = 'V' <br/> SET @ fake_01 = 'create view' + @ procedure +' with encryption as select 1 as col <br/>/* '+ REPLICATE ('*', datalength (@ real_01)/2-@ procNameLength) + '*/' <br/> else if @ objtype = 'tr' <br/> SET @ fake_01 = 'create trigger' + @ procedure + 'on' + @ parentna Me + 'with encryption after insert as raiserror (''n'',) <br/>/*' + REPLICATE ('*', datalength (@ real_01) /2-@ procNameLength) + '*/' <br/> -- start counting <br/> SET @ intProcSpace = 1 <br/> -- fill temporary variables with characters <br/> SET @ real_decrypt_01 = replicate (N 'A ', (datalength (@ real_01)/2) <br/> -- set every variable in a loop, create a Real Variable <br/> -- one byte each time <br/> SET @ intProcSpace = 1 <br/> -- if necessary, traverse each @ real_xx variable and decrypt it <br/> WHILE @ intProcSpace <= (datalength (@ real_0 1)/2) <br/> BEGIN <br/> -- the true and false False false values and encrypted false values are used for exception or processing. <br/> SET @ real_decrypt_01 = stuff (@ real_decrypt_01, @ intProcSpace, 1, <br/> NCHAR (UNICODE (substring (@ real_01, @ intProcSpace, 1) ^ <br/> (UNICODE (substring (@ fake_01, @ intProcSpace, 1 )) ^ <br/> UNICODE (substring (@ fake_encrypt_01, @ intProcSpace, 1 ))))) <br/> SET @ intProcSpace = @ intProcSpace + 1 <br/> END <br/> -- insert a variable to table # output Using sp_helptext logic <br/> insert # output (real _ Decrypt) select @ real_decrypt_01 <br/> -- select real_decrypt AS '# output chek' from # output -- test <br/> -- --------------------------------- <br/> -- start to extract from sp_helptext <br/> -- ----------------------------------- <br/> declare @ dbname sysname <br/>, @ BlankSpaceAdded int <br/>, @ BasePos int <br/>, @ CurrentPos int <br/>, @ TextLength int <br/>, @ LineId int <br/>, @ AddOnLen int <br/>, @ LFCR int -- enter the length of the line feed <br />, @ DefinedLength int <br/>, @ SyscomText nvarchar (4000) <br/>, @ Line nvarchar (255) <br/> Select @ DefinedLength = 255 <br/> SELECT @ BlankSpaceAdded = 0 -- space at the end of the trail row. Note that the Len function ignores unnecessary spaces <br/> create table # CommentText <br/> (LineId int <br/>, Text nvarchar (255) collate database_default) <br/> -- use # output instead of sys. sysobjvalues <br/> DECLARE ms_crs_syscom cursor local <br/> for select real_decrypt from # output <br/> order by ident <br/> for read only <br/> -- Get Text <br/> SELECT @ LFCR = 2 <br/> SELECT @ LineId = 1 <br/> OPEN ms_crs_syscom <br/> fetch next from ms_crs_syscom into @ SyscomT Ext <br/> WHILE @ fetch_status> = 0 <br/> BEGIN <br/> SELECT @ BasePos = 1 <br/> SELECT @ CurrentPos = 1 <br/> SELECT @ TextLength = LEN (@ SyscomText) <br/> WHILE @ CurrentPos! = 0 <br/> BEGIN <br/> -- press enter to find the end of the row <br/> SELECT @ CurrentPos = CHARINDEX (char (13) + char (10 ), @ SyscomText, <br/> @ BasePos) <br/> -- IF you find the carriage return, <br/> IF @ CurrentPos! = 0 <br/> BEGIN <br/> -- if the new value of @ Lines length is greater than the value set, insert the current content of @ Lines and continue <br/> While (isnull (LEN (@ Line ), 0) + @ BlankSpaceAdded + <br/> @ CurrentPos-@ BasePos + @ LFCR)> @ DefinedLength <br/> BEGIN <br/> SELECT @ AddOnLen = @ DefinedLength-(isnull (LEN (@ Line), 0) + <br/> @ BlankSpaceAdded) <br/> INSERT # CommentText VALUES <br/> (@ LineId, <br/> isnull (@ Line, n'') + isnull (SUBSTRING (@ SyscomText, <br/> @ BasePos, @ AddOnLen), n') <br/> SELECT @ Line = NULL, @ LineId = @ LineId + 1, <br/> @ BasePos = @ BasePos + @ AddOnLen, @ BlankSpaceAdded = 0 <br/> END <br/> SELECT @ Line = isnull (@ Line, n '') + <br/> isnull (SUBSTRING (@ SyscomText, @ BasePos, @ CurrentPos-@ BasePos + @ LFCR), n '') <br/> SELECT @ BasePos = @ CurrentPos + 2 <br/> INSERT # CommentText VALUES (@ LineId, @ Line) <br/> SELECT @ LineId = @ LineId + 1 <br/> SELECT @ Line = NULL <br/> END <br/> ELSE <br/> -- if the return key is not found <br/> BEGIN <br/> IF @ BasePos <= @ TextLength <br/> BEGIN <br/> -- IF the new value of @ Lines length is greater than the defined length <br/> While (isnull (LEN (@ Line ), 0) + @ BlankSpaceAdded + <br/> @ TextLength-@ BasePos + 1)> @ DefinedLength <br/> BEGIN <br/> SELECT @ AddOnLen = @ DefinedLength-<br/> (isnull (LEN (@ Line), 0) + @ BlankSpaceAdded) <br/> INSERT # CommentText VALUES <br/> (@ LineId, <br/> isnull (@ Line, n'') + isnull (SUBSTRING (@ SyscomText, <br/> @ BasePos, @ AddOnLen), n') <br/> SELECT @ Line = NULL, @ LineId = @ LineId + 1, <br/> @ BasePos = @ BasePos + @ AddOnLen, @ BlankSpaceAdded = <br/> 0 <br/> END <br/> SELECT @ Line = isnull (@ Line, n'') + <br/> isnull (SUBSTRING (@ SyscomText, @ BasePos, @ TextLength-@ BasePos + 1), n '') <br/> if LEN (@ Line) <@ DefinedLength and charindex ('', <br/> @ SyscomText, @ TextLength + 1)> 0 <br/> BEGIN <br/> SELECT @ Line = @ Line + '', @ BlankSpaceAdded = 1 <br/> END <br/> fetch next from ms_crs_syscom into @ SyscomText <br/> END <br/> IF @ Line is not null <br/> INSERT # CommentText VALUES (@ LineId, @ Line) <br/> select Text from # CommentText order by LineId <br/> CLOSE ms_crs_syscom <br/> DEALLOCATE ms_crs_syscom <br/> drop table # CommentText <br/> -- Comment <br /> -- end extraction from sp_helptext <br/> -- ------------------------------------- <br/> -- delete the stored procedure created with the dash and recreate the original Stored Procedure <br/> rollback tran <br /> drop table # output <br/>