--
参考:
下面给出了一个存储过程,它的作用是自动将当前数据库的用户存储过程加密。
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 上通过。
____________________________________________________________________
下面给出了一个存储过程,它的作用是自动将当前数据库的用户存储过程加密。
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
__________________________________
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
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
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
**********************************************************************************
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
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