为什么在sql server中生成的MD5哈希不相等? [英] Why generated MD5 hash in sql server are not equal?
问题描述
我在SQL Server 2008 R2中有一个包含两个字段(WordHash,Word)的表。这个在C#中生成的 Hash
字段,我需要在sql server中为 Word
字段重新生成哈希码。
但是我的问题是在sql server和C#中生成的MD5哈希是不同的。我发现下面的代码来解决这个问题,但我仍然有同样的问题。
SQL代码:
CONVERT(NVARCHAR(32),HASHBYTES('MD5','some word'),2)
将这个代码块放到我的查询中后,我看到了一些有线结果!这是我的结果:
-
我的查询:
SELECT
[ WordHash],
convert(nvarchar(32),HASHBYTES('MD5','Analytics'),2)AS TestHash,
convert(nvarchar(32),HASHBYTES('MD5',[Word] ),2)AS SqlHash
FROM myTable
结果:
WordHash:A768CAA988605A2846599CF7E2D0C26A
TestHash:A768CAA988605A2846599CF7E2D0C26A
SqlHash F4AFA5FEF805F7F5163EC6402BAF61FF
请注意,'Analytics'
是数据库中记录数据之一。
为什么 TestHash
& SqlHash
在使用相同代码生成时不同!
NVARCHAR
和 VARCHAR
得到散列到不同的值。 HASHBYTES('MD5','Analytics'),
和 [WordHash]
都是 VARCHAR
值,但 [Word]
是 NVARCHAR
。 select HASHBYTES('MD5','Analytics'),'varchar'
union
select HASHBYTES('MD5',N 'Analytics'),'nvarchar'
- 输出
--------------------------- ---------- --------
0xA768CAA988605A2846599CF7E2D0C26A varchar
0xF4AFA5FEF805F7F5163EC6402BAF61FF nvarchar
要解决此问题,您必须将 [Word]
更改为 VARCHAR
或re - 计算 [WordHash]
使用 NVARCHAR
值。
一些有用的进一步阅读:比较SQL Server HASHBYTES函数和.Net哈希值
I have a table in SQL Server 2008 R2 that contain two field (WordHash, Word). This Hash
field generated in C# and I need regenerate hash code for Word
field in sql server.
But my problem is that generated MD5 hash in sql server and C# are different. I found below code to resolve this problem but still I have same problem.
SQL code:
CONVERT(NVARCHAR(32),HASHBYTES('MD5', 'some word'), 2)
After putting this code block to my query, I saw some wired result! This is my result:
My Query:
SELECT
[WordHash],
convert(nvarchar(32),HASHBYTES('MD5', 'Analytics'),2) AS TestHash,
convert(nvarchar(32),HASHBYTES('MD5', [Word]),2) AS SqlHash
FROM myTable
Result:
WordHash: A768CAA988605A2846599CF7E2D0C26A
TestHash: A768CAA988605A2846599CF7E2D0C26A
SqlHash F4AFA5FEF805F7F5163EC6402BAF61FF
Note that the 'Analytics'
is one of records data in database.
Why TestHash
& SqlHash
are different while they generated from same code!?
The issue is NVARCHAR
and VARCHAR
get hashed to different values. Both HASHBYTES('MD5', 'Analytics'),
and [WordHash]
are hashes of VARCHAR
values but [Word]
is a NVARCHAR
.
select HASHBYTES('MD5', 'Analytics'), 'varchar'
union
select HASHBYTES('MD5', N'Analytics'), 'nvarchar'
--outputs
------------------------------------- --------
0xA768CAA988605A2846599CF7E2D0C26A varchar
0xF4AFA5FEF805F7F5163EC6402BAF61FF nvarchar
To fix this you must either change [Word]
to be VARCHAR
or re-compute [WordHash]
using NVARCHAR
values.
Some useful further reading: Comparing SQL Server HASHBYTES function and .Net hashing
这篇关于为什么在sql server中生成的MD5哈希不相等?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!