user authentication by SQL lookup

[Paulo Tetovisk]

Hello,

I have a doubt, maybe related with "Best Pratices" and "How to do a securely
SQL Lookup to authenticate a user against a Database".

It's a simple solution, everybody nows how to do, but what's more secure ?

Send the query or SP with the following statement:
"SELECT COUNT(*) FROM tb_users WHERE uid = 'foo' AND passwd =
'hashedpassword'" and then check if the answer is 0 or 1 ?

Or

Send a query like "SELECT passwd FROM tb_users WHERE uid = 'foo'" and then
you do the password validation on the webserver that host your application ?

The secound solution has a pro: It "saves" SQL processing, but in the
another hand you send your password hash in throught your password, i don't
think that is the most secure solution. Has the first option any con ?

Thanks in advance!

PT

 

[Misbah Arefin]

This is exactly what we do with out MembershipProviders i.e. return just the
count or the PK matching the username and password.

If the password is stored in plain text then it can be checked either in SQL
or .NET code but I would prefer not returning the password from SQL. You are
already checking for username in your WHERE clause why not also check for
password and return just the id or count (integer - 4 bytes) instead of text
(length of password bytes).

If the password is encrypted (and cant be decrypted) then you need to pass
in the encrypted password to your proc and return a value indicating
successful match or not.