萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> sql server教程 >> MD5算法的T-SQL實現(FORSQL2000)(二)

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 +
copyright © 萬盛學電腦網 all rights reserved