Database/MS SQL

암호화된 Procedure 보는 방법

catchv 2013. 4. 13. 14:37

버전 : SQL Server 2005, 2008, 2008 R2, 2012

프로시져를 암호화해서 만들어 놓았는데 원본 소스를 삭제한 경우 기존에 암호화 된 프로시져를 분석해야 한다.

분석할 암호화된 프로지셔를 만들어보자.

  1. -- 암호화된 Procedure   
  2. -- 2013.04.13  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7.     
  8. CREATE PROCEDURE sp_EncryptionProc   
  9.     @TEST VARCHAR(100)  
  10. WITH ENCRYPTION  
  11. AS  
  12. BEGIN  
  13.     -- SET NOCOUNT ON added to prevent extra result sets from  
  14.     -- interfering with SELECT statements.  
  15.     SET NOCOUNT ON;  
  16.     
  17.     SELECT  *   
  18.     FROM    sys.objects  
  19. END  
  20. GO  

 

분석에 사용될 Procedure를 만들자

/****** Object: StoredProcedure [dbo].[sp__procedure] Script Date: 2013-04-13 오후 1:36:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[sp__procedure]

(@procedure sysname = NULL)

AS

    SET NOCOUNT ON

 

    DECLARE @intProcSpace    bigint

        ,    @t                bigint

        ,    @maxColID        smallint

        ,    @intEncrypted    tinyint

        ,    @procNameLength int

    

    select    @maxColID = max(subobjid)

        --,    @intEncrypted = encrypted

    FROM

    sys.sysobjvalues WHERE objid = object_id(@procedure)

    --GROUP BY encrypted

 

    --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)

 

    select @real_decrypt_01a = ''

 

    -- extract the encrypted imageval rows from sys.sysobjvalues

    SET @real_01 = (SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) and valclass = 1 and subobjid = 1 )

 

    -- create this table for later use

    create table #output

    (

        [ident] [int] IDENTITY (1, 1) NOT NULL

    ,    [real_decrypt] NVARCHAR(MAX)

    )

 

    -- We'll begin the transaction and roll it back later

    BEGIN TRAN

 

    -- alter the original procedure, replacing with dashes

    SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS

    '+REPLICATE('-', 40003 - @procNameLength)

 

    EXECUTE (@fake_01)

 

    -- extract the encrypted fake imageval rows from sys.sysobjvalues

    SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) and valclass = 1 and subobjid = 1)

 

    SET @fake_01='CREATE PROCEDURE '+ @procedure +' WITH ENCRYPTION AS

    '+REPLICATE('-', 40003 - @procNameLength)

    --start counter

    SET @intProcSpace=1

    --fill temporary variable with with a filler character

    SET @real_decrypt_01 = replicate(N'A', (datalength(@real_01) /2 ))

 

    --loop through each of the variables sets of variables, building the real variable

    --one byte at a time.

    SET @intProcSpace=1

 

    -- Go through each @real_xx variable and decrypt it, as necessary

    WHILE @intProcSpace<=(datalength(@real_01)/2)

    BEGIN

    --xor real & fake & fake encrypted

    SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,

    NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^

    (UNICODE(substring(@fake_01, @intProcSpace, 1)) ^

    UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))

    SET @intProcSpace=@intProcSpace+1

    END

 

    -- Load the variables into #output for handling by sp_helptext logic

 

    insert #output (real_decrypt) select @real_decrypt_01

    -- select real_decrypt AS '#output chek' from #output — Testing

 

    -- ————————————-

    -- Beginning of extract from sp_helptext

    -- ————————————-

    declare @dbname sysname

    ,@BlankSpaceAdded int

    ,@BasePos int

    ,@CurrentPos int

    ,@TextLength int

    ,@LineId int

    ,@AddOnLen int

    ,@LFCR int -- lengths of line feed carriage return

    ,@DefinedLength int

    ,@SyscomText nvarchar(4000)

    ,@Line nvarchar(255)

 

    Select @DefinedLength = 255

    SELECT @BlankSpaceAdded = 0

    --Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces

    CREATE TABLE #CommentText

    (LineId int

    ,Text nvarchar(255) collate database_default)

 

    -- use #output instead of sys.sysobjvalues

    DECLARE ms_crs_syscom CURSOR LOCAL

    FOR SELECT real_decrypt from #output

    ORDER BY ident

    FOR READ ONLY

 

    -- Else get the text.

 

    SELECT @LFCR = 2

    SELECT @LineId = 1

 

    OPEN ms_crs_syscom

 

    FETCH NEXT FROM ms_crs_syscom into @SyscomText

 

    WHILE @@fetch_status >= 0

    BEGIN

 

    SELECT @BasePos = 1

    SELECT @CurrentPos = 1

    SELECT @TextLength = LEN(@SyscomText)

 

    WHILE @CurrentPos != 0

    BEGIN

    --Looking for end of line followed by carriage return

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

    @BasePos)

 

    --If carriage return found

    IF @CurrentPos != 0

    BEGIN

    --If new value for @Lines length will be > then the

    --set length then insert current contents of @line

    --and proceed.

 

    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

    --else carriage return not found

    BEGIN

    IF @BasePos <= @TextLength

    BEGIN

    --If new value for @Lines length will be > then the

    --defined length

    --

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

    @TextLength-@BasePos+1 ) > @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

 

    -- ————————————-

    -- End of extract from sp_helptext

    -- ————————————-

 

    -- Drop the procedure that was setup with dashes and rebuild it with the good stuff

    -- Version 1.1 mod; makes rebuilding hte proc unnecessary

    ROLLBACK TRAN

 

    DROP TABLE #output

 


sp__procedure를 사용하기 위해서는 관리자 전용 연결로 연결(DAC)을 해야 사용이 가능하다.
(관리자 전용 연결(DAC) : http://catchv.tistory.com/34)

 

EXEC sp__procedure 'sp_EncryptionProc'
실행하면
암호화된 Procedure를 볼 수 있다.