different outputs using hashbytes

Question

I have a simple table for testing HASH PASSWORDS
i have 2 columns PWD & HASHED_PWD. let's say in PWD i have password '111' in HASHED_PWD I used this query to add hashed password '111':

declare @hashThis nvarchar(150);
select @hashThis = CONVERT(nvarchar(150),(SELECT PWD FROM Customers where CustomerID='cust-111'));
update Customers set HASHED_PWD=HASHBYTES('md5',@hashThis) where CustomerID='cust-111';

the result is this:

칮핏섛鐓鈦絣䮝ຆ

but when i use the variable to hash '111':

declare @hashThis nvarchar(150);
select @hashThis = CONVERT(nvarchar(150),(SELECT PWD FROM Customers where CustomerID='cust-111'));
--update Customers set HASHED_PWD=HASHBYTES('md5',@hashThis) where CustomerID='cust-111';
select HASHBYTES('md5',@hashThis) as HASHES;

the same query but the result is different. my variable @hashThis is:

0x6ECE4FD51BC113942692637D9D4B860E

my column data type and variable are the same nvarchar(150)

why i have different results?
any ideas?
I used this query to compare and the result is always not correct

declare @pwd nvarchar(150);
declare @pwd2 nvarchar(150);
set @pwd=HASHBYTES('md5','111');
set @pwd2=(select HASHED_PWD from Customers where CustomerID='cust-111')
if @pwd=@pwd2
print 'Correct'
else
print 'not correct'

What is the type of `HASHED_PWD`? This is possibly just a representation issue. — Gordon Linoff, May 02 '14 at 13:51
@GordonLinoff, no it's not a representation issue. i compared both in simple query and the result is always different! — Sam, May 02 '14 at 13:56

score 0 · Answer 1 · answered May 02 '14 at 18:32

I found the issue:
I have to use VARBINARY data type for PWD and HASHED_PWD columns.
and for my variable @hashThis as well
This is what I did. I converted data type for both columns:

alter table Customers alter column PWD VARBinary(150)
alter table Customers alter column HashED_PWD VARBinary(150)

Then I used a variable to use the same password for test like below:

DECLARE @hashThis nvarchar(50);
select @hashThis ='111';
select @hashThis= CONVERT (varbinary(150),@hashThis);
set @hashThis= HASHBYTES('md5',@hashThis);
if @hashThis=(select HASHED_PWD from Customers WHERE CustomerID='cust-111')
print 'Correct'
else
print 'not Correct'

and the result is Correct.

different outputs using hashbytes

1 Answers1