密码与哈希算法不匹配 (SQL Server)

发布于 2024-10-16 09:13:54 字数 933 浏览 4 评论 0原文

我遇到了 SQL Server 问题。我的任务是用 PHP 实现一个简单的基于 SQL Server 的用户系统。这些数据库也被 MMORPG 使用，所以我不允许更改数据库结构或哈希算法。我的问题的真正原因是，密码通过以下算法进行哈希处理

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

我创建了一个名为 test123 的测试用户和 pw Hallo123 ，它符合密码哈希值“0x7CEE495091E11FF9560D3D016513332200000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000”

到目前为止一切都应该清楚了。好吧，但是当我尝试通过查询获取数据时，我没有得到任何结果。我运行了这个查询：

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

没有返回结果。但是，如果我直接运行这个查询，

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

我会毫无问题地获取数据，

我现在正在努力解决这个问题大约八个小时，但我无法找出问题所在。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

捂风挽笑 2024-10-23 09:13:54

我认为您需要仔细查看您的数据类型。我的猜测是变量 @ID 和 @PW 是生成哈希的 char 数据类型。当您尝试插入“test123”和“hallo123”的查询时，需要在后面添加空格，以便它们与创建哈希的例程中的任何 char(?) 数据类型匹配。

下面的示例说明了使用 char 和 varchar 数据类型的差异。

使用 char

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

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

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

哈希结果

0x9F1E0132F198216841E2608901D27115

使用 varchar

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

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

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

哈希结果

0x870B01D196916AFA88EBC900BE5395BE

哈希值显然不匹配。

哈希值后面有一堆零的原因是哈希值存储在二进制 (60) 字段中。 HashBytes 返回 VarBinary，但这并不是查询失败的原因。 SQL Server 在比较二进制与 VarBinary 时会进行某种转换。

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.

回复收藏 0 原文

情绪 2024-10-23 09:13:54

您使用的任何语言是否有可能对其进行奇怪的编码？如果您打开 SQL 分析器并查看正在发送的查询以查看是否可以发现差异，该怎么办？

回复收藏 0 原文

幸福％小乖 2024-10-23 09:13:54

当我运行 SELECT HashBytes('MD5', REVERSE(UPPER('test123')) + 'hallo123') 时，我得到结果 0x870B01D196916AFA88EBC900BE5395BE。您确定您的哈希创建正确，并且您在创建测试用户时使用了正确的 ID 和密码吗？

回复收藏 0 原文

~没有更多了~