How to activate account when activation link has been clicked for first time?

Question

I am trying to create an activation page so the when the user clicks on their activation link, it will navigate the user to the activate.php page and display a message stating wether the account is activated or not.

The problem I have is that it keeps displaying the message "The Code and Username doesn't match! Account is not Activated." It does not display the successful message "Account is Activated" at all, even though the activation link has been clicked on.

Also the "Active" column in the database should change to "1" if the correct username and code has been identified, but it still keeps displaying "0", meaning account is still inactive.

What my question is that why isn't it recognising that the username and activation code is correct?

Below is the registration.php script where it inserts the user's registration details and sends the activation link by email:

$teacherpassword = md5(md5("j3j".$teacherpassword."D2n"));  
$code = md5(rand(1,9));

$insertsql = "
INSERT INTO Teacher
(TeacherForename, TeacherSurname, TeacherEmail, TeacherAlias, TeacherUsername, TeacherPassword, Code)
VALUES
(?, ?, ?, ?, ?, ?, ?)
";
if (!$insert = $mysqli->prepare($insertsql)) {
// Handle errors with prepare operation here
}                                           

$insert->bind_param("sssssss", $getfirstname, $getsurname,
$getemail, $getid, $getuser,
$teacherpassword, $code);

$insert->execute();

if ($insert->errno) {
// Handle query error here
}

$insert->close();

    $getuser = $_POST['user'];
    $code = md5(rand(1,9));

    $site = "http://helios.hud.ac.uk/......../Mobile_app";
    $webmaster = "Mayur Patel <.......@hud.ac.uk>";
    $headers = "From: $webmaster";
    $subject = "Activate Your Account";
    $message = "Thanks for Registering. Click the link below to Acivate your Account. \n";
    $message .= "$site/activate.php?user=$getuser&code=$code\n";
    $message .= "You must Activate your Account to Login";

    if(mail($getemail, $subject, $message, $headers)){
    $errormsg = "You have been Registered. You must Activate your Account from the Activation Link sent to <b>$getemail</b>";
    $getfirstname = "";
    $getsurname = "";
    $getid = "";
    $getuser = "";
    $getemail = "";
    }

Below is the activate.php page where it checks the activation link and performs the mysqli operations and display the messages:

 <?php
    $user_to_be_activated  =   $_GET['user'];
    $code_to_be_matched   =   $_GET['code'];


    // don't use $mysqli->prepare here
    $query = "SELECT TeacherUsername, Active, Code FROM Teacher WHERE TeacherUsername = ? AND Code = ? ";
    // prepare query
    $stmt=$mysqli->prepare($query);
    // You only need to call bind_param once
    $stmt->bind_param("ss",$user_to_be_activated, $code_to_be_matched);
    // execute query
    $stmt->execute(); 
    // get result and assign variables (prefix with db)
    $stmt->bind_result($dbTeacherUsername, $dbActive, $dbCode);
    //get number of rows
    $stmt->store_result();
    $counting = $stmt->num_rows();

    if($counting == '1')
    {

   $updatesql = "UPDATE Teacher SET Active = ? WHERE TeacherUsername = ? AND Code = ?";                                         
    $update = $mysqli->prepare($updatesql);
    $update->bind_param("iss", 1, $user_to_be_activated, $code_to_be_matched);
        $update->execute();
        $update->close();

        echo "Account is Activated";
    }
    else
    {
        echo "The Code and Username doesn't match! Account is not Activated.";
    }

        ?>

I just want it to be able to activate the account when the link has been clicked for the first time.

Answer 1

you just need to remove that second code md5 because you already have code md5 once.

Answer 2

Got it, the error is here:

$update->bind_param("iss", 1, $user_to_be_activated, $code_to_be_matched);

simply it doesnt pass 1 and im not sure why it doesnt show you an error.

so you can just put that number 1 into a variable and than use it in your sql code like this:

so whole code:

   $code_activated = 1;

// don't use $mysqli->prepare here
    $query = "SELECT TeacherUsername, Active, Code FROM Teacher WHERE TeacherUsername = ? AND Code = ? ";
    // prepare query
    $stmt=$mysqli->prepare($query);
    // You only need to call bind_param once
    $stmt->bind_param("ss",$user_to_be_activated, $code_to_be_matched);
    // execute query
    $stmt->execute(); 
    // get result and assign variables (prefix with db)
    $stmt->bind_result($dbTeacherUsername, $dbActive, $dbCode);
    //get number of rows
    $stmt->store_result();
    $counting = $stmt->num_rows();

    if($counting == '1')
    {

   $updatesql = "UPDATE Teacher SET Active = ? WHERE TeacherUsername = ? AND Code = ?";                                         
    $update = $mysqli->prepare($updatesql);
    $update->bind_param("sss", $code_activated, $user_to_be_activated,  $code_to_be_matched);
        $update->execute() or die(mysql_error());
        $update->close();

        echo "Account is Activated";
    }
    else
    {
        echo "The Code and Username doesn't match! Account is not Activated.";
    }

You can test here:

http://design05.comuf.com/avtivate.php

with following details:

user: master

code: bbb111

http://design05.comuf.com/avtivate.php?user=master&code=bbb111

user:admin

code: abc123

http://design05.comuf.com/avtivate.php?user=admin&code=abc123

and of course try some wrong ones.

Answer 3

It seems that you generate a random code that you store in the database and then you generate another code that you add inside the email. Those two codes are likely to be different :)

Answer 4

How I did it:

Create a security_key in your database (new column), when a uses activates his account by something like:

      example.com/activate/username/security_key

It will set the "activated" column to 1. Now set a new hash, so it's impossible to activate it "again" and get some weird bugs.

Now, when the users wants to login, he hasn't set any credentials, but the e-mail is known. So just send him a "lost-password" e-mail with a recovery link:

     example.com/set-password/username/security_key

For security reasons it is best if you refresh the security_key on EVERY update in his row. So you can never get weird occurrences where a 3rd-party-user (hacker) gets to see his security key.

I hope this helped you :)

Conclusion, get some fresh air, have a moment and look at it for a second time with a clear mind. It's best if you sketch systems like this on paper first, so you know what you are working on. (I did the same, it really did help me!)