SQL 2005的DES加密算法

-- 参考:
下面给出了一个存储过程，它的作用是自动将当前数据库的用户存储过程加密。
DECLARE @sp_name nvarchar ( 400 )
DECLARE @sp_content nvarchar ( 2000 )
DECLARE @asbegin int
declare @now datetime
select @now = getdate ()
DECLARE sp_cursor CURSOR FOR
SELECT object_name (id)
FROM sysobjects
WHERE xtype = ' P '
AND type = ' P '
AND crdate < @now
AND OBJECTPROPERTY (id, ' IsMSShipped ' ) = 0
OPEN sp_cursor
FETCH NEXT FROM sp_cursor
INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM syscomments WHERE id = OBJECT_ID ( @sp_name )
SELECT @asbegin = PATINDEX ( ' %AS ' + char ( 13 ) + ' % ' , @sp_content )
SELECT @sp_content = SUBSTRING ( @sp_content , 1 , @asbegin - 1 )
+ ' WITH ENCRYPTION AS '
+ SUBSTRING ( @sp_content , @asbegin + 2 , LEN ( @sp_content ))
SELECT @sp_name = ' DROP PROCEDURE [ ' + @sp_name + ' ] '
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
该存储过程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存储过程的 SQL 定义语句，将 AS 修改为了 WITH ENCRYPTION AS ，从而达到了加密存储过程的目的。本存储过程在 SQL Server 2000 上通过。

____________________________________________________________________

加密存贮过程的源代码

create PROCEDURE sp_decrypt_sp ( @objectName varchar ( 50 ))
AS
begin
declare @objectname1 varchar ( 100 )
declare @sql1 nvarchar ( 4000 ), @sql2 nvarchar ( 4000 ), @sql3 nvarchar ( 4000 ), @sql4 nvarchar ( 4000 ), @sql5 nvarchar ( 4000 ), @sql6 nvarchar ( 4000 ), @sql7 nvarchar ( 4000 ), @sql8 nvarchar ( 4000 ), @sql9 nvarchar ( 4000 ), @sql10 nvarchar ( 4000 )
DECLARE @OrigSpText1 nvarchar ( 4000 ), @OrigSpText2 nvarchar ( 4000 ) , @OrigSpText3 nvarchar ( 4000 ), @resultsp nvarchar ( 4000 )
declare @i int , @t bigint
declare @m int , @n int , @q int
set @m = ( SELECT max (colid) FROM syscomments WHERE id = object_id ( @objectName ))
set @n = 1
-- get encrypted data
create table # temp (colid int ,ctext varbinary ( 8000 ))
insert # temp SELECT colid,ctext FROM syscomments WHERE id = object_id ( @objectName )
-- select * from #temp
set @sql1 = ' ALTER PROCEDURE ' + @objectName + ' WITH ENCRYPTION AS '
-- set @sql1='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
set @q = len ( @sql1 )
set @sql1 = @sql1 + REPLICATE ( ' - ' , 4000 - @q )
select @sql2 = REPLICATE ( ' - ' , 4000 ), @sql3 = REPLICATE ( ' - ' , 4000 ), @sql4 = REPLICATE ( ' - ' , 4000 ), @sql5 = REPLICATE ( ' - ' , 4000 ), @sql6 = REPLICATE ( ' - ' , 4000 ), @sql7 = REPLICATE ( ' - ' , 4000 ), @sql8 = REPLICATE ( ' - ' , 4000 ), @sql9 = REPLICATE ( ' - ' , 4000 ), @sql10 = REPLICATE ( ' - ' , 4000 )
exec ( @sql1 + @sql2 + @sql3 + @sql4 + @sql5 + @sql6 + @sql7 + @sql8 + @sql9 + @sql10 )
while @n <= @m
begin
SET @OrigSpText1 = ( SELECT ctext FROM # temp WHERE colid = @n )
set @objectname1 = @objectname + ' _t '
SET @OrigSpText3 = ( SELECT ctext FROM syscomments WHERE id = object_id ( @objectName ) and colid = @n )
if @n = 1
begin
SET @OrigSpText2 = ' CREATE PROCEDURE ' + @objectName + ' WITH ENCRYPTION AS ' --
set @q = 4000 - len ( @OrigSpText2 )
set @OrigSpText2 = @OrigSpText2 + REPLICATE ( ' - ' , @q )
end
else
begin
SET @OrigSpText2 = REPLICATE ( ' - ' , 4000 )
end
-- 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
print @resultsp
-- execute( @resultsp)
set @n = @n + 1
end
drop table # temp
end
__________________________________

存储过程解密(破解函数,过程,触发器,视图.仅限于SQLSERVER2000)  

 --*/  

 /*--调用示例:  

 --解密指定存储过程  
 exec sp_decrypt '存储过程名'  

 --*/  


 create PROCEDURE sp_decrypt(@objectName varchar(50))  
 AS  
 begin  
 begin tran  
 declare @objectname1 varchar(100),@orgvarbin varbinary(8000)  
 declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000)  
 DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)  
 declare @i int,@status int,@type varchar(10),@parentid int  
 declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int  
 select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)  

 create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)  
 insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)  
 select @number=max(number) from #temp  
 set @k=0  

 while @k <=@number  
 begin  
 if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)  
 begin  
 if @type='P'  
 set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '  
 else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '  
 end)  

 if @type='TR'  
 set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '  

 if @type='FN' or @type='TF' or @type='IF'  
 set @sql1=(case @type when 'TF' then  
 'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '  
 when 'FN' then  
 'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'  
 when 'IF' then  
 'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'  
 end)  

 if @type='V'  
 set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '  

 set @q=len(@sql1)  
 set @sql1=@sql1+REPLICATE('-',4000-@q)  
 select @sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)  
 exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)  
 end  
 set @k=@k+1  
 end  

 set @k=0  
 while @k <=@number  
 begin  

 if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)  
 begin  
 select @colid=max(colid) from #temp where number=@k  
 set @n=1  

 while @n <=@colid  
 begin  
 select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k  

 SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)  
 if @n=1  
 begin  
 if @type='P'  
 SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '  
 else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '  
 end)  


 if @type='FN' or @type='TF' or @type='IF'--刚才有错改一下  
 SET @OrigSpText2=(case @type when 'TF' then  
 'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '  
 when 'FN' then  
 'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'  
 when 'IF' then  
 'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'  
 end)  

 if @type='TR'  
 set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '  

 if @type='V'  
 set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '  

 set @q=4000-len(@OrigSpText2)  
 set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)  
 end  
 else  
 begin  
 SET @OrigSpText2=REPLICATE('-', 4000)  
 end  
 --start counter  
 SET @i=1  
 --fill temporary variable  

 SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))  

 --loop  
 WHILE @i <=datalength(@OrigSpText1)/2  
 BEGIN  

 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  
 set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))  
 set @resultsp=(case when @encrypted=1  
 then @resultsp  
 else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)  
 end)  
 print @resultsp  
 --execute( @resultsp)  
 set @n=@n+1  

 end  

 end  
 set @k=@k+1  
 end  

 drop table #temp  
 rollback tran  
 end  
 GO  
   

_____________________________________________________________

 

简单的字符串的加密函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER function [ dbo ] . [ f_jmstr ] ( @str varchar ( 8000 ), @pwdstr varchar ( 8000 ))
returns varchar ( 8000 )
As
begin
    declare @re varchar ( 8000 )
    declare @i int
   
    select @i = len ( @str ), @re = ''
    while len ( @pwdstr ) < @i
        set @pwdstr = @pwdstr + ' 123 ' + @pwdstr
   
    while @i > 0
        select @re = nchar ( unicode ( substring ( @str , @i , 1 )) ^ unicode ( substring ( @pwdstr , @i , 1 ))) + @re , @i = @i - 1

    return ( @re )
end

 

******************************************************************************

ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[fn_PwdCrypt]') and xtype in (N'FN', N'IF', N'TF'))

dropfunction[dbo].[fn_PwdCrypt]

GO



CREATEFUNCTION dbo.fn_PwdCrypt(@ptSourcevarchar(255), @ptPasswordvarchar(255))



RETURNSvarbinary(64)

BEGIN



            DECLARE@tdest                                 varchar(255),

                                    @lteller                         int,

                                    @lPasswTeller   int,

                                    @cnt                            int,

                                    @len                             int,

                                    @tFinal                         varbinary(64)



            SELECT@tFinal=Convert(varbinary(64),'')

            SELECT@len=Len(@ptSource)

            SELECT@lteller=1

            SELECT@tdest=@ptSource

            SELECT@lPasswTeller=0



            WHILE@lteller<=@len

            BEGIN

            SELECT@lPasswTeller=@lPasswTeller-1

            If@lPasswTeller<1

                                    SELECT@lPasswTeller=Len(@ptPassword)



                        SELECT@tfinal=@tfinal+Convert(varbinary(64),Stuff(Substring(@tdest, @lteller, 1),1,Len( Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ) )

                        SELECT@lteller=@lteller+1

            END

            RETURN@tfinal







END



GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO



ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[fn_PwdDeCrypt]') and xtype in (N'FN', N'IF', N'TF'))

dropfunction[dbo].[fn_PwdDeCrypt]

GO



CREATEFUNCTION dbo.fn_PwdDeCrypt(@ptSourcevarchar(255), @ptPasswordvarchar(255))



RETURNSvarchar(255)

BEGIN



            DECLARE@tdest                                 varchar(255),

                                    @lteller                         int,

                                    @lPasswTeller   int,

                                    @cnt                            int,

                                    @len                            int,

                                    @tFinal                         varchar(255)



            SELECT@tFinal=''

            SELECT@len=Len(@ptSource)

            SELECT@lteller=1

            SELECT@tdest=@ptSource

            SELECT@lPasswTeller=0



            WHILE@lteller<=@len

            BEGIN

            SELECT@lPasswTeller=@lPasswTeller-1

            If@lPasswTeller<1

                                    SELECT@lPasswTeller=Len(@ptPassword)



                        SELECT@tfinal=@tfinal+Left(Stuff(substring(@tdest, @lteller, 1),1,Len( Char(Ascii(substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ), 1)

                        SELECT@lteller=@lteller+1

            END

            RETURN@tfinal



END



GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO



--Test it here

createtable #temp

            ( pwd varbinary(64))



insertinto #temp (pwd)

select dbo.fn_PwdCrypt ('MyPassword','secret')



select pwd from #temp

select dbo.fn_PwdDeCrypt (pwd,'secret') from #temp

droptable #temp

 

******************************************************************************************

 

2005中SQL本身就带了DES加密算法。
CREATE SYMMETRIC KEY key1 WITH ALGORITHM=DES
    ENCRYPTION BY PASSWORD='abc$123'
GO

OPEN SYMMETRIC KEY key1   
    DECRYPTION BY PASSWORD='abc$123'

DECLARE@strNVARCHAR(100),@EncryptedVARBINARY(MAX)
SET@str=N'要加密的字串'   
SELECT@Encrypted=ENCRYPTBYKEY(KEY_GUID('key1'),@str)
SELEcT'加密'=@Encrypted ,'解密'=CAST(DECRYPTBYKEY(@Encrypted) ASNVARCHAR(100))
CLOSE SYMMETRIC KEY key1
GO
DROP SYMMETRIC KEY key1
GO

**********************************************************************************

 

写个扩展存储过程
RETCODE __declspec(dllexport) xp_EncryptData(SRV_PROC *srvproc)
RETCODE __declspec(dllexport) xp_DecryptData(SRV_PROC *srvproc)

use master
GO
exec sp_addextendedproc N'xp_EncryptData', N'DESProc.dll'
GO

exec sp_addextendedproc N'xp_DecryptData', N'DESProc.dll'
GO

转载于:https://www.cnblogs.com/wbzhao/archive/2012/04/24/2468363.html