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
- In Visual Studio 2015, create a SQL Server Database Project.
- Add a file with template SQL CLR C# User Dfined Function.
- Add the SHA1 and MD5 method as below in your .cs file.
The C# method code lines of hash binary data using SHA1:
[SqlFunction(DataAccess = DataAccessKind.None)]
public static String ComputeSHA1(SqlBytes content)
{
String hashSHA1 = String.Empty;
SHA1 calculator = SHA1.Create();
Byte[] buffer = calculator.ComputeHash(content.Stream);
calculator.Clear();
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < buffer.Length; i++)
{
stringBuilder.Append(buffer[i].ToString("x2"));
}
hashSHA1 = stringBuilder.ToString();
return hashSHA1;
}
The C# method code lines of hash binary data using MD5:
[SqlFunction(DataAccess = DataAccessKind.None)]
public static String ComputeMD5(SqlBytes content)
{
String hashMD5 = String.Empty;
MD5 calculator = MD5.Create();
Byte[] buffer = calculator.ComputeHash(content.Stream);
calculator.Clear();
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < buffer.Length; i++)
{
stringBuilder.Append(buffer[i].ToString("x2"));
}
hashMD5 = stringBuilder.ToString();
return hashMD5;
}
- Build your project and you will get a DLL file in bin directory.
- 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!