php if statement random failure

Question

I'm putting together an email activation page for my site and having issues. It seems that despite the page receiving all the needed info to show the successful activation message, it randomly decides to show the error message stating that the credentials did not match anything in the database. here's the code

 // Check their credentials against the database
  try {
    $check = $db->prepare("SELECT * FROM users WHERE id= ? AND username = ? AND email = ? AND user_ident = ? AND activated = '1' AND email_activated = '0' LIMIT 1");

    $check->bindParam(1, $id);
    $check->bindParam(2, $u);
    $check->bindParam(3, $e);
    $check->bindParam(4, $p);
    $check->execute();

    $num_rows = $check->rowCount();

} catch(Exception $er){
    // ERROR : COULD NOT INSERT EMAIL INTO USERS TABLE
    Send_email('support@site.com', 'check user - email activation failure', 'Could not check user '.$er);   
    exit;
}
// Evaluate for a match in the system (0 = no match, 1 = match)
if($num_rows === 0){
    Send_email('support@site.com', 'email activation credentials no match', 'invalid email activation variables.<br/> email: '.$e.'<br/>userid: '.$id.'<br/>username: '.$u.'<br/>user_ident: '.$p); 
    header("location: message.php?msg=Your credentials are not matching anything in our system");
    exit();
}
// Match was found, you can activate them
try {
    $checking = $db->prepare("UPDATE users SET email_activated = 1 WHERE id= ? LIMIT 1");
    $checking->bindParam(1, $id);
    $checking->execute();
} catch (Exception $er){
    // ERROR : COULD NOT INSERT EMAIL INTO USERS TABLE
    Send_email('support@site.com', 'Email add error', 'Could not set active state '.$er);   
    exit;
}

So sometimes, when clicking on the activation link in the email it will work fine, then other times it will throw up the error saying that the credentials do not match anything in the database HOWEVER when i check the database immediately after clicking the link, the email_activated field has been updated to show that it's been activated. This technically shouldn't be occurring as, if i'm doing this correctly, the page will exit after throwing up the credentials error, meaning the email_activated update code should never even run. It seems like it might be running twice but i've used a counter and that's only showing one run. any help would be great!

Answer 1

Well, you have a heap more filters in your first query. That is:

id= ? AND username = ? AND email = ? AND user_ident = ? AND activated = '1' AND email_activated = '0' 

Whereas you only have the one in the second:

id= ?

You cant expect the same results if the queries are totally different. One of the items in the first query is obviously failing to match, but since your second query ignores all but "id" it goes ahead and updates the row.

Answer 2

It may be that the browser is caching the redirect. Try specifying explicitly that this is "302 Found" redirect.

header('HTTP/1.1 302 Found');
header('Location: /your-location');

Or include all caching headers as you suggested:

header('Expires: Sun, 01 Jan 2014 00:00:00 GMT'); 
header('Cache-Control: no-store, no-cache, must-revalidate'); 
header('Cache-Control: post-check=0, pre-check=0', FALSE); 
header('Pragma: no-cache');

Answer 3

The PDO rowCount is not suitable for your purpose. Check - http://php.net/manual/en/pdostatement.rowcount.php

"PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications."

See this SO question too, for better understanding the scenario.