CODEWITHSUNDEEP
phpmysqlauthentication
Val
Val

Reputation: 17532

Authentication MySQL vs PHP?

This is more of a question to deter which option you guys think its better pro/cons.

Which authentication process is better ?

Option 1

$sql = "SELECT * FROM users WHERE username = $user  AND password = $password";
if($result){// success }

Option 2 

$sql = 'SELECT * FROM USER WHERE username = $user';
///query goes here and returns to $result 
if(count($result)==1 && $_POST['passwod'] == $result['password']){
    /// success
}

I have always used the second option but wanna know someone else's opinion.

The code might be a bit messy and vulnerable to hackers but its only for demonstration purposes.

Upvotes: 0

Views: 520

Answers (8)

Alexey Lebedev
Alexey Lebedev

Reputation: 12197

Both options 1 and 2 imply storing passwords as plain text, which is dangerous. If your database gets stolen (happened to Gawker Media, happened to Reddit, happened to my company) the hacker will not only get access to the accounts on your site, but to many of users' e-mail accounts too, because people usually use the single password for all their accounts.

Option 3. Hash your passwords with a cryptographic hash function (hint: not MD5) and a random salt. See this question for a PHP solution that is used in WordPress and Drupal. As you cannot validate salted password without retrieving the salt from the database first, this solution is closer to your second option.

Upvotes: 3

Terseus
Terseus

Reputation: 2212

Unless I'm mistaken, if in the table users the field username is the primary key there will be an index in that field (clustered or not), so querying by the fields username and password will waste the advantage of the index and do a table scan; ITOH querying only by the index username will do a index seek, much more quick.

Of course you can also make another index in (username, password) but it's not necessary and IMHO a waste of space since it will only be used in the authentication.

So, if I'm right, I hate to contradict everyone but for me the most optimized way it's the option 2.

And as the people said, please don't store the passwords in plain text, use a hash algorithm and save the hash, not the password.

PD.: Sorry for my poor english people.

EDIT: I don't have a great knowledge of the DBMS internals, so if I'm wrong about the importance of the query fields to use or not the index, please correct me.

Upvotes: 1

Dejan Marjanović
Dejan Marjanović

Reputation: 19380

I do it this way...

$username = preg_replace("#[^a-zA-Z0-9]#", "", $_POST["username"]);
$password = preg_replace("#[^a-zA-Z0-9]#", "", $_POST["password"]);
$password = sha1($password); // or md5
$sql = mysql_query("SELECT id,password FROM users WHERE username = '$username' AND password = '$password' LIMIT 1");

if(mysql_num_rows($sql) > 0){
    $row = mysql_fetch_assoc($sql);
    setcookie("example", $row["id"]."_".$row["password"], 0, "/", ".example.com");
}
header("Location: http://example.com");

Upvotes: -1

theomega
theomega

Reputation: 32051

I competly agree with the other posters, but there are two points for the second way:

  • The password doesn't apear in the query-log (and the slow-query-low perhaps), which might be enabled on some servers.
  • You can output two different error-messages, one "User not found" and the other "Password wrong", if you want to.

Upvotes: 3

Spencer Ruport
Spencer Ruport

Reputation: 35117

The second, and not because I think it's necessarily more efficient or whatever. You should add a salt field to your table, hash the password submitted, add the salt from the returned record set and salt it again. Then you should compare it to the password field.

(Obviously the data in the password field must go through the same process when it's stored in the first place. And you'll have to come up with a good way to generate salts.)

Upvotes: 1

Bleaourgh
Bleaourgh

Reputation: 1326

1) Don't forget to use mysql_real_escape_string($_POST['password']) (as well as on $_POST['user']), otherwise you'll encounter glaring security holes.

2) The former is much better. Less traffic to the server and you never pull the password out of the database (which may be an issue if, say, someone was sitting at your server debugging the PHP script who wasn't supposed to have access to the DB).

3) If you're storing passwords in the database, consider using some sort of hashing algorithm as well.

Upvotes: 0

Marc B
Marc B

Reputation: 360762

Which do you prefer?

Option 1: Going to the store and looking at the various brands of product X and pick out one there.

Option 2: Buying all of the bottles of product X, drag them home, pick out the one you want, and throw away the rest.

Personally, I'd rather save the time and money by going with Option #1

Upvotes: 3

fredley
fredley

Reputation: 33921

The former, with "s around $user and $password. The latter creates a huge amount of traffic between PHP and MySQL - my bad, read the query wrong, thought you were getting all records. Also, I really hope your database is set up so that that call is in effect something along the lines of:

SELECT * FROM users WHERE username = '$user'  AND password = '".sha1($password.$salt)."'

Storing plaintext passwords is bad.

Upvotes: 1

Related Questions