Website Account Activator

To validate the email address users provide, send an email to the address with special URL

Posted on April 22, 2016 in PHP

When users sign up for your website, it’s helpful to know that they’ve provided you with a correct email address. To validate the email address they provide, send an email to the address they supply when they sign up. If they don’t visit a special URL included in the email after a few days, deactivate their account.

This system has three parts. The first is the notify-user.php program that send an email to a new user. The second is the verify-user.php that handles the verification. The third is the delete-user.php program that deactivates accounts of users who don’t visit the verification URL.

The SQL Schema for user information is as following:


CREATE TABLE users (
    email varchar(255) NOT NULL,
    created_on DATETIME NOT NULL,
    verify_string varchar(16) NOT NULL,
    verified TINYINT UNSIGNED
);

First Part: notify user


//connect to the database
$db = new PDO('sqlite:users.db');

$email = 'david';

//Generate verify_string
$verify_string = '';
for($i = 0; $i < 16; $i++) {
    $verify_string .= chr(mt_rand(32,126));
}

//Insert user info database
$sth = $db->prepare("INSERT INTO users (email, created_on, verify_string, verified) VALUES (?, datetime('now'), ?, 0)");

$sth->execute(array($email, $verify_string));

$verify_string = urlencode($verify_string);
$safe_email = urlencode($email);

$verify_url = "http://www.example.com/verify-user.php"';
$mail_body =<<<_MAIL_
To $email;

Please click on the following link to verify your account creation:

$verify_url?email=$safe_email&verify_string=$verify_string

If you do not verify your account in the next seven days, it will be deleted.
_MAIL_;
mail($email, "User Verfication", $mail_body); 

Second Part: verify user


//Connect to the database
$db = new PDO('sqlite:users.db');

$sth= $db->prepare('UPDATE users SET verified = 1 WHERE email = ? AND verify_string = ? AND verified = 0');

$res = $sth->execute(array($_GET['email'], $_GET['verify_string']));

if(!$res) {
    print "Please try again later due to a database error.";
}
else {
    if($sth->rowCount == 1) {
        print "Thank you, your account is verified.";
    }
    else {
        print "Sorry, you could not be verified";
    }
}

Third Part: delete user


//connect to the database
$db = new PDO('sqlite:users.db');

$window = '-7 days';

$sth = $db->prepare("DELTE FROM users WHERE verified = 0 AND created_on < datetime('now', ?)");

$res = $sth->execute(array($window));

if($res) {
    print "Deactivated ". $sth->rowCount() . " users.\n";
}
else {
    print "Can't delete users.\n";
}

Run the program once a day to scrub the users table of users that haven’t been verified. You might change $window value to adjust the expiry days.


comments powered by Disqus