Hashed value in DB not matching hashed value of user login

Hello I have converted user passwords to a hash value using SQL hashbytes. When I go to test the login, the value of both the hashed value in the database and the hashed user input value do not match. I am using a stored proc on the login and here is the SQL: SELECT @intMemberID=ISNULL((SELECT intMemberID FROM tblMembers WHERE strUsernameII=@strUsername AND strPasswordII= HashBytes('MD5',Convert(nvarchar,'@strPassword'))),0) The column in a varbinary(max) column. Any help would be greatly appreciated. Thanks

The hashed value of a string is ALWAYS different each time you hash it. The system clock is often used as part of the seed for the hashed value. In other words....one-way. What you want is encryption/decryption. I've often used the cf_crypt custom tag (adjusted to use AES instead of the default CF uses). HTH Cheers On Wed, 2010-07-21 at 14:03 -0400, Torrent Girl wrote: ----- Excess quoted text cut - see Original Post for more -----

I'm storing hashed passwords, and when you rehash the same value, you should get the same string. That's how you can check the validity of the password. UUID changes over time, hash does not as long as you are using the same algorithm. I've noticed occasional quirkyness storing these values, however, so if someone has some insight... The hashed value of a string is ALWAYS different each time you hash it. The system clock is often used as part of the seed for the hashed value. In other words....one-way. What you want is encryption/decryption. I've often used the cf_crypt custom tag (adjusted to use AES instead of the default CF uses). HTH Cheers On Wed, 2010-07-21 at 14:03 -0400, Torrent Girl wrote: ----- Excess quoted text cut - see Original Post for more -----

You are correct if you are hashing a Password+Random_Seed value, but usually this is not the case. The hashed value of the same string is ALWAYS THE SAME.  That's why password hashes don't really help because of rainbow tables, which lists common passwords and their hashes. A prime example of an MD5 hash is "5F4DCC3B5AA765D61D8327DEB882CF99" which is the MD5 hash for "password" If you see this in a hash column, then that user's password is "password".  This is always the MD5 hash of "password", unless you concatenate a seed value to it. That is why when you store a hash, you should also store what's called a SALT value, which essentially should be concatenated to the password when the hash is made.  Here is a more thorough explanation: http://www.mail-archive.com/openbd@googlegroups.com/msg04775.html Going back to the Torrent Girl's question, this SQL code worked for me (it should output the second row, the row with 'password11'): CREATE TABLE #xTable (     ID INT IDENTITY(1,1) PRIMARY KEY,     strPasswordII VARBINARY(MAX) ) INSERT INTO #xTable (strPasswordII) SELECT HASHBYTES('MD5',CONVERT(nvarchar,'password00')) INSERT INTO #xTable (strPasswordII) SELECT HASHBYTES('MD5',CONVERT(nvarchar,'password11')) INSERT INTO #xTable (strPasswordII) SELECT HASHBYTES('MD5',CONVERT(nvarchar,'password22')) SELECT * FROM #xTable WHERE strPasswordII = HASHBYTES('MD5',CONVERT(nvarchar,'password11')) DROP TABLE #xTable Bryan Stevenson wrote: > The hashed value of a string is ALWAYS different each time you hash it. > The system clock is often used as part of the seed for the hashed value.

Yep...I sure did get that backwards didn't I ;-) Sorry TorrentGirl!! Hash is on-way (can't un-hash)....but the hashed value of a string remains the same. It's encryption where the value will change each time....but you can always decrypt to find the original string (useful for password retrieval). Good call on the rainbow tables Rex....important point! Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com Notice: This message, including any attachments, is confidential and may contain information that is privileged or exempt from disclosure. It is intended only for the person to whom it is addressed unless expressly authorized otherwise by the sender. If you are not an authorized recipient, please notify the sender immediately and permanently destroy all copies of this message and attachments. Please consider the environment before printing this e-mail

Worked for me: CREATE TABLE #xTable (     ID INT IDENTITY(1,1) PRIMARY KEY,     strPasswordII VARBINARY(MAX) ) INSERT INTO #xTable (strPasswordII) SELECT HASHBYTES('MD5',CONVERT(nvarchar,'password')) SELECT ID FROM #xTable WHERE strPasswordII = HASHBYTES('MD5',CONVERT(nvarchar,'password')) DROP TABLE #xTable Torrent Girl wrote: ----- Excess quoted text cut - see Original Post for more -----

The problem was with the quotes around @strPassword. When I remove them it works (????) This doesn't work: HashBytes('MD5',Convert(nvarchar,'@strPassword'))),0) This works: HashBytes('MD5',Convert(nvarchar,@strPassword))),0)