md5.sql

tsql md5函数

/***************************************************************************** 
* Name: T-SQL MD5算法实现 
* Author:   Rambo Qian 
* Create Date: 2003-04-10 
* Last Modified by: Rambo Qian 
* Last Update Date: 2003-04-16 
* Version: V1.0.00 
*****************************************************************************/ 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_m_OnBits]') AND xtype IN(N'FN', N'IF', N'TF')) 
	DROP FUNCTION [dbo].[MD5_m_OnBits] 
GO 
/***************************************************************************** 
* Name: MD5_m_OnBits 
* Description: 常数组 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_m_OnBits( 
	@i  TINYINT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  DECLARE @iRes  INT 
  SELECT @iRes = 
    CASE @i 
  WHEN 0   THEN 1  -- 00000000000000000000000000000001 
  WHEN 1   THEN 3  -- 00000000000000000000000000000011 
  WHEN 2   THEN 7  -- 00000000000000000000000000000111 
  WHEN 3   THEN 15        -- 00000000000000000000000000001111 
  WHEN 4   THEN 31        -- 00000000000000000000000000011111 
  WHEN 5   THEN 63        -- 00000000000000000000000000111111 
  WHEN 6   THEN 127       -- 00000000000000000000000001111111 
  WHEN 7   THEN 255       -- 00000000000000000000000011111111 
  WHEN 8   THEN 511       -- 00000000000000000000000111111111 
  WHEN 9   THEN 1023     -- 00000000000000000000001111111111 
  WHEN 10 THEN 2047     -- 00000000000000000000011111111111 
  WHEN 11 THEN 4095     -- 00000000000000000000111111111111 
  WHEN 12 THEN 8191     -- 00000000000000000001111111111111 
  WHEN 13 THEN 16383    -- 00000000000000000011111111111111 
  WHEN 14 THEN 32767    -- 00000000000000000111111111111111 
  WHEN 15 THEN 65535    -- 00000000000000001111111111111111 
  WHEN 16 THEN 131071      -- 00000000000000011111111111111111 
  WHEN 17 THEN 262143      -- 00000000000000111111111111111111 
  WHEN 18 THEN 524287      -- 00000000000001111111111111111111 
  WHEN 19 THEN 1048575     -- 00000000000011111111111111111111 
  WHEN 20 THEN 2097151     -- 00000000000111111111111111111111 
  WHEN 21 THEN 4194303     -- 00000000001111111111111111111111 
  WHEN 22 THEN 8388607     -- 00000000011111111111111111111111 
  WHEN 23 THEN 16777215   -- 00000000111111111111111111111111 
  WHEN 24 THEN 33554431   -- 00000001111111111111111111111111 
  WHEN 25 THEN 67108863   -- 00000011111111111111111111111111 
  WHEN 26 THEN 134217727  -- 00000111111111111111111111111111 
  WHEN 27 THEN 268435455  -- 00001111111111111111111111111111 
  WHEN 28 THEN 536870911  -- 00011111111111111111111111111111 
  WHEN 29 THEN 1073741823    -- 00111111111111111111111111111111 
  WHEN 30 THEN 2147483647    -- 01111111111111111111111111111111 
  ELSE 0 
    END 
  RETURN(@iRes) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_m_2Power]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_m_2Power] 
GO 
/***************************************************************************** 
* Name: MD5_m_2Power 
* Description: 常数组 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_m_2Power( 
  @i  TINYINT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  DECLARE @iRes  INT 
  SELECT @iRes = 
    CASE @i 
  WHEN 0   THEN 1  -- 00000000000000000000000000000001 
  WHEN 1   THEN 2  -- 00000000000000000000000000000010 
  WHEN 2   THEN 4  -- 00000000000000000000000000000100 
  WHEN 3   THEN 8  -- 00000000000000000000000000001000 
  WHEN 4   THEN 16        -- 00000000000000000000000000010000 
  WHEN 5   THEN 32        -- 00000000000000000000000000100000 
  WHEN 6   THEN 64        -- 00000000000000000000000001000000 
  WHEN 7   THEN 128       -- 00000000000000000000000010000000 
  WHEN 8   THEN 256       -- 00000000000000000000000100000000 
  WHEN 9   THEN 512       -- 00000000000000000000001000000000 
  WHEN 10 THEN 1024     -- 00000000000000000000010000000000 
  WHEN 11 THEN 2048     -- 00000000000000000000100000000000 
  WHEN 12 THEN 4096     -- 00000000000000000001000000000000 
  WHEN 13 THEN 8192     -- 00000000000000000010000000000000 
  WHEN 14 THEN 16384    -- 00000000000000000100000000000000 
  WHEN 15 THEN 32768    -- 00000000000000001000000000000000 
  WHEN 16 THEN 65536    -- 00000000000000010000000000000000 
  WHEN 17 THEN 131072      -- 00000000000000100000000000000000 
  WHEN 18 THEN 262144      -- 00000000000001000000000000000000 
  WHEN 19 THEN 524288      -- 00000000000010000000000000000000 
  WHEN 20 THEN 1048576     -- 00000000000100000000000000000000 
  WHEN 21 THEN 2097152     -- 00000000001000000000000000000000 
  WHEN 22 THEN 4194304     -- 00000000010000000000000000000000 
  WHEN 23 THEN 8388608     -- 00000000100000000000000000000000 
  WHEN 24 THEN 16777216   -- 00000001000000000000000000000000 
  WHEN 25 THEN 33554432   -- 00000010000000000000000000000000 
  WHEN 26 THEN 67108864   -- 00000100000000000000000000000000 
  WHEN 27 THEN 134217728  -- 00001000000000000000000000000000 
  WHEN 28 THEN 268435456  -- 00010000000000000000000000000000 
  WHEN 29 THEN 536870912  -- 00100000000000000000000000000000 
  WHEN 30 THEN 1073741824    -- 01000000000000000000000000000000 
  ELSE 0 
    END 
  RETURN(@iRes) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_LShift]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_LShift] 
GO 
/***************************************************************************** 
* Name: MD5_LShift 
* Description: MD5_LShift 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_LShift( 
   @iValue    INT 
  ,@iShiftBits  TINYINT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  DECLARE @iRes  BIGINT 
  SET @iRes = CAST(@iValue AS BINARY(8)) 
  SET @iRes = @iRes * dbo.MD5_m_2Power(@iShiftBits) 
  RETURN(CAST(@iRes & 0x00000000FFFFFFFF AS BINARY(4))) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_RShift]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_RShift] 
GO 
/***************************************************************************** 
* Name: MD5_RShift 
* Description: MD5_RShift 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_RShift( 
   @iValue    INT 
  ,@iShiftBits  TINYINT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  DECLARE @iRes  BIGINT 
  SET @iRes = CAST(@iValue AS BINARY(8)) 
  SET @iRes = @iRes / dbo.MD5_m_2Power(@iShiftBits) 
  RETURN(CAST(@iRes & 0x00000000FFFFFFFF AS BINARY(4))) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_RotateLeft]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_RotateLeft] 
GO 
/***************************************************************************** 
* Name: MD5_RotateLeft 
* Description: MD5_RotateLeft 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_RotateLeft( 
   @iValue    INT 
  ,@iShiftBits  TINYINT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  RETURN(dbo.MD5_LShift(@iValue, @iShiftBits) | dbo.MD5_RShift(@iValue, (32 - @iShiftBits))) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_AddUnsigned]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_AddUnsigned] 
GO 
/***************************************************************************** 
* Name: MD5_AddUnsigned 
* Description: MD5_AddUnsigned 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_AddUnsigned( 
   @iX    INT 
  ,@iY    INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  DECLARE @iRes  BIGINT 
  SET @iRes = CAST(CAST(@iX AS BINARY(8)) AS BIGINT) + CAST(CAST(@iY AS BINARY(8)) AS BIGINT) 
  RETURN(CAST(@iRes & 0x00000000FFFFFFFF AS BINARY(4))) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_F]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_F] 
GO 
/***************************************************************************** 
* Name: MD5_F 
* Description: MD5_F 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_F( 
   @x    INT 
  ,@y    INT 
  ,@z    INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  RETURN((@x & @y) | ((~@x) & @z)) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_G]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_G] 
GO 
/***************************************************************************** 
* Name: MD5_G 
* Description: MD5_G 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_G( 
   @x    INT 
  ,@y    INT 
  ,@z    INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  RETURN((@x & @z) | (@y & (~@z))) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_H]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_H] 
GO 
/***************************************************************************** 
* Name: MD5_H 
* Description: MD5_H 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_H( 
   @x    INT 
  ,@y    INT 
  ,@z    INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  RETURN(@x ^ @y ^ @z) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_I]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_I] 
GO 
/***************************************************************************** 
* Name: MD5_I 
* Description: MD5_I 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_I( 
   @x    INT 
  ,@y    INT 
  ,@z    INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  RETURN(@y ^ (@x | (~@z))) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_FF]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_FF] 
GO 
/***************************************************************************** 
* Name: MD5_FF 
* Description: MD5_FF 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_FF( 
   @a    INT 
  ,@b    INT 
  ,@c    INT 
  ,@d    INT 
  ,@x    INT 
  ,@s   INT 
  ,@ac  INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  SET @a = dbo.MD5_AddUnsigned(@a, dbo.MD5_AddUnsigned(dbo.MD5_AddUnsigned(dbo.MD5_F(@b, @c, @d), @x), @ac)) 
  SET @a = dbo.MD5_RotateLeft(@a, @s) 
  SET @a = dbo.MD5_AddUnsigned(@a, @b) 
  RETURN(@a) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_GG]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_GG] 
GO 
/***************************************************************************** 
* Name: MD5_GG 
* Description: MD5_GG 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_GG( 
   @a    INT 
  ,@b    INT 
  ,@c    INT 
  ,@d    INT 
  ,@x    INT 
  ,@s   INT 
  ,@ac  INT 
) 
RETURNS INT 
WITH ENCRYPTION 
AS 
BEGIN 
  SET @a = dbo.MD5_AddUnsigned(@a, dbo.MD5_AddUnsigned(dbo.MD5_AddUnsigned(dbo.MD5_G(@b, @c, @d), @x), @ac)) 
  SET @a = dbo.MD5_RotateLeft(@a, @s) 
  SET @a = dbo.MD5_AddUnsigned(@a, @b) 
  RETURN(@a) 
END 
GO 

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MD5_HH]') AND xtype IN(N'FN', N'IF', N'TF')) 
  DROP FUNCTION [dbo].[MD5_HH] 
GO 
/***************************************************************************** 
* Name: MD5_HH 
* Description: MD5_HH 
*****************************************************************************/ 
CREATE FUNCTION dbo.MD5_HH(