SQLServer中的MD5加密实现方法——区分32位和16位加密
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
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
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
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
CREATE FUNCTION dbo.MD5_I(
@x INT
,@y INT
,@z INT
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
RETURN(@y ^ (@x | (~@z)))
END
GO
CREATE FUNCTION dbo.MD5_H(
@x INT,
@y INT,
@z INT
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
RETURN(@x ^ @y ^ @z)
END
GO
CREATE FUNCTION dbo.MD5_G(
@x INT,
@y INT,
@z INT
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
RETURN((@x & @z) | (@y & (~@z)))
END
GO
CREATE FUNCTION dbo.MD5_F(
@x INT,
@y INT,
@z INT
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
RETURN((@x & @y) | ((~@x) & @z))
END
GO
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
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
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
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
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
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
CREATE FUNCTION dbo.MD5_ConvertToWordArray
(
@sOrigMess VARCHAR(8000)=''
)
RETURNS @tWordArray TABLE([ID] INT IDENTITY(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(UNICODE(SUBSTRING(@sOrigMess,@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
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 WHEN 0 THEN '0'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
WHEN 8 THEN '8'
WHEN 9 THEN '9'
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN '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 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
WHEN 8 THEN '8'
WHEN 9 THEN '9'
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE '' END
SET @iCount = @iCount + 1
END
RETURN(@sRes)
END
GO
CREATE FUNCTION dbo.MD5(
@sOrigMess NVARCHAR(4000),@lenValue INT
)
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 = 20
SELECT @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] = @iWordArrayCount + 1), @S12, 0xE8C7B756)
SELECT @c = dbo.MD5_FF(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 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] = @iWordArrayCount + 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] = @iWordArrayCount + 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] = @iWordArrayCount + 5), @S31, 0xFFFA3942)
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, 0xA4BEEA44)
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, 0x655B59C3)
SELECT @d = dbo.MD5_II(@d, @a, @b, @c, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 3), @S42, 0x8F0CCC92)
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] = @iWordArrayCount + 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] = @iWordArrayCount + 11), @S42, 0xBD3AF235)
SELECT @c = dbo.MD5_II(@c, @d, @a, @b, (SELECT [Word] FROM @tTmp WHERE [ID] = @iWordArrayCount + 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
IF @lenValue = 32
SET @sRes = dbo.MD5_WordToHex(@a) + dbo.MD5_WordToHex(@b) + dbo.MD5_WordToHex(@c) + dbo.MD5_WordToHex(@d)
ELSE
BEGIN
SET @sRes = dbo.MD5_WordToHex(@b) + dbo.MD5_WordToHex(@c)
END
SET @sRes = LOWER(@sRes)
RETURN(@sRes)
END
GO
--执行函数
--生成MD5
PRINT dbo.MD5('1987',16)
--生成6位随机数
PRINT cast(rand()*999999+100000 AS int)
--生成6位加密MD5密码
PRINT dbo.MD5(cast(rand()*999999+100000 AS int),16)
★大家请注意代码的末尾，那里有一个IF语句对MD5加密实现32位加密与16位加密进行了判断。
好多初学者可能不知道怎么使用或者有高手搞不懂为什么分32位和16加密，其实也没有很复杂，就是因为有很多客户的MD5加密用的是16位，为了与以前的程序保持同步所以进行了判断。
具体怎么使用我想就不用说了吧！这个很简单了！我在项目中是应用到触发器中的。见下图：
16位加密代码示例
32位加密代码示例
大家请注意我的密码是随机产生的。
分享到：
发表评论
相关推荐
### SQL Server MD5函数知识点详解 ...通过上述介绍，我们了解到如何在SQL Server中实现MD5加密功能，并了解了其中涉及的关键技术点。这对于在实际工作中需要处理数据加密任务的开发者来说具有重要的参考价值。
在给定的文件名"SQLEncryption"中，可能包含示例代码、数据库脚本或相关的教程资源，这些资源可以帮助你更好地理解和实现SQL Server与C#中的DES加密和解密功能。在实践中，确保遵循最佳安全实践，如定期更换密钥，...
在SQL Server 2005中，开发人员经常需要处理数据加密和编码转换，例如Base64、MD5和SHA1。这些算法在数据库安全、数据验证和信息完整性方面起着重要作用。本篇文章将详细介绍如何在SQL Server 2005环境下轻松实现这...
本文将详细讲解如何在SQL Server 2005中实现Base64的加密和解密功能。 一、Base64编码原理 Base64编码基于64个可打印字符，包括大小写字母、数字以及"+"和"/"，还有一个特殊字符"="用于填充。它将每3个字节的二进制...
### SQL Server 中的数据加密方法详解 #### 一、引言 在现代信息技术中，数据安全至关重要，尤其是在数据库管理系统中。SQL Server 作为一款广泛使用的数据库系统，提供了多种数据加密手段来保护敏感信息的安全。...
此外，第三方工具如XP_CRYPT提供更深入的加密集成，适用于SQL Server的视图、存储过程和触发器，支持MD5、DES和SHA1等哈希算法。 对于数据库文件本身的加密，可以利用Windows的加密文件系统（EFS），但需要备份加密...
例如，XP_CRYPT是专为SQL Server设计的插件，通过扩展存储过程和用户自定义函数提供加密功能，支持MD5、DES和SHA1等哈希算法。此外，还有免费版本供试用，但完整版则提供无限制的比特深度加密。对于ASP开发人员，...
下面将详细介绍如何在.NET中实现MD5加密，并与SQL Server数据库结合使用。 ##### 1. 导入必要的命名空间 ```csharp using System; using System.Collections.Generic; using System.Linq; using System.Text; ...
SQL Server 和 MySql 语法和关键字的区别——用于 SQLServer 到 MySql 的转换 SQL Server 和 MySql 是两种常用的关系型数据库管理系统，但是它们之间存在一定的语法和关键字差异。这些差异使得开发者在从 SQL ...
根据提供的文件信息，本文将详细解释SQL Server中的对象加密功能，并深入探讨所提供的存储过程`sp_EncryptObject`的具体实现逻辑。 ### SQL Server 加密概述 SQL Server提供了多种方式来保护数据的安全性，其中...
在SQLSERVER数据库的使用过程中，有时会出现一个棘手的问题，即驱动程序无法通过安全套接字层（SSL）加密与SQL Server建立安全连接。这个问题通常表明数据库连接在尝试使用SSL进行加密时遇到了障碍，可能影响数据的...
在给定的标题和描述中，我们关注的是如何通过存储过程访问Microsoft SQL Server 2000并实现返回值以及MD5加密。下面将详细阐述这两个核心知识点。 首先，存储过程（Stored Procedure）是预编译的SQL语句集合，它们...
在"sqlserver——oracle工具"的标题中，我们关注的是在SQL Server和Oracle之间进行数据迁移的工具。常见的数据迁移工具有： 1. Oracle SQL Developer Data Pump：Oracle官方提供的免费工具，可以方便地导入导出数据...
### 如何在SQL Server数据库中加密数据 #### 安全测试中的注意事项与SQL注入威胁 在探讨如何在SQL Server数据库中加密数据之前，我们需要先理解为何数据加密如此重要。文件描述提到的安全测试中需要注意的问题主要...
### SQL Server 存储过程 With Encryption 加密的解密方法 #### 背景与目的 在SQL Server中，为了保护存储过程中的敏感代码或逻辑，可以使用`WITH ENCRYPTION`选项对存储过程进行加密处理。这可以有效防止未经授权...
在SQL Server 2005中实现MD5加密，可以使用内置的`HASHBYTES`函数，该函数支持多种哈希算法，包括MD5。例如，若要对字符串进行MD5加密，可以使用如下SQL语句： ```sql SELECT HASHBYTES('MD5', 'your_string'); ```...
在SQL Server中，强制加密是一种安全措施，用于保护数据库中的数据免受未经授权的访问。它通过SSL（Secure Sockets Layer）或TLS（Transport Layer Security）协议实现，确保数据在网络传输过程中被加密。以下是对...
本压缩包包含了针对32位和64位系统的两个补丁：SQLServer2005_BC.msi 和 SQLServer2005_BC_x64.msi，主要目的是为了提升SQL Server 2005 Business Intelligence (BI)组件的兼容性和性能。 SQL Server 2005 Business...
请注意，SQL Server的`CHECKSUM_AGG`和`BINARY_CHECKSUM`并不是真正的MD5函数，实际应用中应使用`HASHBYTES`函数配合`MD5`算法： ```sql SELECT HASHBYTES('MD5', 'YourPassword') AS MD5Hash; ``` 这条语句会返回'...