Thanks for thinking deep into this matter.
My thinking logic was like this …
The Login form asks for 2 inputs (username or email which are being counted as 1 input) & password (which is being counted as the 2nd input. So, 2 inputs here from the user.
Now, the 2 inputs must match with data in mysql. First input must match with a username in the Username column or it must match with an email from the Email column. When a match is found in either column then that is one row match.
Then when a password is matched in the password column then that is the 2nd row match. So, here 2 rows are being matched. So, I thought. I see my error now. It is not 2 rows match but 2 columns match with 1 row match. Correct ?
I got my wires crossed here. I was looking for 2 column matches but writing the code to search for 2 rows match instead. Anyway, is there a way to get the script to check for 2 column matches ? Care to show an example ? And, would it do the job ? Do you suggest I get the script to do it like that (check for 2 column matches) ?
5 secs later, I’m now thinking it would be a bad idea to get the script search for 2 column matches.
User|Pass
Droopy|your-pass
UI Man|my-pass
If you input the following then it would log you in:
User|Pass
Droopy|my-pass
It would log Droopy in with UI man’s pass. I guess best stick to row matches over column. No wonder programmers never search for column matches.
So, I guess, you now gonna say to get the script to log the user in based on any of these conditions:
Greater Than: 0
Not Equal To: 0
Equal To: 1
Right ?
Maybe, I stick to the 1st one even though all the following are valid:
One programmer said that this code of mine is incorrect unless I want to allow the user to login without entering a password.
$sql = "SELECT * FROM users WHERE usernames='".$member_login_username_or_email."' OR emails='".$member_login_username_or_email."' AND passwords='".$member_login_password."'";
He said to code it like this (note the brackets he added):
$sql = "SELECT * FROM users WHERE (usernames='".$member_login_username."' OR emails='".$member_login_email."') AND passwords='".$member_login_password."'";
He warned me though to use password hashing. Will look into that. Others told me to quit the md5 as it’s rubbish and look into the hashing too.
Brackets are important as they control the precedence of operators to work exactly as you want it to, as @ahundiak mentioned back in post #5 of this thread. Operator precedence is how MySQL decides what order you want things to be evaluated in, and it has its own rules just like Maths, English or anything else. The age-old question that suddenly seems to be popping up on Facebook:
What is 5 x 4 + 6?
The correct answer is of course 26, because of BIDMAS, which means that you do the multiplication first to get 20 + 6, then the addition. Some people calculate this as 50, because they do the addition first to get 5 x 10, but they’re wrong because that does not obey the rules of mathematics. But to avoid any issues, you could write it as
What is (5 x 4) + 6?
and now it’s obvious. MySQL is just the same - it has its own rules about what order multiple operators are evaluated in ( https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html ) and you either obey those rules, or stick a load of brackets in to make absolutely sure. It’s probably good practice to use brackets any time you evaluate multiple conditions, though I’m sure some pride themselves on making sure they just obey the rules instead.
As you can see, the problem with your query without the brackets is that it will first evaluate the AND condition, which will return any rows where the emails field and password field match, then it will evaluate the OR condition, where username matches (but not check the password).
Yes, you want to return the single row from the table where the password matches, and either the username or email matches. If your registration process is working as it must, this will only ever return one row.
Yes, don’t store passwords as plain text, it’s a security risk, they should be hashed and MD5 is old and can be cracked with rainbow tables.
So on sign up you hash the password and store that in the table:-
If the database was hacked and someone saw that, they still don’t know the password.
This is close, but with the hashed password you may do things a little different. Also I would use prepared statments for this too.
$query = "SELECT id, usernames, hash, whateverElse FROM users WHERE usernames = :nameOrEmail OR emails = :nameOrEmail" ;
Note: only selecting columns you need.
You can then use row count to see if there is any result, if there is use password verify to check the hash:-
if (password_verify($passwordInput, $row['hash'])) {
$logged = true ;
$message = 'Welcome back '.htmlspecialchars($row['usernames']) ;
// Set user sessions or whatever you want to do
}
else{
$logged = false ;
$message = 'Your input did not match our records' ;
}
It does not have to be exactly like this, but you get the idea.
Again bear in mind what @droopsnoot says about names and emails matching. It would be a good idea to validate emails and usernames and require them to be unique.
You can validate an email to be an email and make the column unique. I guess you could validate a username to not be an email too.