MySQL conditional check

Question

How can I structure this query validation so that it validates the email and password independently. So if the user enters a correct email, the location(see code) will be different than if the user entered a correct password but wrong email? Here's the query, and if statement that set's the GET error variable:

$mysql = mysql_query("SELECT * FROM employees WHERE email = '{$email}' AND hashed_password = '{$hashed_password} '");

if ( mysql_num_rows($mysql) < 1 )
{
    header ("Location: index.php?error=1");
    die();    
}

Are email and hashed_password each unique?

Tony Hopkinson
– Tony Hopkinson

2013-01-23 22:47:43 +00:00
Commented Jan 23, 2013 at 22:47 — Tony Hopkinson
– Tony Hopkinson, Commented Jan 23, 2013 at 22:47

Bill Karwin · Accepted Answer · 2013-01-23 22:48:18Z

1

SELECT hashed_password = '{$hashed_password}' AS is_password_correct 
FROM employees WHERE email = '{$email}'

This query will return zero rows if there's no such entry matching $email.

If there is a row matching $email, the query will return either 1 or 0, based on whether the boolean equality comparison in the select-list is true or false.

You can then do different things in your app depending on the three possible states.

You should stop using the deprecated "mysql" functions in PHP, they are going away in the next version of PHP. Use mysqli or PDO.

Also you should learn to use prepared queries and pass your $hashed_password and $email as query parameters. Then you can avoid SQL injection vulnerabilities and not worry about escaping strings.

Here's a complete example (untested) with PDO:

$stmt = $pdo->prepare("SELECT hashed_password = :password AS is_password_correct 
    FROM employees WHERE email = :email");
if ($stmt === false) {
    // always check for errors
}
$result = $stmt->execute(array(":password"=>$hashed_password, ":email"=>$email));
if ($result === false) {
    // always check for errors
}
if ($stmt->rowCount() == 0) {
    // no such user
} else {
    while ($row = $stmt->fetch()) {
        if ($row["is_password_correct"] == 1) {
            // password is correct for the given user
        } else {
            // password is wrong
        }
    }
}

answered Jan 23, 2013 at 22:48

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

5 Comments

user1315367 Over a year ago

ok @bill - where do i add the else statment { $_SESSION['loggedin'] = "YES"; // Set it so the user is logged in! $_SESSION['email'] = $email; // Make it so the username can be called by $_SESSION['name'] $_SESSION['password'] = $hashed_password; // Make it so the username can be called by $_SESSION['name'] header ("Location: employee_profile.php"); // Kill the script here so it doesn't show the login form after you are logged in! }

Bill Karwin Over a year ago

Where I added the comment // password is correct for the given user, there you know the email and password is authenticated. You can do what you need to record the fact that they're logged in.

user1315367 Over a year ago

Confused - I don't think I'm setting this up right. I completely removed the old mysql_query and replaced it with yours. the page loads but when the form is submitted I get a configuration error.

user1315367 Over a year ago

do i have to change the connection details? I've never used PDO and trying to learn it now. Any help would be appreciated. Thank you.

user1315367 Over a year ago

Thanks @Bill - was able to piece it together and get it working. 'til next time.

Ricardo Souza · Accepted Answer · 2013-01-23 22:44:09Z

0

A simple way of doing this it to use an OR clause, instead of AND and do a comparison in the PHP side:

$mysql = mysql_query("SELECT email, hashed_password FROM employees WHERE email = '{$email}' OR hashed_password = '{$hashed_password} '");

if ( mysql_num_rows($mysql) < 1 )
{
    // e-mail and password not found
    header ("Location: index.php?error=1");
    die();    
}
else
{
    $rows = mysql_fetch_assoc($mysql);

    if ($rows["email"] != $email)
    {
        // e-mail not found
    }
    else if ($rows["hashed_password"] != $hashed_password)
    {
        // passwords do not match
    }

}

answered Jan 23, 2013 at 22:44

Ricardo Souza

16.5k6 gold badges40 silver badges70 bronze badges

Collectives™ on Stack Overflow

MySQL conditional check

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related