Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hashbytes function returning unexpected results Expand / Collapse
Author
Message
Posted Tuesday, March 10, 2009 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 AM
Points: 2, Visits: 22
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:

avenue 0x1E0F528E3CD45F3D9BEFBFFAB073FE41 PW4avenue

Any ideas what is going on here?

Post #672538
Posted Tuesday, March 10, 2009 9:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #672548
Posted Tuesday, March 10, 2009 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 11:35 AM
Points: 2, Visits: 22
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.

Post #672568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse