WEB开发网
开发学院数据库MSSQL Server MD5算法的T-SQL实现(FORSQL2000)(二) 阅读

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

 2007-11-11 04:52:18 来源: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'

Tags:MD 算法 SQL

编辑录入:coldstar [复制链接] [打 印]
赞助商链接