SQL2005自带扩展函数对字符串进行MD5加密

 

-- 获取12345的MD5加密串
select  hashbytes( ' MD5 ' ,  ' 12345 ' ) ;
-- 获取12345的SHA1加密串
select  hashbytes( ' SHA1 ' ,  ' 12345 ' ) ;

有了这个函数就可以很容易的对字符串进行加密，但是 hashbytes() 函数的返回结果是 varbinary 型，也就是以 0x 开头 16 进制形式的二进制数据。通常情况下，我们需要的都是字符串型的数据，很多人首先想到的可能就是用 CAST 或 Convert 函数将varbinary 转换为 varchar，但这样转换后的结果会是乱码，正确转换 varbinary 可变长度二进制型数据到 16 进制字符串应该使用系统内置函数 sys.fn_VarBinToHexStr()，如下所示：

select  sys.fn_VarBinToHexStr(hashbytes( ' MD5 ' ,  ' 12345 ' ))

sys.fn_varBinToHexStr() 函数只在 SQL Server 2005 中有效，在 SQL Server 2000 中实现相同功能使用系统扩展存储过程： master..xp_varBinToHexStr 。

为了便于使用，我将加密的过程编写成了一个函数。代码如下：
--  =============================================
--  Description:  实现MD5加密算法，返回对字符串的加密结果串
--  =============================================
ALTER   FUNCTION   [ dbo ] . [ MD5 ]  
(
     --  源字符串
     @src   varchar ( 255 ),
     --  加密类型(16/32)
     @type   int   =   32
)
RETURNS   varchar ( 255 )
WITH   EXECUTE   AS  CALLER
AS
BEGIN
     --  存放md5加密串(ox)
     DECLARE   @smd5   varchar ( 34 )

     --  加密字符串
     SELECT   @smd5   =  sys.fn_VarBinToHexStr(hashbytes( ' MD5 ' ,  @src ));

     IF   @type = 16
         SELECT   @smd5   =   SUBSTRING ( @smd5 , 11 , 16 )    -- 16位
     ELSE
         SELECT   @smd5   =   SUBSTRING ( @smd5 , 3 , 32 )     -- 32位

     --  返回加密串
     RETURN   @smd5

END
最后，采用触发器的方法，实现对用户密码的自动加密。代码如下：

--  =============================================
--  Author:        peterlu
--  Create date: 2008-6-11
--  Description:  实现对用户密码进行MD5加密
--  =============================================
CREATE   TRIGGER  trg_EncryptPwd
ON   Users
AFTER  INSERT , UPDATE
AS  
BEGIN
     IF ( UPDATE (Password))
     BEGIN
         DECLARE   @uid   int
         DECLARE   @pwd   varchar ( 32 )
         --  获取用户ID和密码
         SELECT   @uid = UserID, @pwd = Password  FROM  inserted
         --  更新密码
         UPDATE  Users  SET  Password  =  dbo.MD5( @pwd , 16 )  WHERE  UserID  =   @uid
     END
END
GO

 

-- =============================================
-- Author:       WJ
-- Create date: 2011-11-05
-- Description:  游标实现对用户密码进行MD5加密
-- =============================================
DECLARE @ID float
DECLARE @PWD  float
DECLARE @strSource varchar(20)--数据源
DECLARE @type int  --md5类型
DECLARE @smd5 varchar(34)
DECLARE Md5_Cursor CURSOR FOR
SELECT  ID,PWD FROM dbo.Card
OPEN Md5_Cursor
FETCH NEXT  FROM Md5_Cursor INTO @ID,@PWD
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @strSource = CAST(CAST(@PWD AS DECIMAL(10,0)) AS VARCHAR(10))
 SET @smd5 = SUBSTRING(sys.fn_VarBinToHexStr(hashbytes('MD5', @strSource)),11,16)
 PRINT '--' + @strSource + '--' + @smd5
 UPDATE dbo.Card SET PWDMD5NEW = @smd5 WHERE ID = @ID
FETCH NEXT FROM Md5_Cursor INTO @ID,@PWD
END
CLOSE Md5_Cursor
DEALLOCATE Md5_Cursor