email validation from link php

Question

I am trying to build a email verification. Sending an email with a link to the user is working. Now I want to set active = 1 when the user clicks on the link wich he received. I have checked the variables $email and $key they are getting the right information from the url. When the active is set to 1 I want to echo an ahref to login.php. I think there is someting wrong in my SQL query can somebody help?

<?php

if (isset($_GET['email'])) {
 $email = $_GET['email'];
}
if (isset($_GET['hash'])){
 $key = $_GET['hash'];
}

 $query = $mysqli->query("UPDATE `users` SET active=1 WHERE `email` = '". $email ."' AND `mailcheck` ='". $key ."' ");

 $result  = $query->fetch_row();

if($result == 1){

     echo "Your account is now active. You may now <a href="login.php">Log in</a>";

}
 else {
 echo "Your account could not be activated. Please recheck the link or contact the system administrator. test";
}


} 
?>

Answer 1

Hold on here. fetch_row() http://php.net/manual/en/mysqli-result.fetch-row.php is for a SELECT and not UPDATE.

What you're looking to use is mysqli_affected_rows()

• http://php.net/manual/en/mysqli.affected-rows.php

on UPDATE in order to check if the update was successful.

If you're looking to do a SELECT here (which makes more sense really), then you need to use mysqli_num_rows(), and if both exists, then do the UPDATE.

• http://php.net/manual/en/mysqli-result.num-rows.php

You should also check for errors against your query:

• http://php.net/manual/en/mysqli.error.php

---

If a row/user exists:

Consult an answer of mine https://stackoverflow.com/a/22253579/1415724 to check if a user exists, where you can base yourself on it.

---

Plus, a suggestion. Use !empty() instead of isset(). It's usually best to check against values.

What would also work better is to check if any are empty, rather than 2 conditional statements.

If one is left empty, your code will continue to execute and in turn, your query failing.

If you want to keep your present method, then you should exit; after each GET, but I wouldn't recommend it.

More like:

if ( !empty($_GET['email']) && !empty($_GET['hash']) ) {
  $email = $_GET['email'];
  $key = $_GET['hash'];
}

else{ exit; }

---

Your present code is open to SQL injection. Use mysqli_* with prepared statements, or PDO with prepared statements.

Answer 2

The problem is because of the following line,

$result  = $query->fetch_row();

You're trying to do UPDATE operation but you're actually fetching the result row using ->fetch_row() statement, which by the way doesn't exist because UPDATE operation doesn't return any result set.

Use ->affected_rows property to get the number of affected rows from the UPDATE operation, like this:

$mysqli->query("UPDATE `users` SET active=1 WHERE `email` = '". $email ."' AND `mailcheck` ='". $key ."'");

if($mysqli->affected_rows ==  1){
    echo "Your account is now active. You may now <a href=\"login.php\">Log in</a>";
}else{
    echo "Your account could not be activated. Please recheck the link or contact the system administrator.";
}

Here's the reference:

• mysqli::$affected_rows

Edited:

Your code on the validation page should be like this:

if(isset($_GET['email']) && isset($_GET['hash'])){
    $email = htmlentities($_GET['email']);
    $key = htmlentities($_GET['hash']);

    $mysqli->query("UPDATE `users` SET active=1 WHERE `email` = '". $email ."' AND `mailcheck` ='". $key ."'");

    if($mysqli->affected_rows){
        echo "Your account is now active. You may now <a href=\"login.php\">Log in</a>";
    }else{
        echo "Your account could not be activated. Please recheck the link or contact the system administrator.";
    }
}else{
    echo "wrong parameters.";
}

Re-edited:

After extensive debugging with OP the issue is resolved now, and this is the final working code,

if (isset($_GET['email']) && isset($_GET['hash'])) { 
    $email = $_GET['email']; 
    $key = $_GET['hash']; 

    $mysqli->query("UPDATE `users` SET active=1 WHERE `email` = '". $email ."' AND `mailcheck` ='". $key ."' "); 


    if($mysqli->affected_rows) { 

        echo "Your account is now active"; 

    }else { 
        echo "Failed"; 
    } 
}