CODEWITHSUNDEEP
asp.netsql-server-2008sha1
jacek_podwysocki
jacek_podwysocki

Reputation: 807

SHA1 password encryption error

I'm trying to perform a login check agains the credentials stored in a database where email is a plain text and password is saved as SHA1. For now, the users have been created manually. To check the login details I decided to use stored procedure. Here's the problem:

The following query returns password in SHA1:

SELECT HASHBYTES('SHA1', 'password') 
 // returns 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

The following query confirms user existence:

SELECT COUNT(*) from users where email='[email protected]'
 and password='0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8' // returns 1

And now here comes the stored procedure (the select statement extracted from the whole procedure where 'password' is a placeholder) which does not seem to return 1 as it should:

SELECT COUNT(*) from users where email='[email protected]'
 and password=(SELECT HASHBYTES('SHA1', 'password')) // returns 0

Does anyone know why?

Upvotes: 1

Views: 440

Answers (1)

jpumford
jpumford

Reputation: 548

SELECT HASHBYTES() returns type varbinary and your password column seems to be a varchar.

Solution as working for OP: 

SELECT COUNT(*) from users where email='[email protected]' and password=(master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', 'password'), 1, 0))

Upvotes: 3

Related Questions