Password Validation

Question

Hi there.

Using mysql and php

Is there any reason / value when checking a password to query the database using the user name and password (after sanitizing, of course) and recording a failed attempt when no rows are returned vs querying the database using the user name and then comparing the return password string?

EDIT: To those who mentioned it below, yes the password is hashed up in the database.

Answer 1

I see 2 questions here.

1. The general way to check any info against database is to make database to do it. So, put both login and password into query. Especially if we store a hash, not a password itself
2. Password guessing protection is valuable addition to the any authorization system.

Answer 2

If I'm understanding you correctly, you're wondering if there's a difference between comparing the results of:

$results = mysql_query("SELECT name FROM users WHERE name = $properlyEscapedName AND pass = $properlyEscapedPassword");
if (mysql_num_rows($result) == 1)
    $authenticated = true;

versus

$results = mysql_query("SELECT name, pass FROM users WHERE name = $properlyEscapedName");
$array = mysql_fetch_assoc($results);
if ($array["pass"] == $unescapedPass)
    $authenticated = true;

I would note that it's better to store password hashes than plaintext passwords for security reasons, but otherwise the only difference I see is that in the first case you will return one less field, which may mean more efficient use of bandwidth between your server and the database. On the other hand, your query is several bytes larger, so that probably mitigates the small benefit of the smaller result set.

Answer 3

There's basically no difference between the two methods. Both methods will let you know if the password doesn't match the name.

Answer 4

The more work you do / the longer you hold open a connection after a failed login attempt, the more you you leave yourself open to a DOS attack. OTOH you need to balance this against preventing brute force searches.

A smarter solution is to push the number of failed attempts client side in an encrypted cookie (drop the cookie before the user gets to the login page - if they try to post a username and password without a cookie - fail it automaticaly - with a message that cookies must be enabled). Increment the failures and re-encrypt to drop a new cookie if the login fails, after 3 consecutive failures fail any attempt.

It doesn't completely prevent brute force attacks but makes them a lot more difficult.

C.

Answer 5

You have a trade-off between information given to the user and protection against attacks.

If you query your database checking for corresponding login and password and it fails, you can only tell the user that either the login or the password is erroneous. Not helping if he does not remember which login you used to register (was it "Arkh01", "arkh" or "Arkh1").

If you query your database for the login, getting the hash and salt you can tell the user if the login is wrong or the password. The user is happy. But someone attacking your website can learn easily that the user "aa" does not exists, but the user "ab" does. As lot of websites give access to a userlist, you can almost always dismiss this concern.

To prevent bruteforce, log the number of attempts made by an IP or on a login and block any further attempt after 5ish errors. Preferably block the IP used, not the account.

About storing hashed passwords, use salts and hmac + sha512 in php : hash_hmac