MD5算法的T-SQL实现(FOR SQL2000)（二）
2007-11-11 08:00:00 来源：WEB开发网核心提示：IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_ConvertToWordArray]') AND xtype IN(N'FN', N'IF', N'TF')
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_ConvertToWordArray]') AND xtype IN(N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[MD5_ConvertToWordArray]
GO
/*****************************************************************************
* Name: MD5_ConvertToWordArray
* Description: MD5_ConvertToWordArray
*****************************************************************************/
CREATE FUNCTION dbo.MD5_ConvertToWordArray(
@sOrigMess VARCHAR(8000) = ''
)
RETURNS @tWordArray TABLE([ID] INT IDENTITY(0,1),[Word] INT)
WITH ENCRYPTION
AS
BEGIN
IF @sOrigMess IS NULL
SET @sOrigMess = ''
DECLARE @iLenOfMess INT
DECLARE @iWordArrayLen INT
DECLARE @iPosOfWord INT
DECLARE @iPosOfMess INT
DECLARE @iCountOfWord INT
SET @iLenOfMess = LEN(@sOrigMess)
SET @iWordArrayLen = ((@iLenOfMess + 8)/64 + 1) * 16
SET @iCountOfWord = 0
WHILE(@iCountOfWord<@iWordArrayLen)
BEGIN
INSERT INTO @tWordArray([Word]) VALUES(0)
SET @iCountOfWord = @iCountOfWord + 1
END
SELECT @iPosOfMess = 0, @iPosOfWord = 0, @iCountOfWord = 0
WHILE(@iPosOfMess < @iLenOfMess)
BEGIN
SELECT @iCountOfWord = @iPosOfMess / 4, @iPosOfWord = @iPosOfMess % 4
UPDATE @tWordArray
SET [Word] = [Word] | dbo.MD5_LShift(UNICODE(SUBSTRING(@sOrigMess,@iPosOfMess+1,1)),@iPosOfWord*8)
WHERE [ID] = @iCountOfWord
SET @iPosOfMess = @iPosOfMess + 1
END
SELECT @iCountOfWord = @iPosOfMess / 4, @iPosOfWord = @iPosOfMess % 4
UPDATE @tWordArray
SET [Word] = [Word] | dbo.MD5_LShift(0x80,@iPosOfWord*8)
WHERE [ID] = @iCountOfWord
UPDATE @tWordArray
SET [Word] = [Word] | dbo.MD5_LShift(@iLenOfMess,3)
WHERE [ID] = @iWordArrayLen - 2
UPDATE @tWordArray
SET [Word] = [Word] | dbo.MD5_RShift(@iLenOfMess,29)
WHERE [ID] = @iWordArrayLen - 1
RETURN
END
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_WordToHex]') AND xtype IN(N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[MD5_WordToHex]
GO
/*****************************************************************************
* Name: MD5_WordToHex
* Description: MD5_WordToHex
*****************************************************************************/
CREATE FUNCTION dbo.MD5_WordToHex(
@iValue INT
)
RETURNS CHAR(8)
WITH ENCRYPTION
AS
BEGIN
DECLARE @sRes VARCHAR(8)
DECLARE @iTmp INT
DECLARE @iCount TINYINT
SELECT @sRes = '', @iCount = 0
WHILE(@iCount<4)
BEGIN
SET @iTmp = dbo.MD5_RShift(@iValue,@iCount*8) & 0x000000FF
SET @sRes = @sRes + CASE @iTmp / 16 WHEN 0 THEN '0'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
WHEN 8 THEN '8'
WHEN 9 THEN '9'
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE '' END
+ CASE @iTmp % 16 WHEN 0 THEN '0'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
WHEN 8 THEN '8'
WHEN 9 THEN '9'
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE '' END
SET @iCount = @iCount + 1
END
RETURN(@sRes)
END
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5]') AND xtype IN(N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[MD5]
GO
/*****************************************************************************
* Name: MD5
* Description: MD5
*****************************************************************************/
CREATE FUNCTION dbo.MD5(
@sOrigMess NVARCHAR(4000)
)
RETURNS CHAR(32)
WITH ENCRYPTION
AS
BEGIN
--====================================
DECLARE @S11 TINYINT
DECLARE @S12 TINYINT
DECLARE @S13 TINYINT
DECLARE @S14 TINYINT
DECLARE @S21 TINYINT
DECLARE @S22 TINYINT
DECLARE @S23 TINYINT
DECLARE @S24 TINYINT
DECLARE @S31 TINYINT
DECLARE @S32 TINYINT
DECLARE @S33 TINYINT
DECLARE @S34 TINYINT
DECLARE @S41 TINYINT
DECLARE @S42 TINYINT
DECLARE @S43 TINYINT
DECLARE @S44 TINYINT
SELECT @S11 = 7, @S12 = 12, @S13 = 17, @S14 = 22
SELECT @S21 = 5, @S22 = 9, @S23 = 14, @S24 = 20
SELECT @S31 = 4, @S32 = 11, @S33 = 16, @S34 = 23
SELECT @S41 = 6, @S42 = 10, @S43 = 15, @S44 = 21
--====================================
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
DECLARE @d INT
DECLARE @AA INT
DECLARE @BB INT
DECLARE @CC INT
DECLARE @DD INT
SELECT @a = 0x67452301
,@b = 0xEFCDAB89
,@c = 0x98BADCFE
,@d = 0x10325476
--====================================
DECLARE @sRes VARCHAR(32)
SET @sRes = ''
DECLARE @iWordArrayLen INT
DECLARE @iWordArrayCount INT
DECLARE @tTmp TABLE([ID] INT, [Word] INT)
INSERT INTO @tTmp SELECT * FROM dbo.MD5_ConvertToWordArray(@sOrigMess)
SELECT @iWordArrayCount=0, @iWordArrayLen = COUNT(*) FROM @tTmp
WHILE(@iWordArrayCount < @iWordArrayLen)
BEGIN
SELECT @AA = @a, @BB = @b, @CC = @c, @DD = @d
SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S11, 0xD76AA478)
SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S12, 0xE8C7B756)
SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S13, 0x242070DB)
SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S14, 0xC1BDCEEE)
SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S11, 0xF57C0FAF)
SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S12, 0x4787C62A)
SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S13, 0xA8304613)
SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S14, 0xFD469501)
SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S11, 0x698098D8)
SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S12, 0x8B44F7AF)
SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S13, 0xFFFF5BB1)
SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S14, 0x895CD7BE)
SELECT @a = dbo.MD5_FF(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S11, 0x6B901122)
SELECT @d = dbo.MD5_FF(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S12, 0xFD987193)
SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S13, 0xA679438E)
SELECT @b = dbo.MD5_FF(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S14, 0x49B40821)
SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S21, 0xF61E2562)
SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S22, 0xC040B340)
SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S23, 0x265E5A51)
SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S24, 0xE9B6C7AA)
SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S21, 0xD62F105D)
SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S22, 0x2441453)
SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S23, 0xD8A1E681)
SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S24, 0xE7D3FBC8)
SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S21, 0x21E1CDE6)
SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S22, 0xC33707D6)
SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S23, 0xF4D50D87)
SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S24, 0x455A14ED)
SELECT @a = dbo.MD5_GG(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S21, 0xA9E3E905)
SELECT @d = dbo.MD5_GG(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S22, 0xFCEFA3F8)
SELECT @c = dbo.MD5_GG(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S23, 0x676F02D9)
SELECT @b = dbo.MD5_GG(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S24, 0x8D2A4C8A)
SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S31, 0xFFFA3942)
SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S32, 0x8771F681)
SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S33, 0x6D9D6122)
SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S34, 0xFDE5380C)
SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S31, 0xA4BEEA44)
SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S32, 0x4BDECFA9)
SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S33, 0xF6BB4B60)
SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S34, 0xBEBFBC70)
SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S31, 0x289B7EC6)
SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S32, 0xEAA127FA)
SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S33, 0xD4EF3085)
SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S34, 0x4881D05)
SELECT @a = dbo.MD5_HH(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S31, 0xD9D4D039)
SELECT @d = dbo.MD5_HH(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S32, 0xE6DB99E5)
SELECT @c = dbo.MD5_HH(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S33, 0x1FA27CF8)
SELECT @b = dbo.MD5_HH(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S34, 0xC4AC5665)
SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 0), @S41, 0xF4292244)
SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 7), @S42, 0x432AFF97)
SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 14), @S43, 0xAB9423A7)
SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 5), @S44, 0xFC93A039)
SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 12), @S41, 0x655B59C3)
SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S42, 0x8F0CCC92)
SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 10), @S43, 0xFFEFF47D)
SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 1), @S44, 0x85845DD1)
SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 8), @S41, 0x6FA87E4F)
SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 15), @S42, 0xFE2CE6E0)
SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 6), @S43, 0xA3014314)
SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 13), @S44, 0x4E0811A1)
SELECT @a = dbo.MD5_II(@a, @b, @c, @d, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 4), @S41, 0xF7537E82)
SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 11), @S42, 0xBD3AF235)
SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 2), @S43, 0x2AD7D2BB)
SELECT @b = dbo.MD5_II(@b, @c, @d, @a, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 9), @S44, 0xEB86D391)
SET @a = dbo.MD5_AddUnsigned(@a, @AA)
SET @b = dbo.MD5_AddUnsigned(@b, @BB)
SET @c = dbo.MD5_AddUnsigned(@c, @CC)
SET @d = dbo.MD5_AddUnsigned(@d, @DD)
SET @iWordArrayCount = @iWordArrayCount + 16
END
SET @sRes = dbo.MD5_WordToHex(@a) + dbo.MD5_WordToHex(@b) + dbo.MD5_WordToHex(@c) + dbo.MD5_WordToHex(@d)
SET @sRes = LOWER(@sRes)
RETURN(@sRes)
END
GO
--Test
set nocount on
select dbo.MD5('') as 'MD5('''')'
union
select 'd41d8cd98f00b204e9800998ecf8427e'
select dbo.MD5('a') as 'MD5(''a'')'
union
select '0cc175b9c0f1b6a831c399e269772661'
select dbo.MD5('abc') as 'MD5(''abc'')'
union
select '900150983cd24fb0d6963f7d28e17f72'
select dbo.MD5('message digest') as 'MD5(''message digest'')'
union
select 'f96b697d7cb7938d525a2f31aaf161d0'
select dbo.MD5('abcdefghijklmnopqrstuvwxyz') as 'MD5(''abcdefghijklmnopqrstuvwxyz'')'
union
select 'c3fcd3d76192e4007dfb496cca67e13b'
select dbo.MD5('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') as 'MD5(''ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'')'
union
select 'd174ab98d277d9f5a5611c2c9f419d9f'
select dbo.MD5('12345678901234567890123456789012345678901234567890123456789012345678901234567890') as 'MD5(''12345678901234567890123456789012345678901234567890123456789012345678901234567890'')'
union
select '57edf4a22be3c955ac49da2e2107b67a'
select dbo.MD5('我') as 'MD5(''我'')'
union
select 'a31d0f25367ebe046897f8a939ca4a9f'
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
中查找“MD5算法的T-SQL实现(FOR SQL2000)（二）”更多相关内容
中查找“MD5算法的T-SQL实现(FOR SQL2000)（二）”更多相关内容更多精彩
赞助商链接
