Sql Server MD5和SHA1 加密

 

 

 

注意：

hashbytes()返回varbinary类型值； 
sys.fn_VarBinToHexStr()是把varbinary转换成varchar；

 

在SQl2005下自带的函数hashbytes() ，此函数是微软在SQL SERVER 2005中提供的，可以用来计算一个字符串的 MD5 和 SHA1 值，使用方法如下：

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

有了这个函数可以在sqlserver中为字符串进行加密，但是hashbytes() 函数的返回结果是 varbinary类型，

（以 0x 开头 16 进制形式的二进制数据）通常情况下，我们需要的都是字符串型的数据，很多人首先想到的

可能就是用 CAST 或 Convert 函数将varbinary 转换为 varchar，但这样转换后的结果会是乱码，正确转换 varbinary 可变长度二进制型数据到 16 进制字符串应该使用系统内置函数 sys.fn_varbintohexstr()（只在sqlserver2005下有），如下所示：

select sys.fn_varbintohexstr(hashbytes('MD5', '123456'))

 然后就可以截取需要的部分

select lower(right(sys.fn_varbintohexstr(hashbytes('MD5','123456')),32))

为md5加密串。

 

 

具体使用方式如下：

 

-- =============================================

-- Description:  实现MD5加密算法，返回对字符串的加密结果串

-- =============================================

CREATE FUNCTION MD5

(

    -- 源字符串

    @src varchar(255),

    -- 加密类型(16/32)

    @type int = 32

)

RETURNS varchar(255)

WITH EXECUTE AS CALLER

AS

BEGIN

    -- 存放md5加密串(ox)

    DECLARE @smd5 varchar(34)

    -- 加密字符串此处用MD5加密，还有一种是SHA1加密

    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

 

 

加密时使用

建立一个users表以供测试用：

 

CREATE TABLE [dbo].[Users](

[UserID] [int] NULL,

[Password] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL

)

 

 

对users表添加一个触发器，当插入和修改用户密码时修改密码为加密后的结构，同时更新到users表中

 

-- =============================================

-- 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

 

 

如果我插入一条数据：

insert into users values(123,'12345')

则在数据库库中出现了加密后的结果，但是如果想测试查询一下，可以使用如下的方式：

select * from users where password=dbo.MD5('12345',16)

同样是用加密函数再次加密一下就可以与表中的数据对应