如何获得与以前的php md5哈希匹配的SQL Server md5哈希? [英] How can I get a SQL Server md5 hash to match a previous php md5 hash?

问题描述

我有一个SQL Server 2014表，其中填充了由php使用php $hash = md5($password);命令生成的MD5哈希.现在我们正在转向更严格的安全模型，我希望能够在存储过程中输入密码并将其与先前存储的md5哈希值匹配.

I have a SQL Server 2014 table filled with MD5 hashses generated by php using the php $hash = md5($password); command. Now that we are moving to a tighter security model, I would like to be able to within a Stored Procedure take a password and match it to the previously stored md5 hash.

当SQL返回的md5哈希值与php返回的md5哈希值不同时，就会出现问题.

Problem comes when the md5 hash returned by SQL is different than the one returned by php.

当我使用

SELECT username, 
    [password],  
    master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', CONVERT(VARCHAR(32), [password])), 1, 0) AS Test, 
    HASHBYTES('MD5', [password]) AS MD5 
FROM myTable 
WHERE username=@username

我得到:

Password = 28d744960521b00fd7c4a9e7e7d4d3a3, 
Test = 172b078903f8f16098fea3df31ee8989, and 
MD5 = 0x172B078903F8F16098FEA3DF31EE8989

MD5表达式是正常的tsql，而我上线了Test表达式，但是正如您所看到的，它们的结果都不等于我最初从php的md5()表达式中存储的结果.

The MD5 expression is normal tsql and the Test expression I got online, but as you can see, neither of their results equal what I originally stored from php's md5() expression.

任何想法为什么这些都不相同，更好的是，有没有一种方法可以格式化tsql命令以使我与存储的php md5值匹配?

Any idea why these are different, and better yet, is there a way I can format the tsql command to give me a match on the stored php md5 value?

推荐答案

我很抱歉！我正在将值[password]发送到sql哈希序列，而不是输入的变量@password！因此我在Test和MD5中收到的是哈希值的哈希值，而不是原始密码的哈希值.

My apologies! I was sending the value [password] to the sql hash sequence not the inputted variable @password! So I was receiving in Test and MD5 a hash of a hash, not the hash of the original password.

有趣的是，需要花式master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', CONVERT(VARCHAR(32), [password])), 1, 0)来删除前导0x以获得正确的匹配.

Interestingly enough, the fancy master.sys.fn_varbintohexsubstring(0, HASHBYTES('MD5', CONVERT(VARCHAR(32), [password])), 1, 0) is needed to remove the leading 0x to get the right match.

这篇关于如何获得与以前的php md5哈希匹配的SQL Server md5哈希?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！