我们在书写SQL脚本时,可能会碰到对字符串进行md5加密的需要,比如存储在数据库中的用户密码需要进行MD5加密等。大部分数据库都直接提供了MD5加密函数,比如在MySQL和PostgreSQL中,都直接提供了名称就是md5的加密函数,着实方便。但在MSSQL中却并不简单!

md5是常用的数据加密法(五分钟玩通使用SQL实现MD5加密)(1)

MSSQL各版本对MD5加密的处理

使用SQLServer的朋友会发现,在MSSQL中,并没有名称类似MD5这样的函数,如何对字符串进行MD5加密还真不是太容易。特别是在早期版本的,比如MSSQL2000中,要进行MD5加密只能自己想办法了,还能有什么办法,就是自己写自定义函数进行处理喽。

如果是在MSSQL2000中进行MD5处理,只能自己写自定义函数处理,网上也有一些高手写好的脚本。我这里也整理过一个MD5处理的脚本,有需要的朋友可以@网云技术 找我,我可以私发给您。

可这毕竟不是事儿,不伦不类挺讨厌的。还好从2008开始,MSSQL提供了hashbytes函数,该函数可以对字符串进行MD2、MD4、MD5、SHA、SHA1、SHA2_256、SHA2_512七种算法的加密,可谓不鸣则已一鸣惊人。我们今天就研究下如何使用hashbytes进行md5加密。

md5是常用的数据加密法(五分钟玩通使用SQL实现MD5加密)(2)

功能强大的hashbytes函数

hashbytes的语法格式非常简单:

HASHBYTES ( '算法', expression)

第一个参数就是使用单引号包裹起来的算法名称,这个名字可以以下七种方式中的任何一种:

MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

第二个参数就是将要被加密的字符串表达式,可以是常量、变量、表达式或字段等。

返回值是一个最大8000长度的varbinary。没错,返回值是varbinary,并不是直接的字符串。下面我们先做个小实验,对空字符串和字符串'123456'进行MD5加密,看看返回的结果。

select hashbytes('md5',space(0)); select hashbytes('md5','123456');

下图是执行的结果:

md5是常用的数据加密法(五分钟玩通使用SQL实现MD5加密)(3)

查询结果看上去似乎就是加密后的字符串,但其类型是varbinary,这样的结果与我们期望的还是存在一定差距的。

结合sys.fn_sqlvarbasetostr转换为字符串

我们想要的结果,是MD5加密后的字符串,但hashbytes返回的是varbinary,这倒是没关系,有一个系统函数sys.fn_sqlvarbasetostr是专门用来将varbinary转为varchar的,当然您可以使用其它方式转换,既然MSSQL提供了直接转换的函数,我们就不要多此一举了。

sys.fn_sqlvarbasetostr的语法格式非常简单:

sys.fn_sqlvarbasetostr(varbinary)

参数就是varbinary类型的表达式了,返回值则就是varchar类型的字符串。

我们将上述的例子调用sys.fn_sqlvarbasetostr做一下改造:

select sys.fn_sqlvarbasetostr(hashbytes('md5',space(0))); select sys.fn_sqlvarbasetostr(hashbytes('md5','123456'));

运行效果参看下图:

md5是常用的数据加密法(五分钟玩通使用SQL实现MD5加密)(4)

由上图可以看出,varbinary转换为varchar类型,大写也变成了小写,与我们想要的结果更接近了一步,只是最前面两位0x并不是MD5加密后的结果部分,我们还需要使用截取函数将它干掉。

使用截取函数获得完整MD5加密结果

说道截取函数,刚好我昨日发文《您最应该掌握的七个SQL字符串处理函数》中,提到的函数中至少有两个都可以实现该功能,一个就是SUBSTRING、另一个则是stuff。

我们先来看看它们的语法格式。

substring

SUBSTRING(expression,start,length)

stuff

STUFF(expression,start,length,replace)

使用substring最直观,直接开干,使用stuff有点迂回,等于是把前两位挖掉填入空字符串。我们下面综合运用两种方式处理下:

select substring(sys.fn_sqlvarbasetostr(hashbytes('md5',space(0))),3,32); select stuff(sys.fn_sqlvarbasetostr(hashbytes('md5','123456')),1,2,space(0));

第一个使用了substring,从第3位截取32位的长度,MD5加密后的字符串就是32位的;第二个使用stuff,从第1位向后挖掉2位,填入空字符串。可谓是殊途同归。具体结果参看下图:

md5是常用的数据加密法(五分钟玩通使用SQL实现MD5加密)(5)

综合来说,我们如果要对一个字符串进行MD5加密,完整的语法可以是:

substring(sys.fn_sqlvarbasetostr(hashbytes('md5',待加密字符串)),3,32)

也可以是:

stuff(sys.fn_sqlvarbasetostr(hashbytes('md5',待加密字符串)),1,2,'')

希望对您有所帮助!

,