1

I have a table named "users" in MySQL with these data(data types):
id(10 digit INT), email(char-32), phone(13 digit INT), password(char-64)
I need to build a login page that accepts id/email/phone as username. Then it should search the table for any match and then check the password.
My query for getting data is this:

SELECT id, email, phone, password FROM users WHERE username IN (id, email, phone);

The problem is that when I try to search for email, it returns correct AND NULL records.
What am I doing wrong?
Sorry for my poor English.

edit:
I can check for username type in php then execute a type-specific query. But is there a way to use a single query to do it?

Improve this question
4
  • 32 letters for an email address is really, really tiny. Use VARCHAR(255) as a default "string" value and don't trim that or extended it unless you have a compelling reason to do so. Commented Sep 12, 2020 at 0:45
  • WARNING: Writing an access control layer is not easy and there are many opportunities to get it severely wrong. Any modern development framework like Laravel comes with an authentication system built-in, and there are authentication libraries you can use. At the absolute least follow recommended security best practices and never store passwords as plain-text or a weak hash like SHA1 or MD5. Commented Sep 12, 2020 at 0:46
  • Thanks for recommendations sir. 255 letters isn't toooo long for an email? About the warning, I am a beginner in php and I'm trying to understand and learn. So I'm doing this from scratch to understand php better. By the way, I stored my passwords in MD5 hash. Commented Sep 12, 2020 at 2:56
  • 255 is a safe default. Even though technically email addresses can be longer, in practice this is unlikely. As for MD5, it is completely unsuitable for storing passwords because it can be trivially cracked. At the absolute minimum use a password-specific hash like Bcrypt. Commented Sep 12, 2020 at 22:26

2 Answers 2

Reset to default
1

Presuming these values are all unique then:

SELECT id, email, phone, password FROM users WHERE ? IN (id, email, phone) LIMIT 1

Where ? is a placeholder value you bind the given user input to.

Note: In practice you really don't want to do this, instead have the input type examined and typed according to patterns, such as through a regular expression, and then run a query appropriate for that type. Doing it as "one query" actually exposes you to collision problems where someone puts in a phone number identical to someone else's ID just to screw with them.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I can't give you an example because I'm obviously a beginner. But limiting the results of an authentication query sounds like a security flaw. All these values are unique but in my case, using an email as a username, returned correct record as well as ALL null-email records. So I think limiting the query may end up to a successful login to another account.
LIMIT 1 isn't a security flaw, it's asking for one row, and with the right uniqueness checks there should only be one matching row. The only way this could fail and let you into the wrong account is if your password verification code is completely broken. Since you've admitted to using MD5 you already have huge problems to fix. This is why I strongly recommend never rolling your own security and always using a tested, community-supported system. If a security problem is uncovered in that there will be a notification sent out, and you can patch.
1

If I understand your question that'username' is a parameter from login page, try this query

SELECT id, email, phone, password FROM users WHERE concat(id, email, phone)= username;
Improve this answer

3 Comments

I'm not sure smashing them all together with concat is what's desired here.
id, email and phone may or may not be present in the database. But if they do, why should I concatenate them? Could you explain it please.
id, email and phone are unique. It sounds wired but maaay be someone's id is identical to someone else's phone. Comparing all those fields together with username is not a security risk?!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.