Password does not match with hash algorithm (SQL Server)

Question

I got a problem with SQL Server. My Task is it to realize a simple SQL Serverbased usersystem with PHP. The databases are also used by a MMORPG so I am not allowed to change the database structure or hashing alogrithms. The real cause of my problem is, that the password is hashed by the following alogrithm

HashBytes('MD5', REVERSE(UPPER(@ID)) + @PW)

I created a test user called test123 and pw hallo123 what conforms the password hash "0x7CEE495091E11FF9560D3D01651333220000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"

up to this point everything should be clear. Well but when I try to fetch the data by a query I get no results. I ran this query:

mssql_query("SELECT * FROM [DB_Member].[dbo].[Member] WHERE ID = 'test123' AND PW = HashBytes('MD5', REVERSE(UPPER('test123')) + 'hallo123')");

Which returns no result. But if I directly run this query

mssql_query("SELECT * FROM [DB_Member].[dbo].[Member] WHERE ID = 'test123' AND PW = 0x7CEE495091E11FF9560D3D01651333220000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);

I get the data without any problems

I'm grappling with this now for about eight hours but I can't figure out the problem.

Mikael Eriksson · Accepted Answer · 2011-02-05 22:37:07Z

1

I think you need to have a closer look at your data types. My guess is that the variables @ID and @PW is a char data type where the hash is generated. When you try the query, inserting 'test123' and 'hallo123', you need to add spaces after so they match whatever char(?) data type they were in the routine that created the hash.

Here is an example illustrating the difference in using char and varchar data types.

Using char

declare @PW char(25)
declare @ID char(25)

set @PW = 'hallo123'
set @ID = 'test123'

select (HashBytes('MD5', REVERSE(UPPER(@ID)) + @PW))

Hash result

0x9F1E0132F198216841E2608901D27115

Using varchar

declare @PW varchar(25)
declare @ID varchar(25)

set @PW = 'hallo123'
set @ID = 'test123'

select (HashBytes('MD5', REVERSE(UPPER(@ID)) + @PW))

Hash result

0x870B01D196916AFA88EBC900BE5395BE

The hashes clearly does not match.

The reason you have a bunch of zeros after your hash is because the hash is stored in a binary(60) field. HashBytes returns a VarBinary but that is not the reason for the query to fail. SQL Server does some kind of conversion when comparing binary with VarBinary.

answered Feb 5, 2011 at 22:37

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

Great Answer, thank you! Is there a way to take a look at the query exactely the way theserver recieves it? Then I could see which hash is being generated. Thanks

Mikael Eriksson Over a year ago

@Thomas - You can use SQL Server Profiler to look at what is sent to the server when you set the password. Setup a trace and start it just before you change the password and then turn it off as soon as possible after. Then you only need to read through the log and look for the statement that changes the password and look at what the parameters contain. The example I gave was that they where padded with spaces. They could very well be padded with something else.

Ben Thul · Accepted Answer · 2011-02-05 14:20:03Z

0

Is it possible that whatever language you're using is encoding it strangely? What if you pull up a SQL profiler and look at the query that's being sent to see if you can spot the differences?

answered Feb 5, 2011 at 14:20

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Comments

outis · Accepted Answer · 2011-02-05 14:20:22Z

0

When I run SELECT HashBytes('MD5', REVERSE(UPPER('test123')) + 'hallo123'), I get the result 0x870B01D196916AFA88EBC900BE5395BE. Are you sure your hash creation is correct, and that you used the correct ID and password when creating the test user?

answered Feb 5, 2011 at 14:20

outis

77.7k23 gold badges155 silver badges228 bronze badges

Collectives™ on Stack Overflow

Password does not match with hash algorithm (SQL Server)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related