Jan
29
2009

SHA256 and higher in SQL Server

As part of a new project I’m doing, I needed to use hashes.

A good source of information on hashes is available from wikipedia.

SQL has the built in function HASHBYTES but this only supports up to the SHA128 algorithm and I want to use SHA256.

I created myself a CLR in C#.net and in some moment of feeling generous, decided to share the code.

This code takes a string and returns the hash as a string however it is a simple process to return a binary instead.

Please comment if you find it useful.

To use it, you’ll need to:

First of all, the (completely undocumented) csharp. There is a link above to download it as a precompiled binary, otherwise fire up Visual Studio and compile the following in a database project.

using System.Collections;
using System.Data.SqlTypes;
using System.Security.Cryptography;
using Microsoft.SqlServer.Server;
using System.Text;
 
 
namespace cryptohashCLR
{
    public sealed class SqlHash
    {
        [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true, Name = "udf_hashBytes", SystemDataAccess = SystemDataAccessKind.None)]
        public static SqlString CLRHash(SqlString hashtype, SqlString input)
        {
            if (input.IsNull || hashtype.IsNull)
            {
                return SqlString.Null;
            }
 
            byte[] plainTextBytes = Encoding.UTF8.GetBytes(input.ToString());
 
            HashAlgorithm oHash;
 
            switch(hashtype.ToString().ToUpper())
            {
                case "MD5":
                    oHash = new MD5CryptoServiceProvider();
                    break;
                case "SHA":
                case "SHA1":
                case "SHA128":
                    oHash = new SHA1Managed();
                    break;
                case "SHA2":
                case "SHA256":
                    oHash = new SHA256Managed();
                    break;
                case "SHA3":
                case "SHA384":
                    oHash = new SHA384Managed();
                    break;
                case "SHA5":
                case "SHA512":
                    oHash = new SHA512Managed();
                    break;
                default:
                    return SqlString.Null;
            }
 
            byte[] hashedBytes = oHash.ComputeHash(plainTextBytes);
 
            string cleanoutput = System.BitConverter.ToString(hashedBytes);
 
            cleanoutput = cleanoutput.Replace("-", "");
 
            return new SqlString(cleanoutput);
 
        }
    }
}

Once you have the dll, here is the SQL to create the assembly. You’ll need to change the path in the script to that of the dll. Once the assembly is created, the dll is no longer required.

/*
--You'll need to run this part if CLR is not enabled on your database
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
*/
 
DECLARE @strDllPath VARCHAR(256)
--Set this path to the path of the dll
SET @strDllPath = 'C:\cryptohashCLR.dll'
 
--Drop the assembly if it already exists
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'cryptohashCLR')
	DROP ASSEMBLY [cryptohashCLR]
--Create the assembly
CREATE ASSEMBLY [cryptohashCLR] FROM @strDllPath WITH PERMISSION_SET = SAFE

And then finally, you an now create the function.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_hashBytes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_hashBytes]
GO
 
CREATE FUNCTION [dbo].[udf_hashBytes]
	(
	@hashtype NVARCHAR(MAX),
	@input NVARCHAR(MAX)
	)
RETURNS NVARCHAR(MAX) AS EXTERNAL NAME cryptohashCLR.[cryptohashCLR.SqlHash].CLRHash
GO
 
--Execute sample cases
IF dbo.udf_hashbytes('SHA1','The quick brown fox jumps over the lazy dog') = '2FD4E1C67A2D28FCED849EE1BB76E7391B93EB12'
	PRINT 'Successful: Output of UDF test was as expected'
ELSE
	PRINT 'Error: Inconsistent results were returned from UDF test'

The CLR supports the following hash types:

  • MD5
  • SHA128
  • SHA256
  • SHA384
  • SHA512

And is as simple as running:

PRINT dbo.udf_hashbytes('SHA256','INPUT STRING')
Written by John in: C# Tips,SQL Tips,Tips |

2 Comments »

  • Neena

    Hi ,

    I am very new to Hshbytes and I now need to generate the SHA256 on a field which I have no clue of …..
    Have generated hashbytes like sha1 or md5 but not sure where to start for the sha256 , I know you have given above the codes but where do i start , i couldnt find database project on my visual studio …please help

    Comment | 15 August 2011
  • Maz

    Just like to say thanks, works great!

    Comment | 14 April 2015

RSS feed for comments on this post. TrackBack URL

Leave a comment