I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?
Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.
The Syntax of the PWDCOMPARE is very simple
PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)
This function return 1 if plain text and hash value are matched else return o.
For example
Lets suppose we have created a table with 3 columns like userId, username and password
as shown below
DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
Username VARCHAR(100),
EncryptedPassword NVARCHAR(MAX))
Now suppose we have inserted 2 rows in to it wit encrypted password
INSERT INTO @tblLogin VALUES (‘Indiandotnet’,PWDENCRYPT(N’MyPassword’))
INSERT INTO @tblLogin VALUES (‘SQLRaaga’, PWDENCRYPT(N’Test’))
Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this
I have to write following query
SELECT * FROM @tblLogin WHERE PWDCOMPARE(N’Test’,EncryptedPassword) = 1
For detail take a look of below snap
I hope you understand with above provided example.
Enjoy !!!
RJ!!
