Encrypting passwords using HashBytes('MD5',@PW)

• john.david.birch

  SSC-Addicted

  Points: 446

  More actions

  June 16, 2010 at 5:06 am

  #223900

  We have been using a .NET assembly in our SQL Server 2008 database to perform the MD5Hash of passwords in some circumstances. ( Via a function ) This causes our server crew headaches, because it means that the right versions of .NET Frameworks have to be present in the database every time they do an upgrade or introduce a new server.

  I decided to replace this assembly and function with a simpler one using the T-SQL HASHBYTES('MD5', @PW).

  Initial testing was successfull I used the Wikipedia example:

  MD5("The quick brown fox jumps over the lazy dog") = 9e107d9d372bb6826bd81d3542a419d6

  which gave this same result in both the assembly based function and the HashBytes based function.

  Then I decided to try it on a larger data set. I used the usernames from a usertable as the source of text to be hashed. In "most" cases both functions produced the same result.

  Carefull analysis showed that it failed when the username contained any non-standard ASCII codes. e.g Æ Ø Å Ü Â etc

  Reading the online documentation for CONVERT led me to these two sections of documentation:

  "The binary data types are not for hexadecimal data but rather for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable."

  ... I found this disconcerting, and...

  "Note Because Unicode data always uses an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)"

  Since I dont have access to the source code for the old assembly I cannot determine whether the author ( who has left the scene ) hs made a mistake or whether I need to do some more tricks for my use of HashBytes to return the same result as the assembly.

  Here is my function:

  CREATE FUNCTION [dbo].[fn_Md5Hash_TSQL](@S1 nvarchar(50))

  RETURNS nvarchar(100)

  BEGIN

  declare

  @result varbinary(4000),

  @S2 varchar(100),

  @X nvarchar(100);

  SET @S2 = CONVERT( VARCHAR(100), @S1 );

  SET @result = HASHBYTES('MD5', @S2);

  SET @X = LOWER(CAST('' AS XML).value('xs:hexBinary(sql:variable("@result"))', 'NVARCHAR(100)') )

  RETURN(@X);

  END

  ----------------------------------------------------------

  Testing

  DECLARE @name NVARCHAR(100);

  SET @name = 'AA';

  --SET @name = 'AÅ';

  SELECT 'old', @name, [dbo].[fn_Md5Hash](@name);

  SELECT 'new', @name, [dbo].[fn_Md5Hash_TSQL](@name);

  ---------------------------------------------------------

  results

  oldAA3b98e2dffc6cb06a89dcb0d5c60a0206

  newAA3b98e2dffc6cb06a89dcb0d5c60a0206

  ( Identical )

  ------------------

  And if the 'AÅ' variant is used:

  oldAÅ7645be138eec3e3f19220555fb965501

  newAÅ6a8e7a8270458b4fd99c2d12f4bb74ca

  ( Different )

  -----------------------------------------------

  ( I cannot explain why we have the hashed password as a string and not left as a varbinary, it is just happens to be that way in the database. This is not an issue for the Hashing problem though. )

  Also I got different values for all password if I did not first convert the NVARCHAR parameter to a VARCHAR variable before hashing. This could be an indicator that something is awry in the assembly.

  One solution is to just introduce the new function and wait for people to complain about login problems before clearing each password on an as-needed basis. Of the 40,000 users only a small amount (hopefully !!) will have used one of these characters in the password. However, this system is up 24/7 and the support department is not. In some situations a successfull login might have great importance to the user.

  John