March 31, 2005 at 9:36 am
I found this stored proc to decrypt other sprocs that are encrypted. Quick question, does this replace the encrypted proc with an unencrypted one, or leave it as is? I'm not entirely sure how this works. Thanks.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE DECRYPT2K (@objName varchar(50), @type char(1) )
--INPUT: object name (stored procedure,
-- view or trigger), object type ('S'-store
-- d procedure, 'V'view or 'T'-trigger)
--Original idea: shoeboy <shoeboy@ade
-- quacy.org>
--Copyright © 1999-2002 SecurityFocus
--adapted by Joseph Gama
--Planet Source Code, my employer and my
-- self are not responsible for the use of
-- this code
--This code is provided as is and for ed
-- ucational purposes only
--Please test it and share your results
AS
DECLARE @a nvarchar(4000), @b-2 nvarchar(4000), @C nvarchar(4000), @d nvarchar(4000), @i int, @t bigint, @tablename varchar(255), @trigtype varchar(6)
SET @type=UPPER(@type)
IF @type='T'
BEGIN
SET @tablename=(SELECT sysobjects_1.name
FROM dbo.sysobjects INNER JOIN
dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
SET @trigtype=(SELECT CASE WHEN dbo.sysobjects.deltrig > 0 THEN 'DELETE'
WHEN dbo.sysobjects.instrig > 0 THEN 'INSERT'
WHEN dbo.sysobjects.updtrig > 0 THEN 'UPDATE' END
FROM dbo.sysobjects INNER JOIN
dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
END
--get encrypted data
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b-2=case @type
WHEN 'S' THEN 'ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
WHEN 'V' THEN 'ALTER VIEW '+ @objName +' WITH ENCRYPTION AS SELECT dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', 4000-150)
WHEN 'T' THEN 'ALTER TRIGGER '+@objName+' ON '+ @tablename+' WITH ENCRYPTION FOR '+@trigtype+' AS PRINT ''a'''+REPLICATE('-', 4000-150)
END
EXECUTE (@b)
--get encrypted bogus SP
SET @C=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b-2=case @type
WHEN 'S' THEN 'CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
WHEN 'V' THEN 'CREATE VIEW '+ @objName +' WITH ENCRYPTION AS SELECT dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', 4000-150)
WHEN 'T' THEN 'CREATE TRIGGER '+@objName+' ON '+ @tablename+' WITH ENCRYPTION FOR '+@trigtype+' AS PRINT ''a'''+REPLICATE('-', 4000-150)
END
--start counter
SET @i=1
--fill temporary variable
SET @d = replicate(N'A', (datalength(@a) / 2))
--loop
WHILE @i<=datalength(@a)/2
BEGIN
--xor original+bogus+bogus encrypted
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END
--drop original SP
IF @type='S'
EXECUTE ('drop PROCEDURE '+ @objName)
ELSE
IF @type='V'
EXECUTE ('drop VIEW '+ @objName)
ELSE
IF @type='T'
EXECUTE ('drop TRIGGER '+ @objName)
--remove encryption
--try to preserve case
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
--replace SP
execute( @d)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 31, 2005 at 1:35 pm
I don't have the answer, but I do have a suggestion. Don't use encrypted stored procedures. They just create unnecessary headaches and don't really buy you added security. The logic to de-encrypt them is well known (as you have discovered), and any two-bit hacker that can do a web search will be able to crack them.
From what i've been reading, Microsoft itself never uses encrypted procs either...
Suggest you find some other way to hide your sensitive code or logic...
- john
March 31, 2005 at 2:08 pm
I'd be careful with that proc. I have used it myself and it has a problem decrypting SP's with more than 1 or 2 comments in syscomments. The problem is when it breaks your proc is corrupted or dropped.
Try the freeware dSQLSRVD v.1
March 31, 2005 at 3:24 pm
This is well known. It decrypts encrypted stored procedures. The main difference between it and the tool cited is dOMNAR's tool requires you to be a member of the sysadmin fixed server role. This stored procedure allows you to decrypt the stored procedure if you have the CREATE PROCEDURE right within a given database. The corruption occurs if the stored procedure is > 4000 characters.
K. Brian Kelley
@kbriankelley
April 1, 2005 at 6:29 am
Thanks guys. I know about the dSQLSRVD tool, but I just started a new job, and don't yet have rights to install apps on my machine, that's why I looked elsewhere. I'll wait until I can get the tool installed.
Pat
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply