sql server 加密解密

if object_id('dbo.jiami','fn') is not null
drop function dbo.jiami
go
--加密函数
create function dbo.jiami(@a varchar(10),@b varchar(30))
returns varchar(50)
as
begin
   declare @i int,@str varchar(50),@str1 varchar(50)
   select @i=1,@str='',@str1=''
   while(@i<len(@b)+1)
    begin
      if(@i%3=0 or @i%5=0)
           select @str1=@str1+char(ascii(substring(@b,@i,1))+17)
      else
           select @str1=@str1+char(ascii(substring(@b,@i,1))+49)       
      set @i=@i+1
    end
   set @i=1
   while(@i<len(@a)+1)
    begin
      select @str=@str+char(ascii(substring(@a,@i,1))+16)
      if(@i in (1,2,6))
           set @str=@str+substring(@str1,@i,3)
      set @i=@i+1
    end
 return @str
end
/*密码解密函数*/
if object_id('dbo.jiemi','fn') is not null
drop function dbo.jiemi
go
create function dbo.jiemi(@a varchar(50))
returns varchar(50)
as
begin
   declare @i int,@str varchar(10)
   select @i=1,@str=''
   while(@i<len(@a)+1)
    begin
      select @str=@str+char(ascii(substring(@a,@i,1))-16)
      if(@i in (9,10,11))
         set @i=@i+1
      else
         set @i=@i+4
    end
 return @str
end


declare @res varchar(50),@a varchar(10),@b varchar(30)
set @b='231083198202293215'
set @a='351696'
select @res=dbo.jiami(@a,@b)
print @res
select @res=dbo.jiemi(rtrim(@res))
print @res
select ascii('0')-ascii('a')


此函数不行，若密码中含有q，则加密后为null


DECLARE @ClearPWD varchar(255) 
DECLARE @EncryptedPWD varbinary(255)
SELECT @ClearPWD = 'test'
SELECT @EncryptedPWD = CONVERT(varbinary(255), pwdencrypt(@ClearPWD))
SELECT pwdcompare(@ClearPWD, @EncryptedPWD, 0)
SELECT pwdcompare('ErrorPassword', @EncryptedPWD, 0)


用sql server 自带的加密算法，存储加密后的内容的字段应为varbinary类型，否则加密后为null，无法正确解密




CREATE function [dbo].[jiami](@miyue varchar(50),@mima varchar(50))
returns varchar(200)
as
BEGIN
DECLARE @pwd VARCHAR(200)
SET @pwd=sys.fn_varbintohexstr(EncryptByPassPhrase(@miyue,@mima))  
return @pwd
END




CREATE function [dbo].[jiemi](@miyue VARCHAR(50),@mima varchar(200))
returns varchar(50)
as
BEGIN
declare @PassWord varchar(50)
SET @PassWord=convert(varchar,DecryptByPassphrase(@miyue, dbo.HexStrToVarBin(@mima)))
RETURN @PassWord
END






--字符串转二进制
CREATE FUNCTION [dbo].[HexStrToVarBin](@hexstr VARCHAR(8000)) 


RETURNS varbinary(8000) 


AS 


 


BEGIN 


   DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(8000), @odd BIT, @start bit


   SET @count = LEN(@hexstr) 


   SET @start = 1


   SET @b = CAST('' AS varbinary(1)) 


   IF SUBSTRING(@hexstr, 1, 2) = '0x' 


       SET @i = 3 


   ELSE 


       SET @i = 1 


   SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)


   WHILE (@i <= @count) 


    BEGIN 


       IF @start = 1 AND @odd = 1


       BEGIN


           SET @hex = '0' + SUBSTRING(@hexstr, @i, 1)


       END


       ELSE


       BEGIN


           SET @hex = SUBSTRING(@hexstr, @i, 2)


       END


       SET @b = @b + 


               CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]' 


                   THEN CAST(SUBSTRING(@hex, 1, 1) AS INT) 


                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT) 


               END * 16 + 


               CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]' 


                   THEN CAST(SUBSTRING(@hex, 2, 1) AS INT) 


                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT) 


               END AS binary(1)) 


       SET @i = @i + (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))


       IF @start = 1


       BEGIN


           SET @start = 0


       END


    END 


    RETURN @b 


END