Password does not match with hash algorithm (SQL Server)

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.

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.

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?

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?