如何哈希超出 SQL Server HASHBYTES 输入参数限制（8000 字节）的二进制数据

引言

有时，我们会将二进制数据存储在数据库中，并需要对其进行哈希处理，以获取二进制数据的标识符。我们知道 SQL Server 具有内置函数来执行此操作，即 HASHBYTES。但其允许的输入值限制为 8000 字节。那么如何哈希超过 8000 字节的二进制数据呢？我们知道 SQL Server 可以通过 CLR 进行扩展。这里，主要有两种哈希算法：MD5 和 SHA1。现在让我们开始吧。

背景

您需要具备 C#、SQL、SHA1、MD5 的知识。

Using the Code

1. 在 Visual Studio 2015 中，创建一个 SQL Server 数据库项目。

   

2. 添加一个带有 SQL CLR C# 用户定义函数模板的文件。

   

3. 将 SHA1 和 MD5 方法添加到您的 .cs 文件中，如下所示。

   使用 SHA1 哈希二进制数据的 C# 方法代码行

   /// <summary>
   /// Encrypt data with type [varbinary](max) in sql server using SHA1 
   /// then return the encrypted data 
   /// </summary>
   /// <param name="content">Input data you will enter to encrypt it</param>
   /// <returns>Return the encrypted text as hexadecimal string</returns>
   [SqlFunction(DataAccess = DataAccessKind.None)]
   public static String ComputeSHA1(SqlBytes content)
   {
       String hashSHA1 = String.Empty;
       //Create new instance of SHA1 and convert the input data to array of bytes
       SHA1 calculator = SHA1.Create();
       Byte[] buffer = calculator.ComputeHash(content.Stream);
       calculator.Clear();
    
       //loop for each byte, convert it to hexadecimal string and add it to StringBuilder
       StringBuilder stringBuilder = new StringBuilder();
       for (int i = 0; i < buffer.Length; i++)
       {
           stringBuilder.Append(buffer[i].ToString("x2"));
       }
       hashSHA1 = stringBuilder.ToString();
    
       // return hexadecimal string
       return hashSHA1;
   }

   使用 MD5 哈希二进制数据的 C# 方法代码行

   /// <summary>
   /// Encrypt data with type [varbinary](max) in sql server using MD5 
   /// then return the encrypted data 
   /// </summary>
   /// <param name="content">Input data you will enter to encrypt it</param>
   /// <returns>Return the encrypted text as hexadecimal string</returns>
   [SqlFunction(DataAccess = DataAccessKind.None)]
   public static String ComputeMD5(SqlBytes content)
   {
       String hashMD5 = String.Empty;
    
       //Create new instance of md5 and convert the input data to array of bytes
       MD5 calculator = MD5.Create();
       Byte[] buffer = calculator.ComputeHash(content.Stream);
       calculator.Clear();
    
       //loop for each byte, convert it to hexadecimal string and add it to StringBuilder
       StringBuilder stringBuilder = new StringBuilder();
       for (int i = 0; i < buffer.Length; i++)
       {
           stringBuilder.Append(buffer[i].ToString("x2"));
       }
       hashMD5 = stringBuilder.ToString();
    
       //return hexadecimal string
       return hashMD5;
   }

4. 构建您的项目，您将在 bin 目录中获得一个 DLL 文件。
5. 将您的程序集文件发布到您的 SQL Server 数据库中。有两种方法可以做到这一点
   • 使用 Visual Studio 提供的发布工具。您可以生成脚本文件并使用该脚本文件进行发布，或直接将其发布到您的数据库中。

     

   • 使用 Transact-SQL 手动注册程序集到您的数据库中。

首先，您应该确保在您的数据库中启用 CLR。如果未启用，请执行以下 SQL

EXEC sp_configure 'clr enabled',1
go
RECONFIGURE 
go

使用以下 SQL 注册您的程序集，如下所示

CREATE ASSEMBLY [hashassembly]
    AUTHORIZATION [dbo]
    FROM 'c:\hashassembly.dll' WITH PERMISSION_SET = SAFE;

使用以下 SQL 在您的数据库中创建 SQL 函数

CREATE FUNCTION [dbo].[ComputeMD5]
(@content VARBINARY (MAX))
RETURNS NVARCHAR (40)
AS
 EXTERNAL NAME [hashassembly].[UserDefinedFunctions].[ComputeMD5]

GO

CREATE FUNCTION [dbo].[ComputeSHA1]
(@content VARBINARY (MAX))
RETURNS NVARCHAR (40)
AS
 EXTERNAL NAME [hashassembly].[UserDefinedFunctions].[ComputeSHA1]

到目前为止，我们已经完成了将您的程序集部署到您的数据库中，您可以使用 SQL 函数来调用它以生成您的哈希值。例如

UPDATE [dbo].[Picture]
   SET [HashKey] = dbo.ComputeSHA1([PictureBinary])

感谢阅读！