Hashbytes function returning unexpected results

• timothy.a.gitchel

  SSC Veteran

  Points: 248

  More actions

  March 10, 2009 at 9:18 am

  #131999

  I have a script that updates a user table and sets the passwords to a hash value using the Hashbytes function:

  update secUser

  set [password] = HashBytes('MD5', 'PW4' + [UserName]),

  HasChangedPassword = 0

  GO

  This is producing unexpected results in that if I run the Hashbyte function for a specific user value, such as:

  select HashBytes('MD5', 'PW4avenue')

  I get a result of '0x8847665608F36E2534A655D3EB57BC0F', but the table updated from the above statement has '0x1E0F528E3CD45F3D9BEFBFFAB073FE41' in it.

  I can't figure out where this value is coming from.

  Running this query:

  select HashBytes('MD5', 'PW4' + [UserName]) as [password],

  'PW4' + [UserName] as test from secUser

  where [UserName] = 'avenue'

  returns these results:

  avenue0x1E0F528E3CD45F3D9BEFBFFAB073FE41PW4avenue

  Any ideas what is going on here?

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  March 10, 2009 at 9:30 am

  #956891

  Hi there,

  I'm not sure how Hasbytes works but I went on a hunch and here is my code

  DECLARE @tbl TABLE

  (UserName1 VARCHAR(100),

  UserName2 NVARCHAR(100))

  INSERT INTO @tbl

  SELECT 'avenue','avenue'

  --VARCHAR RESULTS

  select HashBytes('MD5', 'PW4' + 'avenue') as [password],

  'PW4' + 'avenue' as test

  select HashBytes('MD5', 'PW4' + UserName1) as [password],

  'PW4' + UserName1 as test

  FROM @tbl

  --NVARCHAR RESULTS

  select HashBytes('MD5', 'PW4' + N'avenue') as [password],

  'PW4' + N'avenue' as test

  select HashBytes('MD5', 'PW4' + UserName2) as [password],

  'PW4' + UserName2 as test

  FROM @tbl

  It seems that your table is storing the user name as a NVarchar

  however when you run it normally you using a varchar, so the results are different

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• timothy.a.gitchel

  SSC Veteran

  Points: 248

  More actions

  March 10, 2009 at 9:52 am

  #956911

  Thanks for the assistance. I changed my update statement to the following:

  update secUser

  set [password] = HashBytes('MD5', 'PW4' + cast([UserName] as varchar(100))),

  HasChangedPassword = 0

  GO

  This gives me the original values I'm expecting since the code that checks the values is using a varchar.