在 SQL Server 2000 時代,資料庫引擎並未提供內建的 MD5 雜湊函數。為了在預存程序、觸發器或查詢中直接產生 MD5 值 (如密碼雜湊、資料指紋),筆者 (Rambo Qian) 於 2003 年發表了完整的 T-SQL 實作。本文為系列第一部分,奠定基礎常數與位元運算函數。
回傳低 @i 位皆為 1 的整數 (如 @i=3 回傳 0x00000007)。用於後續位元擷取與操作。
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 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 15
WHEN 4 THEN 31 WHEN 5 THEN 63 WHEN 6 THEN 127 WHEN 7 THEN 255
WHEN 8 THEN 511 WHEN 9 THEN 1023 WHEN 10 THEN 2047 WHEN 11 THEN 4095
WHEN 12 THEN 8191 WHEN 13 THEN 16383 WHEN 14 THEN 32767 WHEN 15 THEN 65535
WHEN 16 THEN 131071 WHEN 17 THEN 262143 WHEN 18 THEN 524287 WHEN 19 THEN 1048575
WHEN 20 THEN 2097151 WHEN 21 THEN 4194303 WHEN 22 THEN 8388607 WHEN 23 THEN 16777215
WHEN 24 THEN 33554431 WHEN 25 THEN 67108863 WHEN 26 THEN 134217727 WHEN 27 THEN 268435455
WHEN 28 THEN 536870911 WHEN 29 THEN 1073741823 WHEN 30 THEN 2147483647
ELSE 0 END
RETURN @iRes
END
回傳 2^@i,作為左移運算的乘數。
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 WHEN 1 THEN 2 WHEN 2 THEN 4 WHEN 3 THEN 8
WHEN 4 THEN 16 WHEN 5 THEN 32 WHEN 6 THEN 64 WHEN 7 THEN 128
WHEN 8 THEN 256 WHEN 9 THEN 512 WHEN 10 THEN 1024 WHEN 11 THEN 2048
WHEN 12 THEN 4096 WHEN 13 THEN 8192 WHEN 14 THEN 16384 WHEN 15 THEN 32768
WHEN 16 THEN 65536 WHEN 17 THEN 131072 WHEN 18 THEN 262144 WHEN 19 THEN 524288
WHEN 20 THEN 1048576 WHEN 21 THEN 2097152 WHEN 22 THEN 4194304 WHEN 23 THEN 8388608
WHEN 24 THEN 16777216 WHEN 25 THEN 33554432 WHEN 26 THEN 67108864 WHEN 27 THEN 134217728
WHEN 28 THEN 268435456 WHEN 29 THEN 536870912 WHEN 30 THEN 1073741824
ELSE 0 END
RETURN @iRes
END
利用 BIGINT 暫存乘積,再取低 32 位模擬 32 位元無號左移。
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
同樣透過 BIGINT 除法實作無號右移,補 0 不補號。
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
下篇將介紹訊息填充函數 MD5_ConvertToWordArray、十六進位轉換函數 MD5_WordToHex 以及整合所有運算的主函數 MD5,敬請期待。