Authentication MySQL vs PHP?

Question

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.

You are wasting processing power making all of those checks IMO. Option 1 makes the most sense as you have already verified them by performing the query. — Kevin Peno, Mar 10 '11 at 17:54

score 3 · Answer 1 · answered Mar 10 '11 at 17:55

3

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

answered Mar 10 '11 at 17:55

Marc B

356,200
43
426
500

1

Well usernames are always unique so you would receive only one result :) – Val Mar 10 '11 at 17:57
2

I made the same mistake, that's not actually the case due to `WHERE username = $user` – fredley Mar 10 '11 at 17:58
1

That's fine if he sticks with plain text passwords but if he salts them like he should be he's going to have to go with option 2. – Spencer Ruport Mar 10 '11 at 17:59
@Val: True enough. But in general, if you can do the matching on the database, then do it on the database. Particularly with `select *`. There's no point in retrieving all the fields if you're merely interested if the record exists. – Marc B Mar 10 '11 at 18:01
@marc `*` was used to give the idea of the php vs mysql quick and simple to understand :) – Val Mar 10 '11 at 18:16

score 3 · Answer 2 · answered Mar 10 '11 at 17:59

3

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.

answered Mar 10 '11 at 17:59

theomega

31,591
21
89
127

1

The first constructive answer. – Ivan Mar 10 '11 at 18:00
5

User not found/Password wrong is just making hackers attempt of breaking into account easier. – Dejan Marjanović Mar 10 '11 at 18:02
@webarto thats true you tell them username is right all u have to do is find the right password :) – Val Mar 10 '11 at 18:09
@webarto - Not as bad as storing the user's password directly in a cookie. (Looking at your answer below) – Spencer Ruport Mar 10 '11 at 20:05

score 3 · Accepted Answer · edited May 23 '17 at 12:04

3

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.

edited May 23 '17 at 12:04

Community

1
1

answered Mar 10 '11 at 18:06

Alexey Lebedev

11,988
4
39
47

I think I will choose your answer as I really like the frame work, however didn't really like the BitTorrent explaination :) but thnx – Val Mar 11 '11 at 11:43
I removed that BitTorrent reference, and, I hope, made my explanation a bit clearer. – Alexey Lebedev Mar 11 '11 at 12:27

score 1 · Answer 4 · answered Mar 10 '11 at 17:53

1

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.

answered Mar 10 '11 at 17:53

fredley

32,953
42
145
236

"huge amount of traffic"? Dunno about that. – Spencer Ruport Mar 10 '11 at 17:55
He's making a query to it anyway. How does either of these cases reduce SQL traffic? – Kevin Peno Mar 10 '11 at 17:55
3

If username is unique i can't see that there is any difference in amount of traffic. Both queries return one record. – Ivan Mar 10 '11 at 17:55
huge amounts more than you need. Imagine if Facebook's authentication worked like that. – fredley Mar 10 '11 at 17:55
@kevin: consider a system like facebook with millions of users. Would you rather have the database do the filtering, or suck out however many gigabytes of data are in the user table and filter it in your client app. – Marc B Mar 10 '11 at 17:56
He has to make the query regardless and needs the user data. You really think that adding a couple of bytes to the SQL request is "huge amounts of traffic"? – Kevin Peno Mar 10 '11 at 17:57
fredley, salts are based on the user. Where's that salt coming from? He's going to have to return the recordset anyway. – Spencer Ruport Mar 10 '11 at 17:58
@Marc B: Passwords are about 8 characters long. In order for this to be returning one gigabyte of data it would have to be executed 134,217,728 times. – Spencer Ruport Mar 10 '11 at 18:02
@Kevin: he's doing `select *` - if that table contains a BLOB field, then yes... huge traffic. – Marc B Mar 10 '11 at 18:02
@ivan thank you :) couple people keep saying that and I was like how lol – Val Mar 10 '11 at 18:02
@marc like I said it only was for demonstrations I always always select only the fields i need to print out and I always use md5-(salted) – Val Mar 10 '11 at 18:04

score 1 · Answer 5 · answered Mar 10 '11 at 17:57

1

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.)

answered Mar 10 '11 at 17:57

Spencer Ruport

34,865
12
85
147

Yep I use salts very effective :) It got me the creeps the hash table lookup thingy since then I have used salts – Val Mar 10 '11 at 17:59
Where are you salting the password? Salts should be unique to each user. – Spencer Ruport Mar 10 '11 at 18:03
I use a php var as a salt, and maybe their email, or username and password something like `$salt = '[secret]'.$_POST['username'].$_POST['password']` not on the same order but ye :) sometimes i include the date of registration lol if on db that is – Val Mar 10 '11 at 18:06
Hm, well I've never seen someone do it that way. Not sure if it's a good idea or not. – Spencer Ruport Mar 10 '11 at 18:11
forgot to md5 it all :) lol and thats what i use to store on db, on a cookie i md5 their username so hackers can think that could be a password or somin and give them random cookie name :) like password where infact its a username md5-ed :) or timestamp of date of registration or whatever i need :) – Val Mar 10 '11 at 18:15

score 1 · Answer 6 · answered Mar 10 '11 at 18:18

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.

score 0 · Answer 7 · answered Mar 10 '11 at 17:56

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.

score -1 · Answer 8 · answered Mar 10 '11 at 18:00

-1

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");

answered Mar 10 '11 at 18:00

Dejan Marjanović

19,244
7
52
66

3

You'd store the password in the cookie? Bad idea. – Spencer Ruport Mar 10 '11 at 18:06
@spencer might aswell print out on the source code lol on a hidden input if ur using cookies like that :) – Val Mar 10 '11 at 18:11
Even if someone somehow hijacks cookie, simple IP restriction will prevent him to be logged in automatically, so it is not that bad :) MyBB forum uses exactly the same id_password cookie... – Dejan Marjanović Mar 10 '11 at 18:21

Authentication MySQL vs PHP?

8 Answers8