熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

MD5算法的T-SQL實現(FOR SQL2000)(二)

2013-11-15 14:38:40  來源: SQL Server 

  IF EXISTS(SELECT * FROM dbosysobjects WHERE id = OBJECT_ID(N[dbo][MD_ConvertToWordArray]) AND xtype IN(NFN NIF NTF))
    DROP FUNCTION [dbo][MD_ConvertToWordArray]
  GO
  /*****************************************************************************
  * Name: MD_ConvertToWordArray
  * Description: MD_ConvertToWordArray
  *****************************************************************************/
  CREATE FUNCTION dboMD_ConvertToWordArray(
     @sOrigMess    VARCHAR()  =
  )
  RETURNS @tWordArray TABLE([ID] INT IDENTITY()[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 + )/ + ) *
    SET @iCountOfWord =
    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:http://tw.wingwit.com/Article/program/SQLServer/201311/22109.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.