Saturday, October 8, 2011

MS SQL: MD5 Hash

The non-reversable hashing algorithm MD5 is supported in Microsoft SQL Server, but is not directly accessable though a simple transact-SQL function. Normally you would store the MD5 hash value as a hexadecimal string in your database. The HashBytes function returns a binary array of hash data. To convert the binary data to hex we need to use the function fn_varbintohexstr.


To make your code easier to read, I've made a wrapper in a custom userdefined function:

CREATE FUNCTION [dbo].[sudf_Common_Md5Hash]
(
      -- Add the parameters for the function here
      @strValue nvarchar(max)
)
RETURNS nvarchar(32)
AS
BEGIN
      -- Declare the return variable here
      declare @strResult nvarchar(32)

      -- Generate the MD5
      set @strResult = SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @strValue)), 3, 32)
     
      -- Return the result of the function
      RETURN @strResult
END
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

0 kommentarer:

Post a Comment