Search code examples
phpsql-serverpasswordspassword-hash

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.

Solution

  • 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.