is it possible to search a value in multiple columns using in clause in mysql?

Question

I wonder if it is possible to search a value in columns using in clause having column names as in elements. for instance :

$username_or_mail = 'value';
select * from users where $username_or_mail in(username,email);

where username and email are column names in table users.
I tried this and seems that it is working but i want to be sure if i'm right.

Answer 1

Would I be right in assuming you're using this for a "Enter your username or e-mail address and password to login" login form?

If so, then your SQL code is correct, but hints at a possible design flaw: what happens if someone has a username that is also the email address of another user? This could be used as a malicious attack (i.e. hijack another user's account by making your username equal to the victim's email address).

There is a solution/workaround: simply check for the '@' character and ensure that email addresses contain @ and similarly ensure that no username contains @ either.

...and if you're going to do that logic, then you might as well optimize the SQL and skip having to check multiple columns (psuedocode):

if( $usernameOrEmail contains '@' ) {
    registerParameter("@email", $usernameOrEmail);
    $sql = "SELECT ... WHERE EmailAddress = @email"; // note that "@email" is the syntax for query parameters in MySQL.
} else {
    registerParameter("@userName", $usernameOrEmail);
    $sql = "SELECT ... WHERE UserName = @userName";

}