March 25,2004
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [Md5_converttowordArray]') and xtype in (n'fn ', n'if ", n't')))
Drop function [dbo]. [MD5_CONVERTTOWORDARRAY]
Go
/ ************************************************** *****************************
* Name: MD5_CONVERTTOWORDARRAY
* Description: MD5_CONVERTTOWORDARRAY
*********************************************************** *********************************** /
Create function dbo.md5_convertToWordArray (
@sorigmess varchar (8000) = ''
)
Returns @twordArray Table ([ID] Int Idnessity (0, 1), [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 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 (substring (@ sorigmess, @ iposofmess 1, 1), @ iposofword * 8)
Where [id] = @icountofword
Set @iposofmess = @iposofmess 1
End
SELECT @icountofword = @iposofmess / 4, @iposofword = @iposofmess% 4UPDATE @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't')))
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 by 0 Then '0'
When 1 Ten '1'
When 2 Then '2'
When 3 Ten '3'
When 4 Ten '4'
When 5 Ten '5'
When 6 Then '6'
When 7 Ten '7'
When 8 Then '8'
When 9 Ten '9'
When 10 Ten 'A'
When 11 Then 'b'
When 12 Ten 'c'
When 13 Ten '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 Ten '3'
When 4 Ten '4'
When 5 Ten '5'
When 6 Then '6'
When 7 Ten '7'
When 8 Then '8'
When 9 Ten '9'
When 10 Ten 'A'
When 11 Then 'b'
When 12 Ten 'c'
When 13 Ten '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't')))
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 = 20select @ 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 @ttmp
While (@iWordArraycount <@iwordArraylen)
Begin
SELECT @AA = @a, @BB = @B, @cc = @c, @dd = @d
Select @a = dbo.md5_ff (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 0), @ S11, 0xD76AA478)
SELECT @d = dbo.md5_ff (@D, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordaraycount 1), @ S12, 0xE8C7B756)
Select @c = dbo.md5_ff (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordaraycount 2), @ S13, 0x242070dB) select @B = dbo. MD5_FF (@B, @C, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 3), @ S14, 0xc1bdceee)
Select @a = dbo.md5_ff (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 4), @ S11, 0xF57c0faf)
SELECT @d = dbo.md5_ff (@D, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordaraycount 5), @ S12, 0x4787c62a)
SELECT @c = dbo.md5_ff (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 6), @ S13, 0xA8304613)
SELECT @B = dbo.md5_ff (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 7), @ S14, 0xFD469501)
Select @a = dbo.md5_ff (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordaraycount 8), @ S11, 0x698098d8)
SELECT @d = dbo.md5_ff (@D, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 9), @ S12, 0x8b44f7af)
SELECT @c = dbo.md5_ff (@c, @D, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 10), @ S13, 0xffff5bb1)
SELECT @B = dbo.md5_ff (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 11), @ S14, 0x895cd7be)
Select @a = dbo.md5_ff (@a, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 12), @ S11, 0x6b901122) Select @d = dbo. MD5_FF (@D, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 13), @ S12, 0xFD987193)
SELECT @c = dbo.md5_ff (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 14), @ S13, 0xA679438E)
SELECT @B = dbo.md5_ff (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 15), @ S14, 0x49b40821)
Select @a = dbo.md5_gg (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 1), @ S21, 0xf61e2562)
SELECT @d = dbo.md5_gg (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 6), @ S22, 0xc040b340)
SELECT @c = dbo.md5_gg (@c, @D, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 11), @ S23, 0x265e5a51)
SELECT @B = dbo.md5_gg (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 0), @ S24, 0xE9B6C7AA)
SELECT @a = dbo.md5_gg (@a, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 5), @ S21, 0xD62F105D)
SELECT @d = dbo.md5_gg (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 10), @ S22, 0x2441453)
SELECT @c = dbo.md5_gg (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 15), @ S23, 0xD8A1E681) Select @B = dbo. MD5_GG (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 4), @ S24, 0xE7D3FBC8)
Select @a = dbo.md5_gg (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 9), @ S21, 0x21e1cde6)
SELECT @d = dbo.md5_gg (@D, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 14), @ S22, 0xc33707d6)
SELECT @c = dbo.md5_gg (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 3), @ S23, 0xf4d50d87)
SELECT @B = dbo.md5_gg (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 8), @ S24, 0x455A14ed)
SELECT @a = dbo.md5_gg (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 13), @ S21, 0xA9E3E905)
SELECT @d = dbo.md5_gg (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 2), @ S22, 0xfcefa3f8)
Select @c = dbo.md5_gg (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 7), @ S23, 0x676f02d9)
SELECT @B = dbo.md5_gg (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 12), @ S24, 0x8D2A4C8A)
Select @a = dbo.md5_hh (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordaraycount 5), @ S31, 0xffa3942) SELECT @d = dbo. MD5_HH (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 8), @ S32, 0x8771f681)
Select @c = dbo.md5_hh (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 11), @ S33, 0x6d9d6122)
SELECT @B = dbo.md5_hh (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 14), @ S34, 0xFDE5380C)
Select @a = dbo.md5_hh (@a, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 1), @ S31, 0xA4bee44)
SELECT @d = dbo.md5_hh (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 4), @ S32, 0x4bdecfa9)
Select @c = dbo.md5_hh (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 7), @ S33, 0xF6bb4b60)
SELECT @B = dbo.md5_hh (@B, @C, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 10), @ S34, 0xbebfbc70)
Select @a = dbo.md5_hh (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 13), @ s31, 0x289b7ec6)
SELECT @d = dbo.md5_hh (@d, @a, @B, @C, (select [word] from @ttmp where [id] = @iwordArraycount 0), @ S32, 0xEAA127FA)
Select @c = dbo.md5_hh (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 3), @ s33, 0xd4ef3085) SELECT @B = dbo. MD5_HH (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 6), @ S34, 0x4881D05)
Select @a = dbo.md5_hh (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 9), @ S31, 0xD9D4D039)
Select @d = dbo.md5_hh (@d, @a, @B, @C, (select [word] from @ttmp where [id] = @iwordArraycount 12), @ S32, 0xE6DB99E5)
SELECT @c = dbo.md5_hh (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 15), @ S33, 0x1fa27cf8)
SELECT @B = dbo.md5_hh (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 2), @ S34, 0xc4ac5665)
Select @a = dbo.md5_ii (@a, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 0), @ S41, 0xF4292244)
Select @d = dbo.md5_ii (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 7), @ S42, 0x432AFF97)
Select @c = dbo.md5_ii (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 14), @ s43, 0xAb9423a7)
SELECT @B = dbo.md5_ii (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 5), @ S44, 0xFC93A039)
Select @a = dbo.md5_ii (@a, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 12), @ S41, 0x65559c3) select @d = dbo. MD5_II (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 3), @ S42, 0x8f0cc92)
Select @c = dbo.md5_ii (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 10), @ S43, 0xffeff47d)
SELECT @B = dbo.md5_ii (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 1), @ S44, 0x85845dd1)
Select @a = dbo.md5_ii (@a, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 8), @ S41, 0x6fa87e4f)
Select @d = dbo.md5_ii (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordArraycount 15), @ S42, 0xfe2CE6E0)
Select @c = dbo.md5_ii (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordArraycount 6), @ S43, 0xA3014314)
SELECT @B = dbo.md5_ii (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordaraycount 13), @ S44, 0x4e0811A1)
Select @a = dbo.md5_ii (@A, @B, @c, @d, (select [word] from @ttmp where [id] = @iwordArraycount 4), @ s41, 0xf7537e82)
Select @d = dbo.md5_ii (@d, @a, @B, @c, (select [word] from @ttmp where [id] = @iwordaraycount 11), @ S42, 0xBD3AF235)
SELECT @c = dbo.md5_ii (@c, @d, @a, @B, (select [word] from @ttmp where [id] = @iwordaraycount 2), @ S43, 0x2AD7D2BB) SELECT @B = dbo. MD5_II (@B, @c, @d, @a, (select [word] from @ttmp where [id] = @iwordArraycount 9), @ S44, 0XEB86D391)
Set @a = dbo.md5_addunsigned (@a, @AA)
Set @B = dbo.md5_addunsigned (@B, @BB)
Set @c = dbo.md5_addunsigned (@c, @cc)
Set @d = dbo.md5_addunsigned (@d, @dd)
Set @iwordArraycount = @iwordArrayCount 16
End
Set @SRES = dbo.md5_wordtohex (@a) dbo.md5_wordtohex (@B) dbo.md5_wordtohex (@c) dbo.md5_wordtohex (@D)
Set @SRES = Lower (@sres)
Return (@SRES)
End
Go
--Test
Set nocount on
SELECT DBO.MD5 ('') AS 'MD5 (' '') '
union
SELECT 'D41D8CD98F00B204E9800998ECF8427E'
Select dbo.md5 ('a') AS 'MD5 (' 'A' ')'
union
SELECT '0CC175B9C0F1B6A831C399E269772661'
Select dbo.md5 ('ABC') AS 'MD5 (' 'ABC') '
union
SELECT '900150983CD24FB0D6963F7D28E17F72'
Select dbo.md5 ('Message Digest') AS 'MD5 (' 'Message Digest' ')'
union
SELECT 'F96B697D7CB7938D525A2F31AAF161D0'
SELECT DBO.MD5 ('AbcdefghijklmnopqrStuvwxyz') AS 'MD5 (' 'AbcdefghijklmnopqrStuvwxyz' ')'
union
SELECT 'C3FCD3D76192E4007DFB496CCA67E13B'
select dbo.MD5 ( 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') as 'MD5 (' 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' ')'
union
SELECT 'D174AB98D277D9F5A5611C2C9F419D9F'
select dbo.MD5 ( '12345678901234567890123456789012345678901234567890123456789012345678901234567890') as 'MD5 (' '12345678901234567890123456789012345678901234567890123456789012345678901234567890' ')'
union
SELECT '57EDF4A22BE3C955AC49DA2E2107B67A'
SELECT DBO.MD5 ('I') AS 'MD5 (' I '' ')'
union
SELECT 'A31D0F25367EBE046897F8A939CA4A9F'
