How to Hash Your Binary Data that Exceed the Limit (8000bytes) of Input Parameter of HASHBYTES in SQL Server

Introduction

Sometimes, we store binary data in database and need to hash them in order to get an identifier of the binary data. We know the SQL Server has a built-in function to do that, it's HASHBYTES. But its allowed input values are limited to 8000 bytes. Then how to hash the binary data that exceed 8000 bytes? We know SQL Server can be extended with CLR. Here, there are mainly two hash algorithms: MD5 and SHA1. Now let us get started.

Background

You should have knowledge on C#, SQL, SHA1, MD5.

Using the Code

1. In Visual Studio 2015, create a SQL Server Database Project.

   

2. Add a file with template SQL CLR C# User Dfined Function.

   

3. Add the SHA1 and MD5 method as below in your .cs file.

   The C# method code lines of hash binary data using 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;
   }

   The C# method code lines of hash binary data using 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. Build your project and you will get a DLL file in bin directory.
5. Publish your assembly file into your SQL Server database. There are two ways to do this:
   • Use the publish tool provided by Visual Studio. You can generate script file and use the script file to publish or directly publish it into your database.

     

   • Manually register assembly into your database using Transact-SQL.

First, you should ensure to enable CLR in your database. If not, execute the following SQL:

EXEC sp_configure 'clr enabled',1
go
RECONFIGURE 
go

Use this SQL to register your assembly as below:

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

Create SQL functions in your database using the following 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]

So far, we have finished deploying your assembly into your database, you can call the SQL function to use it to generate your hash value. For example:

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

Thanks for reading!