SQL MD5 算法对中文字符串不能产生正确的 MD5 码 的解决办法

网上流传的SQL MD5 算法经过检查，对中文字符串产生的 MD5 码，是不正确的。该算法的验证例子中的:

select dbo.MD5('我') as 'MD5(''我'')'
union
select 'a31d0f25367ebe046897f8a939ca4a9f'

但是，"我" 的正确的MD5码是:A92F38654F3A250602AF1E69A88699E3

经过检查是由于dbo.MD5_ConvertToWordArray函数对Unicode的处理有错误。改正后的函数如下：

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*****************************************************************************
* Name: MD5_ConvertToWordArray
* Description: MD5_ConvertToWordArray
*****************************************************************************/
ALTER FUNCTION dbo.MD5_ConvertToWordArray(
@sOrigMess VARCHAR(8000) = ''
)
RETURNS @tWordArray TABLE([ID] INT IDENTITY(0,1),[Word] INT)
AS
BEGIN
IF @sOrigMess IS NULL
SET @sOrigMess = ''

DECLARE @bOrigMess VARBINARY(8000)
SET @bOrigMess = CONVERT(VARBINARY, @sOrigMess)

DECLARE @iLenOfMess INT
DECLARE @iWordArrayLen INT
DECLARE @iPosOfWord INT
DECLARE @iPosOfMess INT
DECLARE @iCountOfWord INT

SET @iLenOfMess = DATALENGTH(@bOrigMess)
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(@bOrigMess, @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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO