The password field in my user table (SQL Server 2008) is encrypted using HASHBYTES on insertion. I have a stored procedure with parameters for the username and plain-text password which does a SELECT using that username and the password sent through HASHBYTES, then returns the user record if it finds a match. The SP is always returning an empty recordset, but if I copy and paste just the SELECT from the SP to a new query window, it returns the matched record. There're no other statements in the SP. I'm missing something here; what is it?
views:
275answers:
2
A:
How are you passing in the parameters when calling it from your code? Are there any embedded CR/LF or whitespace?
Joe
2010-01-12 17:44:10
Maybe, but I doubt it. I've tried this both via code (ASP.NET C#) using command parameters, and using the Execute Stored Procedure function in SSMS. Same results for both.
Michael Itzoe
2010-01-12 17:46:59
+1
A:
If the SELECT on its own works, but inside the procedure it does not, the first thing I would look for is parameter types. Hash will vary wildly on similar input, but different types:
select hashbytes('MD5','Lorem Ipsum'), hashbytes('MD5',N'Lorem Ipsum')
Remus Rusanu
2010-01-12 18:26:42
Thanks. The real problem is I was trying to query a record that I had set via an INSERT statement and I hadn't specified unicode. I updated the record and now it works.
Michael Itzoe
2010-01-12 19:13:24
By the way, I used the word "encrypted" out of convenience when I guess I should have used "hashed". Mistake acknowledged.
Michael Itzoe
2010-01-12 19:14:40