Verfying User Accounts via Email

On sites that have multi user accounts, some people create accounts just to cause trouble. Perhaps someone will troll your bulletin board or try to mess some other part of your system. A common problem with online stores is that someone may place an order and use a fake email address because he or she doesn't want to be spammed, making it difficult for you to reach that person if you need to ask a question about the order.

One fairly effective way to verify that your users are real is to force them to validate an email address. When you first create the user account, keep it disabled until the user clicks on a verification link in an email message. Accessing the link activates the account.

This section illustrates a system that keeps track of new users who haven't yet activated their accounts. When a user tries to log in to your system, you can first check to see if the account hasn't been activated. You need the following components:

• A MySQL database>
• A pending_logins table with two fields: a login and a key. You can create this table with the following SQL (we're using a field called ukey because key is a reserved keyword):

  CREATE TABLE pending_logins (
      'login' varchar(32), 'ukey' varchar(32),
      PRIMARY KEY ('login'), 
      INDEX (ukey));
  

• PHPMailer, already installed and in the phpmailer directory

There are three functions in all: a verification generator, an activator, and a verifier. All of these functions assume that you have already validated the login name as a MySQL-safe string, because they always appear inside other code. Let's first look at the verification-generator function. You need to place a call into your account-generation code to generate a key for unlocking the account and then send that key to the user's email address. The first part generates a random 32-character string of lowercase letters to serve as a key to unlock the account:

function make_verification($login, $email, $db) {
 /* Generate an account verification link and send it to the user. */
 /* Generate key. */
 $key = ""; $i = 0;
 while ($i < 32) {
     $key .= chr(rand(97, 122));
     $i++; 
 }

Next we place the key in the pending_logins table. Because the login is the primary key, and therefore multiple rows per login are not allowed, we make sure that there are no preexisting rows; then we insert the data into the table:

 /* Place the key in the table; first delete any preexisting key. */
 $query = "DELETE FROM pending_logins WHERE login = '$login'";
 mysql_query($query, $db);
 $query = "INSERT INTO pending_logins (login, ukey) VALUES ('$login', '$key')";
 mysql_query($query, $db);
 if (mysql_error($db)) {
   print "Key generation error.";
   return false; 
 }

Now we need to generate the URL that the user must visit to activate the account. Obviously, you'll need to change this to your own server name and activation script, but make sure that you send the key as a parameter somewhere:

 /* Activation URL */
 $url = "http://accounts.example.com/activate.php?k=$key";

All that's left to do is send the email to the user. You'll likely want to customize this part as well.

include_once("phpmailer/class.phpmailer.php");
 $mail = new PHPMailer;
 $mail->ClearAddresses();
 $mail->AddAddress($email, $login);
 $mail->From = 'generator@example.com';
 $mail->FromName = 'Account Generator';
 $mail->Subject = 'Account verification';
 $mail->Body = "To activate your account, please click on the account generation URL below: $url";

 if ($mail->Send()) {
     print "Verification message sent.";
 } else {
     print $mail->ErrorInfo;
     return false;
 }
 return true;
}

To use this function, call it as follows (db is a MySQL database handle that you have opened previously). It returns true if the account was placed in the pending_logins table and PHPMailer was able to send the activation message:

make_verification(login, email_address, db)

Now that we have the verification part out of the way, the next part is a function to activate an account when the user clicks on the link. The first thing to do is to sanitize the key sent to us in case the user goofed up or someone is trying to break in. Because the generator used only lowercase characters, we'll just throw out anything that doesn't fit:

function activate_account($key, $db) {
 /* Activate an account based on a key. */
 /* Clean up the key if necessary. */ 
 $key = preg_replace("/[^a-z]/", "", $key);

Now we see if the key is bogus or not. If it's not even in the pending_logins table, there's nothing to do, and we return false to indicate that we didn't do anything:

 $query = "SELECT login FROM pending_logins WHERE ukey = '$key'";
 $c = mysql_query($query, $db);
 if (mysql_num_rows($c) != 1) {
  return false;
 }

If we get this far, we know that the key exists in the table, so all we need to do is remove that row to activate the login. Notice that we don't even need to know what the login name is.

  $query = "DELETE FROM pending_logins WHERE ukey = '$key'";
  mysql_query($query, $db);
  if (mysql_error($db)) {
     return false;
  }
  return true;
}

To use this function, call it as follows:

activate_account($_REQUEST["k"], db)

The final piece is a function that you need to place inside your user login code to determine whether the account is active or not. If an account has not been activated, it has a row in the pending_logins table. Therefore, you need only look up a username in that table:

function is_active($login, $db) {
 /* See if an account has been activated. */
 $query = "SELECT count(*) AS c FROM pending_logins WHERE login ='$login'";
 $c = mysql_query($query, $db);
 if (mysql_error($db)) {
     return false;
 }
 $r = mysql_fetch_array($c);
 if (intval($r["c"]) > 0) {
     return false;
 }
 return true;
}

There are many things you may need to do to this system to make it fit in with your site. For example, you can add a timestamp field to the table so that you can cull inactive accounts that are never verified. There may be many other reasons to deactivate an account; in that case, you need to store a reason for deactivation in the table. You may even want to include the activation key in the main login table. The activation mechanism illustrated here is specifically meant to plug into foreign login systems with minimal pain.

Hope it helps.