Logged in devices management - PHP & MySQL

In this article, we will be creating logged in devices management feature that will allow the users to check how many devices they have been logged in. You might have seen this feature in Facebook where you can see a list of all devices where you have been logged in, and you also have the capability to remove device. The devices which has been removed will no longer be logged in and had to be logged in by entering the email and password again.

Following are the steps to manage multiple devices/browser logins:

When user enter correct email and password during login, we will check if current device and browser is trusted by that user.
If it is not trusted, then it will send an email with a verification code to user’s email address and an input field is displayed.
User have to enter the verification code in that input field.
Once the code is verified, then the user will be asked to trust this device/browser or not.
If user trusts the device, then the next time he tried to login, he won’t be asked for a verification code.
A separate page is created to show a list of all devices where user is logged in.
From that page, user can remove the device he wants.
When the device/browser is removed, then if the user tried to login from that device/browser, then a new verification code will be sent again on his email address.

Table of content:

Database structure
Login form
Login form submission
Verification code
Trust device
Show all logged in devices
Remove device

1. Database structure

First we will create 2 tables, one for users and one for devices. You might already have a table for users, if you do, make sure to add a new column verification_code in it. If you already have a table for users, you can add the verification_code column by running the following query:

/* if you already have the users table */
ALTER TABLE users ADD COLUMN verification_code TEXT NOT NULL

If you don’t have the users table, then run the following query in your phpMyAdmin or you can create the tables manually.

CREATE TABLE IF NOT EXISTS users(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    email TEXT NOT NULL,
    password TEXT NOT NULL,
    verification_code TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS devices(
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id INTEGER NOT NULL,
    browser_info TEXT NOT NULL,
    browser_token TEXT NOT NULL,
    last_login DATETIME NOT NULL,
    last_login_location TEXT NOT NULL,
    
    CONSTRAINT fk_devices_user_id FOREIGN KEY (user_id) REFERENCES users(id)
);

user_id will be a foreign key from users table.
browser_info will store the browser and operating system name e.g. Safari Mac OS X.
browser_token this will be stored as a cookie in browser to identify the browser.
last_login this will tell the time when that device was last logged in.
last_login_location this will tell the user’s location from where the login happened.
fk_devices_user_id is the name of constraint we use to set the primary key of ID from users table as a foreign key in devices table.

2. Login form

<form method="POST">

    <table>
        <tr>
            <td>
                Email
            </td>
            <td>
                <input type="email" name="email">
            </td>
        </tr>

        <tr>
            <td>
                Password
            </td>

            <td>
                <input type="password" name="password">
            </td>
        </tr>
    </table>
 
    <input type="submit" value="Login" name="login">
</form>

This will create an email and password field along with a submit button labeled as “Login”. You might already have one similar to this, actual change will be in the next step.

3. Login form submission

When the form is submitted, we will check it’s credentials and make sure they are right. Then we will check if this device or browser is trusted by user, if not, then we will send an email to the user to verify this device. If yes, then he will be redirected to devices page where he can see all his devices.

To send an email, we are going to use PHPMailer, you can learn how to integrate PHPMailer by following this.

<?php

// session start is required for login
session_start();

// connecting with database
$conn = mysqli_connect("localhost", "db_username", "db_userpassword", "db_name");

// check if the form is submitted
if (isset($_POST["login"]))
{
    // get input field values, preventing from SQL injection
    $email = mysqli_real_escape_string($conn, $_POST["email"]);
    $password = mysqli_real_escape_string($conn, $_POST["password"]);
      
    // check if the email exists in database
    $sql = "SELECT * FROM users WHERE email = '" . $email . "'";
    $result = mysqli_query($conn, $sql);
 
    if (mysqli_num_rows($result) == 0)
    {
        echo "In-correct email";
        exit();
    }
    else
    {
        // check if the password is correct, we are using hashed password
        $row = mysqli_fetch_object($result);
        if (password_verify($password, $row->password))
        {
            // store the user in session
            $_SESSION["user"] = $row;

            // check if the device or browser is trusted or not
            $sql = "SELECT * FROM devices WHERE browser_token = '" . $_COOKIE["browser_token"] . "' AND user_id = '" . $row->id . "'";
            $result = mysqli_query($conn, $sql);

            // device/browser is trusted
            if (mysqli_num_rows($result) > 0)
            {
                header("Location: devices.php");
            }
            else
            {

                // not trusted, send an email.
                // generate a unique verification code
                $verification_code = uniqid();

                // Instantiation and passing `true` enables exceptions
                $mail = new PHPMailer(true);

                try {
                    //Server settings
                    $mail->SMTPDebug = 0;                      // Enable verbose debug output
                    $mail->isSMTP();                                            // Send using SMTP
                    $mail->Host       = 'smtp.gmail.com';                    // Set the SMTP server to send through
                    $mail->SMTPAuth   = true;                                   // Enable SMTP authentication
                    $mail->Username   = 'your_email';                     // SMTP username
                    $mail->Password   = 'your_password';                               // SMTP password
                    $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;         // Enable TLS encryption; `PHPMailer::ENCRYPTION_SMTPS` encouraged
                    $mail->Port       = 587;                                    // TCP port to connect to, use 465 for `PHPMailer::ENCRYPTION_SMTPS` above

                    //Recipients
                    $mail->setFrom('your_email', 'your_name');
                    $mail->addAddress($email);

                    // Content
                    $mail->isHTML(true);                                  // Set email format to HTML
                    $mail->Subject = 'Verify this browser';
                    $mail->Body    = 'Your verification code is <b style="font-size: 30px;">' . $verification_code . '</b>';

                    $mail->send();
                    // echo 'Message has been sent';
                } catch (Exception $e) {
                    die("Message could not be sent. Mailer Error: {$mail->ErrorInfo}");
                }

                // save the verification code in users table
                $sql = "UPDATE users SET verification_code = '" . $verification_code . "' WHERE id = '" . $_SESSION["user"]->id . "'";
                mysqli_query($conn, $sql);

                ?>

                <!-- show a form to enter verification code from email -->

                <h1>New device detected. Email has been sent with a verification code.</h1>

                <form method="POST">

                    <table>
                        <tr>
                            <td>
                                Verification code
                            </td>
                            <td>
                                <!-- verification code input field -->
                                <input type="text" name="verification_code">
                            </td>
                        </tr>
                    </table>
                 
                    <!-- submit button -->
                    <input type="submit" value="Verify" name="verify">
                </form>

                <?php
            }
        }
        else
        {
            echo "Invalid password";
            exit();
        }
    }
}

Once the email is sent, you will see the verification code field value in that user’s row in database. You will also see an input field labeled as “Enter verification code”, here you need to enter the coode received in your email address.

Note: If you do not receive an email, make sure you have entered correct email and password in PHPMailer and also your Gmail’s account less secure apps option should be enabled. You can enable it from here.

4. Verification code

Now when the verification form is submitted, we will do the following:

Check if the verification code entered in form matches with the one in database.
If matches, then we will empty the verification code field from users table.
And show a form to trust this device/browser or not.

<?php

if (isset($_POST["verify"]))
{
    $user_id = isset($_SESSION["user"]) ? $_SESSION["user"]->id : 0;
    $verification_code = $_POST["verification_code"];

    $sql = "SELECT * FROM users WHERE id = '" . $user_id . "' AND verification_code = '" . $verification_code . "'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) == 0)
    {
        die("Verification code has been expired.");
    }

    $sql = "UPDATE users SET verification_code = '' WHERE id = '" . $user_id . "' AND verification_code = '" . $verification_code . "'";
    mysqli_query($conn, $sql);

?>

    <form method="POST">
        <input type="button" onclick="window.location.href = 'devices.php';" value="Don't trust this device">
        <input type="submit" name="trust_device" value="Trust device">
    </form>

<?php
}

If user presses “Don’t trust this device”, then user will again receive the verification code next time he tried to login in this browser. We are simply redirecting the user to devices page where he will see all his logged in devices.

5. Trust device

Now if the user presses the button to “Trust device”, then we will do the following:

Generate a unique ID, store it in browser’s cookies.
Get browser info (browser name, device type and platform). You need to uncomment browscap line in your php.ini to use this feature.
Get user’s IP address to get his location using Geo plugin. If it is not working from localhost, then you need to place your IP address manually, you can get your IP address from Google.
From Geo plugin, we are getting country and city name.
Finally we will insert that data in devices table, thus next time you will login with same device, it will not ask for verification code.

So our logged in devices management feature will keep track of all trusted devices.

<?php

if (isset($_POST["trust_device"]))
{
    $browser_token = uniqid();
    setcookie("browser_token", $browser_token);

    $browser = get_browser(null, true);
    $browser_info = $browser["browser"] . " " . $browser["device_type"] . " " . $browser["platform"];

    $user_ip = getenv('REMOTE_ADDR');

    $geo = unserialize(file_get_contents("http://www.geoplugin.net/php.gp?ip=$user_ip"));
    $country = $geo["geoplugin_countryName"];
    $city = $geo["geoplugin_city"];
    $last_login_location = $country . ", " . $city;

    $sql = "INSERT INTO devices (user_id, browser_info, browser_token, last_login, last_login_location) VALUES ('" . $_SESSION["user"]->id . "', '" . $browser_info . "', '" . $browser_token . "', NOW(), '" . $last_login_location . "')";
    mysqli_query($conn, $sql);

    header("Location: devices.php");
}

?>

You will be redirected to file named “devices.php“. Now we need to show all logged in devices to user so he can remove if he want.

6. Show all devices

Create a file named devices.php and paste the following code in it:

<?php

// devices.php

// session start is required for login
session_start();

// connecting with database
$conn = mysqli_connect("localhost", "db_username", "db_userpassword", "db_name");

// check if the user is logged in
if (!isset($_SESSION["user"]))
{
    die("Not logged in");
}

// paste the remove device code here from next step

// get all devices of logged in user
$sql = "SELECT * FROM devices WHERE user_id = '" . $_SESSION["user"]->id . "'";
$result = mysqli_query($conn, $sql);

?>

Apply some CSS to make the table look good.

table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 15px;
}

Show all devices data in tabular form:

<!-- table to show all devices data -->
<table>
    <tr>
        <th>Device info</th>
        <th>Last login</th>
        <th>Last location</th>
        <th>Actions</th>
    </tr>

    <!--  table row for each device -->
    <?php while ($row = mysqli_fetch_object($result)): ?>
        <tr>
            <td><?php echo $row->browser_info; ?></td>

            <!-- last login date in readable format -->
            <td><?php echo date("d M, Y H:i:s A", strtotime($row->last_login)); ?></td>
            <td><?php echo $row->last_login_location; ?></td>
            <td>
                <!-- form to remove the device -->
                <form method="POST">
                    <input type="hidden" name="id" value="<?php echo $row->id; ?>">
                    <input type="submit" name="remove_device" value="Remove device">
                </form>
            </td>
        </tr>
    <?php endwhile; ?>
</table>

The code is self-explanatory in comments. Now we need to add a function to remove device. We have already displayed a form with a submit button which when clicked should remove the device from user’s logged in devices list and should not allow that device to login without verification.

7. Remove device

Now we will simply remove the selected device from devices table for logged in user:

<?php

// check if form is submitted
if (isset($_POST["remove_device"]))
{
    // get device ID
    $id = $_POST["id"];

    // remove from database
    $sql = "DELETE FROM devices WHERE user_id = '" . $_SESSION["user"]->id . "' AND id = '" . $id . "'";
    mysqli_query($conn, $sql);

    // success message
    echo "Device has been removed.";
}

?>

So you have successfully created your logged in devices management feature. Try integrating it in one of your existing project. And let us know if you face any problem.

[wpdm_package id=’832′]

Post Views: 474

Logged in devices management – PHP & MySQL