CODEWITHSUNDEEP
mysql
user1315367
user1315367

Reputation: 25

MySQL conditional check

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();    
}

Upvotes: 1

Views: 192

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562270

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
        }
    }
}

Upvotes: 1

Ricardo Souza
Ricardo Souza

Reputation: 16456

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
    }

}

Upvotes: 0

Related Questions