Compare One-Way Hash Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Compare One-Way Hash Problems

Author

Topic

jryanmiller
Starting Member

3 Posts

Posted - 2011-06-23 : 16:22:00

I have created a SHA-1 hash to store my passwords. When I try to retrieve user information when performing a login function, it says that the hashes don't match, although I have manually compared the values and they are the same.

Please review the sample below:

Test_Login_Tbl
username varchar(max)
password varchar(max)
salt varchar(10)

Tbl Values
rmiller
0x04EB90388536536CEDE725AA441BB4CBEDEFD163
r7B$2Zp@Wa //randomly generated for each user

Password listed above is using:

Hashbytes('SHA1', 'r7B$2Zp@Wa' + 'password')

Query:


declare @passkey varchar(max)
Select
username = rtrim(username),
[salt] = salt,
passkey = (Hashbytes('SHA1',salt + 'password')),
[password] = rtrim(password)
from
Test_Login_Tbl
where
username='rmiller' and
password = @passkey

Username variable will be passed from asp page. 'password' will actually be a variable passed from asp page.

What is going on?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-23 : 16:44:10

If the salt is part of hashbytes, you must search using same salt.
Above you are searching password against passkey which will never match.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-23 : 16:47:33

[code]DECLARE @Sample TABLE
(
UserName VARCHAR(20) NOT NULL,
PassKey VARBINARY(MAX) NOT NULL
)

DECLARE @Salt VARCHAR(16) = 'r7B$2Zp@Wa',
@Password VARCHAR(16) = 'SqlServerMvp'

INSERT @Sample
VALUES (
'Peso',
HASHBYTES('SHA1', @Salt + @Password)
)

SELECT *
FROM @Sample

-- Wrong
SELECT *
FROM @Sample
WHERE PassKey = @Password

-- Correct
DECLARE @PassKey VARBINARY(MAX) = HASHBYTES('SHA1', @Salt + @Password)

SELECT *
FROM @Sample
WHERE PassKey = @PassKey[/code]

N 56°04'39.26"
E 12°55'05.63"

jryanmiller
Starting Member

3 Posts

Posted - 2011-06-23 : 17:09:49

quote:
Originally posted by Peso

If the salt is part of hashbytes, you must search using same salt.
Above you are searching password against passkey which will never match.

N 56°04'39.26"
E 12°55'05.63"

I am comparing with the same salt. The salt will not be passed from the asp page. Once the salt is generated for the user, it will be stored in the same table, so I don't need to declare that value. I just have to reference it from the table.

The procedure I wrote works IF you take the hash generated by what I defined as passkey and compare it:
password = '0x04EB90388536536CEDE725AA441BB4CBEDEFD163' //value of the passkey Hash

If there were a way for me to insert single quotes around the variable value the function works. For example:

''' + @passkey + ''' or
'char(39)' + @passkey + 'char(39)'

However, it says that varchar and varbinary cannot use the add operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-23 : 17:35:11

You change salt for every user, and you store the salt in the table in clear text?

DECLARE	@Sample TABLE
	(
		UserName VARCHAR(20) NOT NULL,
		Salt VARCHAR(20) NOT NULL,
		PassKey VARBINARY(MAX) NOT NULL
	)

DECLARE	@Salt VARCHAR(16) = 'r7B$2Zp@Wa',
	@Password VARCHAR(16) = 'SqlServerMvp'

INSERT	@Sample
VALUES	(
		'Peso',
		@Salt,
		HASHBYTES('SHA1', @Salt + @Password)
	)

SELECT	*
FROM	@Sample

-- Wrong
SELECT	*
FROM	@Sample
WHERE	PassKey = @Password

-- Correct
SELECT	s.*
FROM	@Sample AS s	-- Just added an table alias for clarification
WHERE	s.PassKey = HASHBYTES('SHA1', s.Salt + @Password)

N 56°04'39.26"
E 12°55'05.63"

jryanmiller
Starting Member

3 Posts

Posted - 2011-06-23 : 21:37:54

quote:

Originally posted by Peso

You change salt for every user, and you store the salt in the table in clear text?

DECLARE	@Sample TABLE
	(
		UserName VARCHAR(20) NOT NULL,
		Salt VARCHAR(20) NOT NULL,
		PassKey VARBINARY(MAX) NOT NULL
	)

DECLARE	@Salt VARCHAR(16) = 'r7B$2Zp@Wa',
	@Password VARCHAR(16) = 'SqlServerMvp'

INSERT	@Sample
VALUES	(
		'Peso',
		@Salt,
		HASHBYTES('SHA1', @Salt + @Password)
	)

SELECT	*
FROM	@Sample

-- Wrong
SELECT	*
FROM	@Sample
WHERE	PassKey = @Password

-- Correct
SELECT	s.*
FROM	@Sample AS s	-- Just added an table alias for clarification
WHERE	s.PassKey = HASHBYTES('SHA1', s.Salt + @Password)

N 56°04'39.26"
E 12°55'05.63"

Yes. I save the salt in clear text for each user.

You solution looks good, but I get the error:

The data types varchar (salt) and varbinary(max) (password) are incompatible in the add operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-24 : 04:12:19

So how are you storing the password into a varbinary?
Should we keep the guessing game for long, or are you going to give us relevant information to help you?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources