Introduction
Some times, 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 it's 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 know the knowledges 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:
Hide Shrink Copy Code
/// <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 enterd 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:
/// <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 enterd 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 pulish 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 you database, you can call the sql function to use it generate your hash value. For example:
UPDATE [dbo].[Picture]
SET [HashKey] = dbo.ComputeSHA1([PictureBinary])
