<

Registration and Email Confirmation in PHP

Published on
57,873 Points
27,173 Views
12 Endorsements
Last Modified:
Awarded
Introduction
One of the frequent application design questions goes something like this: "How can I confirm when a client registers on my web site?"

The registration might be for general use of a self-administered site like a forum, or for attendance at a specific event.  But the registration is a public page - anyone can register, and that means that any hacker or 'bot can attack your form.  When a client registers, you want to know that the registration is valid, so some kind of "handshake" makes sense.  One common method of getting this handshake is to send a confirmation email to the registrant, with a clickable link to a confirmation page.  When the client clicks the link to the confirmation page, the handshake can be completed.

Many sites use this register-and-confirm pattern, and all of them work the same way.  Once you see the moving parts of the application it is easy to understand and implement a similar design for your registration needs.  Once the registration table is set up in your data base, you can accomplish all of the pieces of the handshake within a single PHP script!

Common Basis for All Scripts
All of our scripts will need to connect to the data base, so we can begin by isolating this commonly used code into a separate "common" script.  We also have a specialized local function to validate an email address, so we will put that into the common script, too.  We will include the common script into our specialized scripts with require_once().  We choose the "require" version of the include() function because our scripts cannot run without this code.  We choose the "once" version of the include() function because the common script contains a function definition, and our scripts would fail if the definition somehow got included more than once.  

 
<?php // RAY_register_and_confirm_common.php


// COMMON CODE AVAILABLE TO ALL OUR REGISTER-AND-CONFIRM SCRIPTS


// SET ERROR REPORTING SO WE CAN DEBUG OUR SCRIPTS EASILY
error_reporting(E_ALL);

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// AN EMAIL VALIDATION SCRIPT THAT RETURNS TRUE OR FALSE
function check_valid_email($email)
{
    // IF PHP 5.2 OR ABOVE, WE CAN USE THE FILTER
    // MAN PAGE: http://php.net/manual/en/intro.filter.php
    if (strnatcmp(phpversion(),'5.2') >= 0)
    {
        if(filter_var($email, FILTER_VALIDATE_EMAIL) === FALSE) return FALSE;
    }
    // IF LOWER-LEVEL PHP, WE CAN CONSTRUCT A REGULAR EXPRESSION
    else
    {
        $regex
        = '/'                       // START REGEX DELIMITER
        . '^'                       // START STRING
        . '[A-Z0-9_-]'              // AN EMAIL - SOME CHARACTER(S)
        . '[A-Z0-9._-]*'            // AN EMAIL - SOME CHARACTER(S) PERMITS DOT
        . '@'                       // A SINGLE AT-SIGN
        . '([A-Z0-9][A-Z0-9-]*\.)+' // A DOMAIN NAME PERMITS DOT, ENDS DOT
        . '[A-Z\.]'                 // A TOP-LEVEL DOMAIN PERMITS DOT
        . '{2,6}'                   // TLD LENGTH >= 2 AND =< 6
        . '$'                       // ENDOF STRING
        . '/'                       // ENDOF REGEX DELIMITER
        . 'i'                       // CASE INSENSITIVE
        ;
        if (!preg_match($regex, $email)) return FALSE;
    }

    // FILTER or PREG DOES NOT TEST IF THE DOMAIN OF THE EMAIL ADDRESS IS ROUTABLE
    $domain = explode('@', $email);
    if ( checkdnsrr($domain[1],"MX") || checkdnsrr($domain[1],"A") ) return TRUE;

    // EMAIL NOT ROUTABLE
    return FALSE;
}

Open in new window


Our Data Base Table
Let's look at the data base table.  It needs three fields to work correctly.  We will keep the client's email address, the activation code, and the activation status.  Obviously we could keep lots of other information, but these three fields are all we need for the confirmation handshake to work correctly.  We mark the email address UNIQUE because email addresses are unique in all the world, and there can be only one registration per email address.  The activation status is set to zero by default.  When a confirmation occurs we add one to this value, so we can easily tell which clients have confirmed registrations.

 
<?php // RAY_register_and_confirm_create_table.php


// SET UP THE DATA BASE TABLE FOR THE REGISTER-AND-CONFIRM PROCESS


// BRING IN OUR COMMON CODE
require_once('RAY_register_and_confirm_common.php');

// REMOVE THE OLD VERSION OF userTable
$res = $mysqli->query('DROP TABLE userTable');

// CREATING THE userTable
$sql
=
"
CREATE TABLE userTable
( email_address  VARCHAR(96) NOT NULL DEFAULT '' UNIQUE
, activate_code  VARCHAR(32) NOT NULL DEFAULT ''
, activated_yes  INT         NOT NULL DEFAULT 0
)
"
;
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ALL DONE - SHOW WHAT WE CREATED
$sql = "SHOW CREATE TABLE userTable";
$res = $mysqli->query($sql);
$row = $res->fetch_object($res);
echo "<pre>";
var_dump($row);

Open in new window


An Administrator's View of the Data Base Table
Next, we will need to be able to look at our table to see who registered and how many registrations have been confirmed.  To do that, we need an administrative script for a view of the table.  For convenience in communicating with our registered users, we make each row of the output contain an email link; a single click will be able pop up an email dialog box.  This administrative script will report the registrations in two parts.

The first part of this report will be the list of confirmed registrations.  We query the table to get a record set that contains only the rows where "activated_yes" is greater than zero.  Since "activated_yes" is set to zero as its default value, it will only be non-zero if we have modified it.  Our confirmation process shown below does that modification.

The second part of this report will be the list of pending but unconfirmed registrations.  Since we know that they have registered but not yet confirmed their registration, we might want to be able to send their confirmation code again.  To do that, we construct a slightly different email link.  It includes a message subject and body that reminds the client of the need to confirm the registration.  It includes their activation code.  Most email reader programs recognize URLs in the body of the email and make clickable links out of the URLs.  By sending the URL this way, we make it easy for our client to confirm the registration.

 
<?php // RAY_register_and_confirm_admin.php


// ADMINISTRATOR SCRIPT FOR A VIEW OF THE REGISTER-AND-CONFIRM PROCESS


// BRING IN OUR COMMON CODE
require_once('RAY_register_and_confirm_common.php');

// SET THE URL OF THE CONFIRMATION PAGE HERE:
$confirm_page = $_SERVER["HTTP_HOST"] . '/RAY_register_and_confirm.php';


// GATHER INFORMATION ABOUT THE SUCCESSFUL REGISTRATIONS
$sql = "SELECT email_address FROM userTable WHERE activated_yes > 0 ORDER BY email_address";
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// HOW MANY ARE CONFIRMED
$num = $res->num_rows();
echo "<br/>$num CONFIRMED REGISTRATIONS:" . PHP_EOL;

// SHOW WHO IS CONFIRMED
while ($row = $res->fetch_assoc($res))
{
    $email_address = $row["email_address"];
    $uri
    = '<a href="mailto:'
    . $email_address
    . '?subject=THANK YOU FOR YOUR REGISTRATION'
    . '">'
    . $email_address
    . '</a>'
    ;
    echo "<br>$uri" . PHP_EOL;
}


// GATHER INFORMATION ABOUT THE PENDING REGISTRATIONS
$sql = "SELECT email_address, activate_code FROM userTable WHERE activated_yes = 0 ORDER BY email_address";
if (!$res = $mysqli->query($sql))
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// HOW MANY ARE NOT CONFIRMED
$num = $res->num_rows();
echo "<br/>$num REGISTRATIONS NOT YET CONFIRMED:" . PHP_EOL;

// SHOW WHO IS NOT CONFIRMED
while ($row = $res->fetch_assoc())
{
    $email_address = $row["email_address"];
    $activate_code = $row["activate_code"];

    // CONSTRUCT A CLICKABLE LINK TO RE-SEND THE ACTIVATION CODE
    $msg = '';
    $msg .= 'WE SEE YOU HAVE NOT CONFIRMED YOUR REGISTRATION YET.  TO CONFIRM, PLEASE CLICK THIS LINK: ' . PHP_EOL;
    $msg .= "http://" . $confirm_page . "?q=$activate_code";
    $msg .= PHP_EOL;
    $uri
    = '<a href="mailto:'
    . $email_address
    . '?subject=PLEASE CONFIRM REGISTRATION'
    . '&body='
    . $msg
    . '">'
    . $email_address
    . '</a>'
    ;
    echo "<br/>$uri" . PHP_EOL;
}

Open in new window


A Client-Facing Controller: Four Parts in One Script File
The client-facing part of the process is implemented in the register-and-confirm script.  This is a four-part script that handles all of the activities specific to the client handshake.  Although this script is organized into parts One through Four for convenience of programming, it may be easier to understand if we look at it from the bottom up.  This bottom-up direction is the chronological order of processing.

Part Four is the registration form.  This is the part of the script that will get executed if the script is called without anything in the GET or POST arrays.  It presents the form and then waits while the client enters an email address.  When the email address is submitted via the POST method, Part Three of the script is activated.

Part Three is the action script for the registration form.  It validates and normalizes the email address, and escapes it for safe use in a query.  Next, it generates an activation code from data elements that should be diverse enough to create a completely unique md5() string.  For a collision to occur on the activation code, we would need to have two clients register identical email addresses at exactly the same second from the same IP address, and have the random number generator create two identical random numbers at the same time -- that is just not likely to happen.  We escape the activation code for safe use in the query.  Strictly speaking this is unnecessary, since the md5() function generates an alphanumeric string that has no special characters and therefore does not require escaping, but our coding standards guide us to use mysql_real_escape_string() on all data that goes into mysql_query, so we comply with the standard.

We insert the email address and activation code into the userTable of our data base.  Here, things get a little interesting.  What if a client registered twice?  We would want to handle that condition gracefully, without creating any unnecessary error messages.  To do that we make a specific check for mysql_errno() equal to 1062, which is the signal for an attempt to make a duplicate INSERT into a UNIQUE column.  If that is the case, we know that we already have the email address in our data base, so we can recover the activation code if necessary.  Obviously, we only need to recover the activation code if the registration has not been confirmed.  If the registration has already been confirmed, we can simply thank the client and terminate the script.

Now that we are equipped with a valid email address, a useful activation code, and a data base table containing these elements, we are ready to ask the client to confirm the registration.  We prepare an email message with a clickable link to this script, that contains the activation code in the GET string of the URL.  We send the email to the client, and present a "check your email" message, and our Part Three work is complete.  When the client clicks on the activation link in the email message, it will take us to Part Two of our script.

Part Two would seem simple enough - just turn on the "activated_yes" setting in the data base, and we could do just that.  But it would be very easy to create a 'bot that polluted our data base if we used the GET string to cause an update.  Besides, the rules of HTTP steer us away from changing any data model on the basis of a GET method request.

Instead, we may be able to reduce the risk of attack if we ask for an additional piece of information during the confirmation process.  This should be something that is easy for the client to know, but hard for a 'bot to guess.  In a "real life" situation we might ask for a client password and provide a password hint, but for this example we can simulate that part of the handshake by asking for the email address and providing an email address hint.  We want to give the client a hint, but not one that is too easy for a 'bot to figure out, so we obscure the email address hint in a way that would be easy for a person to figure out.  When the client enters the email address and submits the form, we will have the email address in the POST array, and because we used the REQUEST_URI string in the action attribute, we will also have the activation code in the GET array.  This combination of two external inputs will cause the script to run Part One.

Part One changes the status of a registration from unconfirmed to confirmed.  We filter and escape the two external inputs - the email address and the confirmation code - and use these in a query to UPDATE the userTable.  By adding 1 to the "activated_yes" value we create the non-zero indicator of the confirmation, and we allow for accidental reruns of the confirmation script.  Why not just set the "activated_yes" to 1 instead of adding 1 to the count?  Because MySQL will not recognize any affected rows if nothing in the row changes during the query.  Assuming everything works as planned, we will always have one affected_rows() from the update query, and we can tell the client that the registration is confirmed.  If the process failed for any reason we will not find matching email and confirmation codes, so we can issue an error message in that case.

 
<?php // RAY_register_and_confirm.php


// HANDLE THE REGISTER-AND-CONFIRM PROCESS


// BRING IN OUR COMMON CODE
require_once('RAY_register_and_confirm_common.php');


// PART ONE - IF THIS IS A POST-METHOD REQUEST FOR CONFIRMATION
if
(  (!empty($_GET["q"]))
&& (is_string($_GET["q"]))
&& (!empty($_POST["e"]))
&& (is_string($_POST["e"]))
)
{
    // PROVIDE FILTERING AND SANITY CHECKS FOR THE EXTERNAL INPUT
    $activate_code = preg_replace('/[^A-Z0-9]/i', '', $_GET["q"]);
    $activate_code = substr(trim($activate_code), 0, 32);
    $safe_activate_code = $mysqli->real_escape_string($activate_code);

    $email_address = strtolower($_POST["e"]);
    $email_address = substr(trim($email_address), 0, 96);
    $safe_email_address = $mysqli->real_escape_string($email_address);

    // PREPATE AND RUN THE QUERY TO CONFIRM THE REGISTRATION
    $sql = "UPDATE userTable SET activated_yes = activated_yes + 1 WHERE activate_code = '$safe_activate_code' AND email_address = '$safe_email_address' LIMIT 1";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // DID THE UPDATE AFFECT A ROW?
    if ( $mysqli->affected_rows ) die("THANK YOU - YOUR ACTIVATION IS COMPLETE");

    // SHOW ERROR RESPONSE
    die("SORRY - YOUR ACTIVATION CODE OR EMAIL ADDRESS WAS NOT FOUND");
}


// PART TWO - IF THIS IS A GET-METHOD REQUEST FOR CONFIRMATION
if
(  (!empty($_GET["q"]))
&& (is_string($_GET["q"]))
&& (empty($_POST["e"]))
)
{
    // PROVIDE FILTERING AND SANITY CHECKS FOR THE EXTERNAL INPUT
    $activate_code = preg_replace('/[^A-Z0-9]/i', '', $_GET["q"]);
    $activate_code = substr(trim($activate_code), 0, 32);
    $safe_activate_code = $mysqli->real_escape_string($activate_code);

    // GET THE EMAIL ADDRESS FROM THE ACTIVATION CODE
    $sql = "SELECT email_address FROM userTable WHERE activate_code = '$safe_activate_code' LIMIT 1";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        ;
        trigger_error($err, E_USER_ERROR);
    }
    if ( $res->num_rows() == 0 ) die("SORRY - YOUR ACTIVATION CODE WAS NOT FOUND");

    // SET UP THE EMAIL ADDRESS HINT - billy@gmail.com HINTS bill? gmail com
    $row = mysql_fetch_assoc($res);
    $arr = explode('@', $row["email_address"]);
    $uid = $arr[0];
    $dmn = $arr[1];
    $len = strlen($dmn);
    $poz = strrpos($dmn, '.');
    $email_hint
    = substr($uid, 0, -1)
    .'?'
    . ' '
    . substr($dmn, 0, $poz)
    . ' '
    . end(explode('.', $dmn))
    ;

    // SHOW THE CONFIRMATION FORM WITH THE EMAIL ADDRESS HINT
    echo '<form method="post" action="' . $_SERVER["REQUEST_URI"] . '">' . PHP_EOL;
    echo 'TO CONFIRM REGISTRATION, ENTER YOUR EMAIL ADDRESS HERE:' . PHP_EOL;
    echo "<br/>HINT: IT LOOKS LIKE $email_hint" . PHP_EOL;
    echo '<input name="e" />' . PHP_EOL;
    echo '<input type="submit" />' . PHP_EOL;
    echo '</form>';
    die();
}


// PART THREE - IF THE REGISTRATION FORM HAS BEEN POSTED
if
(  (!empty($_POST["e"]))
&& (is_string($_POST["e"]))
&& (empty($_GET))
)
{
    // VALIDATE THE EMAIL ADDRESS
    if (!check_valid_email($_POST["e"])) die("SORRY - THE EMAIL ADDRESS IS NOT USABLE");

    // NORMALIZE THE EMAIL ADDRESS
    $email_address = trim($_POST["e"]);
    $email_address = strtolower($email_address);
    $safe_email_address = $mysqli->real_escape_string($email_address);

    // MAKE THE ACTIVATION CODE
    $activate_code
    = md5
    ( mt_rand()
    . time()
    . $email_address
    . $_SERVER["REMOTE_ADDR"]
    )
    ;
    $safe_activate_code = $mysqli->real_escape_string($activate_code);

    // INSERT THE EMAIL ADDRESS AND ACTIVATION CODE INTO THE DATA BASE TABLE
    $sql = "INSERT INTO userTable
    ( email_address
    , activate_code
    ) VALUES
    ( '$safe_email_address'
    , '$safe_activate_code'
    )"
    ;
    if (!$res = $mysqli->query($sql))
    {
        // IF ERROR, BUT NOT A DUPLICATE EMAIL ADDRESS
        if ( $mysqli->errno != 1062 )
        {
            $err
            = "QUERY FAIL: "
            . $sql
            . ' ERRNO: '
            . $mysqli->errno
            . ' ERROR: '
            . $mysqli->error
            ;
            trigger_error($err, E_USER_ERROR);
        }
        // IF A DUPLICATE REGISTRATION, RECOVER THE ACTIVATION CODE
        else
        {
            $sql = "SELECT activate_code FROM userTable WHERE email_address = '$safe_email_address' AND activated_yes = 0 LIMIT 1";
            $res = $mysqli->query($sql);
            $num = $res->num_rows();
            if ($num == 0) die("THANK YOU - YOU ARE ALREADY REGISTERED AND CONFIRMED");

            $row = $res->fetch_assoc();
            $activate_code = $row["activate_code"];
        }
    }

    // SEND THE ACTIVATION EMAIL
    $msg = '';
    $msg .= 'THANK YOU FOR YOUR REGISTRATION.  TO CONFIRM, PLEASE CLICK THIS LINK:' . PHP_EOL;
    $msg .= "http://" . $_SERVER["HTTP_HOST"] . $_SERVER["PHP_SELF"] . "?q=$activate_code";
    mail
    ( $email_address
    , 'PLEASE CONFIRM YOUR REGISTRATION'
    , $msg
    )
    ;
    // TELL THE CLIENT TO CHECK HER EMAIL
    die("PLEASE CHECK YOUR EMAIL FOR A CONFIRMATION LINK");
}


// PART FOUR - THE FORM FOR REGISTRATION
$form = <<<ENDFORM
<form method="post">
TO REGISTER, ENTER YOUR EMAIL ADDRESS HERE:
<input name="e" />
<input type="submit" />
</form>
ENDFORM;
echo $form;

Open in new window


Summary and Conclusion
Equipped with this example, you can add an email confirmation handshake to any registration form, and substantially reduce the risk that a 'bot will pollute your data base.  Want even more security?  Consider adding a CAPTCHA test to the registration and confirmation pages.

We can never prevent 100% of all attacks, but human-activated confirmations go a long way toward application safety and sanity.

Please give us your feedback!
If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles.  If you have questions or comments, please add them.  Thanks!
 
12
Comment
Author:Ray Paseur
4 Comments
 

Expert Comment

by:Nige Ward
This is a great script and does the job really well.
How can I make the script generate say 3, 5, 10 or more uniquely generated tokens and then send a batch of '1-time use' links to the newly registered users email address in one go ? (instead of sending the user only 1 single link from a single token.)
I would like a new user to register but instead of only being emailed a single url link to a page on my website, I would like them to receive a batch of 5 different 'single use' links which the user can return to and click-on to access different pages of my website.
I have been searching for answers to this for weeks so your help will be utterly amazing!
Thanks!
0
 
LVL 111

Author Comment

by:Ray Paseur
Hi, Nige Ward and welcome to E-E. Thanks for your kind words about the article.  We have a question and answer feature here, and that would be one good place to post a question about this topic.  We also have a Gigs feature where you can hire someone directly for small tasks.  

This SitePoint article looks good to me.  Just repeat the part about generating a token five times, store the URLs in an array and send the collection.
https://www.sitepoint.com/generating-one-time-use-urls/
0
 

Expert Comment

by:Luca Bonacina
I greately appreciated these scripts, even if on my hosting probably it doesn't work due to a version problem (I'm using 5.6.28).

It seem failing all the occurrence of instance like this:  $num = $res->num_rows();

In example:  Fatal error: Call to undefined method mysqli_result::num_rows() in /RAY_register_and_confirm.php on line 162

Do you think it is a version problem?
0
 

Expert Comment

by:Dexter Marx
@Luca Bonacina

I ran into the same problem. For everybody who is going to use this script with up to date PHP Versiions:

change num_rows() to num_rows
it's not a function anymore, but a variable

for instance, here:
    if ( $res->num_rows == 0 ){
        die("SORRY - YOUR ACTIVATION CODE WAS NOT FOUND");
    }

further you have to replace
    $row = mysql_fetch_assoc($res);
with
    $row = mysqli_fetch_assoc($res);
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month