MD5算法的T-SQL实现(FORSQL2000)(二)


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'

Published At
Categories with 数据库类
comments powered by Disqus