MD5算法的T-SQL实现(FOR SQL2000)（一）

/*****************************************************************************
* 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(
 @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_H(@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_II]') AND xtype IN(N'FN', N'IF', N'TF'))
 DROP FUNCTION [dbo].[MD5_II]
GO
/*****************************************************************************
* Name: MD5_II
* Description: MD5_II
*****************************************************************************/
CREATE FUNCTION dbo.MD5_II(
 @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_I(@b, @c, @d), @x), @ac))
 SET @a = dbo.MD5_RotateLeft(@a, @s)
 SET @a = dbo.MD5_AddUnsigned(@a, @b)
 RETURN(@a)
END
GO