SQL Server MD5 加密與解密

一、SQL Server MD5 加密

在 SQL Server 中，可以使用內置的 HASHBYTES 函數進行 MD5 加密：

DECLARE @password nvarchar(20)
SET @password = 'mypassword'
SELECT CONVERT(varbinary(16), HASHBYTES('MD5', @password), 2) AS hashed_password

上述代碼中，首先定義一個變量 @password 並且將其賦值為明文密碼，然後使用 HASHBYTES 函數將其進行 MD5 加密，最後使用 CONVERT 函數將二進制值轉換成字符串進行顯示。

需要注意的是，在使用 HASHBYTES 函數時，需要指定要使用的加密方式，如上述代碼中的 ‘MD5’ 參數即為要進行 MD5 加密。

二、SQL Server MD5 解密

由於 MD5 加密是不可逆的，也就意味着無法通過解密來獲取明文密碼。因此，在 SQL Server 中，並沒有內置的 MD5 解密函數。

三、應用場景

MD5 加密技術通常用於用戶密碼的存儲，可以保證用戶密碼在存儲時不會明文存儲。在用戶登錄時，需要將用戶輸入的明文密碼通過 MD5 加密後與數據庫中的密文進行比較，從而進行身份驗證。

下面是一個示例代碼，用於在 SQL Server 中進行用戶密碼的加密和驗證：

-- 創建用戶表
CREATE TABLE [dbo].[users] (
    [id] INT IDENTITY (1, 1) NOT NULL,
    [username] NVARCHAR(50) NOT NULL,
    [password] NVARCHAR(50) NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

-- 插入用戶數據
INSERT INTO [dbo].[users] ([username], [password]) VALUES ('user1', CONVERT(varbinary(16), HASHBYTES('MD5', 'password1'), 2))
INSERT INTO [dbo].[users] ([username], [password]) VALUES ('user2', CONVERT(varbinary(16), HASHBYTES('MD5', 'password2'), 2))
GO

-- 驗證用戶密碼
DECLARE @username nvarchar(50)
DECLARE @password nvarchar(50)
SET @username = 'user1'
SET @password = 'password1'

IF EXISTS (
    SELECT 1 FROM [dbo].[users]
    WHERE [username] = @username AND [password] = CONVERT(varbinary(16), HASHBYTES('MD5', @password), 2)
)
BEGIN
    PRINT 'Authentication succeeded!'
END
ELSE
BEGIN
    PRINT 'Authentication failed!'
END

上述代碼中，首先創建了一個用戶表，其中包含 username 和 password 兩個字段。然後通過 INSERT 語句向表中插入了兩個用戶數據，其中密碼字段通過 HASHBYTES 函數進行了 MD5 加密。

最後通過 DECLARE 和 SET 語句分別指定要驗證的用戶名和密碼，並且使用 EXISTS 和 SELECT 語句來查詢是否存在匹配的用戶數據。

四、安全性問題

雖然 MD5 加密可以保證用戶密碼在存儲時不會明文存儲，但是由於其已經被證實為不安全的加密方式，因此容易被暴力破解。為了提高密碼的安全性，建議在進行密碼加密時，使用更加安全的加密方式，例如 SHA256、PBKDF2 等。

下面是一個示例代碼，使用 PBKDF2 算法進行密碼加密：

-- 創建用戶表
CREATE TABLE [dbo].[users] (
    [id] INT IDENTITY (1, 1) NOT NULL,
    [username] NVARCHAR(50) NOT NULL,
    [salt] VARBINARY(16) NOT NULL,
    [hash] VARBINARY(32) NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

-- 插入用戶數據
DECLARE @username nvarchar(50)
DECLARE @password nvarchar(50)
SET @username = 'user3'
SET @password = 'password3'
DECLARE @salt varbinary(16)
SET @salt = CAST(NEWID() AS varbinary(16))
DECLARE @hash varbinary(32)
SET @hash = HASHBYTES('SHA2_256', @password + CAST(@salt AS varchar(36))) 
    + HASHBYTES('SHA2_256', @password + CAST(REVERSE(@salt) AS varchar(36)))
DECLARE @iterations int
SET @iterations = 10000
DECLARE @hash_str nvarchar(200)
SET @hash_str = N'pbkdf2_sha256$' + CONVERT(nvarchar(20), @iterations) + N'$' + CONVERT(nvarchar(36), @salt, 2) + N'$' + CONVERT(nvarchar(64), @hash, 2)

INSERT INTO [dbo].[users] ([username], [salt], [hash]) VALUES (@username, @salt, @hash)
GO

-- 驗證用戶密碼
DECLARE @username nvarchar(50)
DECLARE @password nvarchar(50)
SET @username = 'user3'
SET @password = 'password3'

DECLARE @hash_str nvarchar(200)
SELECT @hash_str = [hash] FROM [dbo].[users] WHERE [username] = @username

IF dbo.pbkdf2_sha256_verify(@password, @hash_str) = 1
BEGIN
    PRINT 'Authentication succeeded!'
END
ELSE
BEGIN
    PRINT 'Authentication failed!'
END
GO

-- PBKDF2 算法實現
CREATE FUNCTION dbo.pbkdf2_sha256(@password nvarchar(4000), @salt varbinary(16), @iterations int)
RETURNS varbinary(32)
AS
BEGIN
    DECLARE @len int
    DECLARE @block_no int
    DECLARE @block varbinary(64)
    DECLARE @u varbinary(32)
    DECLARE @t_i varbinary(32)

    DECLARE @dk varbinary(32)
    SET @dk = 0x
    SET @len = CEILING(32 / HASHBYTES('SHA2_256', ''))
    SET @block_no = CEILING(64 / DATALENGTH(@salt + CAST(1 AS binary(4))))

    DECLARE @i int
    SET @i = 1
    WHILE @i <= @block_no
    BEGIN
        SET @block = @salt + CAST(@i AS binary(4))
        SET @u = HASHBYTES('SHA2_256', @block)
        SET @t_i = @u

        DECLARE @j int
        SET @j = 2
        WHILE @j <= @iterations
        BEGIN
            SET @u = HASHBYTES('SHA2_256', @u)
            SET @t_i = dbo.pbkdf2_xor(@t_i, @u)
            SET @j = @j + 1
        END

        SET @dk = @dk + @t_i
        SET @i = @i + 1
    END

    RETURN SUBSTRING(@dk, 1, 32)
END
GO

-- 異或運算實現
CREATE FUNCTION dbo.pbkdf2_xor(@a varbinary(32), @b varbinary(32))
RETURNS varbinary(32)
AS
BEGIN
    DECLARE @c varbinary(32)
    SET @c = 0x

    DECLARE @i int
    SET @i = 1
    WHILE @i <= 32
    BEGIN
        SET @c = @c + CAST(SUBSTRING(@a, @i, 1) ^ SUBSTRING(@b, @i, 1) AS binary(1))
        SET @i = @i + 1
    END

    RETURN @c
END
GO

-- 驗證用戶密碼的函數
CREATE FUNCTION dbo.pbkdf2_sha256_verify(@password nvarchar(4000), @hash_str nvarchar(200))
RETURNS int
AS
BEGIN
    DECLARE @hash_len int
    DECLARE @iterations int
    DECLARE @salt varbinary(16)
    DECLARE @hash varbinary(32)

    DECLARE @pos1 int
    DECLARE @pos2 int

    SET @pos1 = CHARINDEX('$', @hash_str)
    SET @pos2 = CHARINDEX('$', @hash_str, @pos1 + 1)

    SET @iterations = CONVERT(int, SUBSTRING(@hash_str, @pos1 + 1, @pos2 - @pos1 - 1))

    SET @pos1 = @pos2
    SET @pos2 = CHARINDEX('$', @hash_str, @pos1 + 1)

    SET @salt = CONVERT(varbinary(16), SUBSTRING(@hash_str, @pos1 + 1, @pos2 - @pos1 - 1), 2)

    SET @pos1 = @pos2
    SET @hash = CONVERT(varbinary(32), SUBSTRING(@hash_str, @pos1 + 1, LEN(@hash_str) - @pos1), 2)

    RETURN CAST(dbo.pbkdf2_sha256(@password, @salt, @iterations) AS varchar(64)) = CAST(@hash AS varchar(64)) 
END
GO

上述代碼中，首先創建了一個用戶表，其中除了 username 和 hash 字段之外，還增加了 salt 字段。salt 字段用於存儲一個隨機生成的字符串，可以為密碼增加一層額外的保護。

然後通過 DECLARE 和 SET 語句分別指定要加密的用戶名和密碼，並且使用 HASHBYTES 函數對密碼進行加密，並通過 PBKDF2 算法加密我們的密碼。

最後通過 DECLARE 和 SET 語句分別指定要驗證的用戶名和密碼，並且使用 dbo.pbkdf2_sha256_verify 函數進行驗證。

結束語

MD5 加密雖然已經不再安全，但是對於一些簡單的加密場景仍然可以滿足要求。在進行密碼加密時，建議使用更加安全的算法，例如 SHA256、PBKDF2 等。