HashBytes function in SQL Server (wrong MD5 result)
hash, md5, sql-server
Solution
You cannot hash text, you can only hash bytes. `HashBytes` converts the input string to bytes first. The conversion depends on whether the data type is a Unicode type. I guess that `Password` is `nvarchar` and I see that your literals are `varchar`.
Decide which one you want and use it consistently.
Problem
I faced on a strange problem with a SQL Server function `HashBytes`. I have a column `Password` in my `Users` table (`nvarchar(32), not null`). The user's password is `123456` which is `e10adc3949ba59abbe56e057f20f883e` in MD5 and this value is stored in the column. I am trying to get MD5 hash function of `e10adc3949ba59abbe56e057f20f883e` which is actually `14e1b600b1fd579f47433b88e8d85291` (you may check it here), however in my SQL Server query, I get a totally another result: ``` SELECT TOP 1 Password, lower(convert(nvarchar(32), HashBytes('MD5', '123456'), 2)) AS md5pass1, lower(convert(nvarchar(32), HashBytes('MD5', 'e10adc3949ba59abbe56e057f20f883e'), 2)) AS md5pass2, lower(convert(nvarchar(32), HashBytes('MD5', Password), 2)) AS md5pass3 FROM Users ``` Result is: and I expect to get '14e1b600b1fd579f47433b88e8d85291' as a result... I appreciate you helping me!