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:
- Compile the dll or download my precompiled one
- Create the assembly in SQL
- Create the user defined scalar function
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') |
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
Just like to say thanks, works great!