DEV Community

Code Boxx
Code Boxx

Posted on

Simple OTP In PHP MYSQL

(1) THE DATABASE

CREATE TABLE `otp` (
  `email` varchar(255) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `otp`
  ADD PRIMARY KEY (`_email`);
Enter fullscreen mode Exit fullscreen mode
  • email User's email, primary key. You can also tie this to an order, transaction, or whatever you want to add an OTP to.
  • pass The OTP itself.
  • timestamp Timestamp of when the OTP is created. Used for expiry and brute force prevention.

(2) PHP LIBRARY

<?php
class OTP {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct() {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE CONNECTION
  function __destruct() {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) HELPER - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GENERATE OTP
  function generate ($email) {
    // (D1) CHECK EXISTING OTP REQUEST
    $this->query("SELECT * FROM `otp` WHERE `email`=?", [$email]);
    $otp = $this->stmt->fetch();
    if (is_array($otp) && (strtotime("now") < strtotime($otp["timestamp"]) + (OTP_VALID * 60))) {
      $this->error = "You already have a pending OTP.";
      return false;
    }

    // (D2) CREATE RANDOM OTP
    $alphabets = "abcdefghijklmnopqrstuwxyzABCDEFGHIJKLMNOPQRSTUWXYZ0123456789";
    $count = strlen($alphabets) - 1;
    $pass = "";
    for ($i=0; $i<OTP_LEN; $i++) { $pass .= $alphabets[rand(0, $count)]; }
    $this->query(
      "REPLACE INTO `otp` (`email`, `pass`) VALUES (?,?)",
      [$email, password_hash($pass, PASSWORD_DEFAULT)]
    );

    // (D3) SEND VIA EMAIL
    if (@mail($email, "Your OTP",
      "Your OTP is $pass. Enter at <a href='http://localhost/3b-challenge.php'>SITE</a>.",
      implode("\r\n", ["MIME-Version: 1.0", "Content-type: text/html; charset=utf-8"])
    )) { return true; }
    else {
      $this->error = "Failed to send OTP email.";
      return false;
    }
  }

  // (E) CHALLENGE OTP
  function challenge ($email, $pass) {
    // (E1) GET THE OTP ENTRY
    $this->query("SELECT * FROM `otp` WHERE `email`=?", [$email]);
    $otp = $this->stmt->fetch();

    // (E2) CHECK - NOT FOUND
    if (!is_array($otp)) {
      $this->error = "The specified OTP request is not found.";
      return false;
    }

    // (E3) CHECK - EXPIRED
    if (strtotime("now") > strtotime($otp["timestamp"]) + (OTP_VALID * 60)) {
      $this->error = "OTP has expired.";
      return false;
    }

    // (E4) CHECK - INCORRECT PASSWORD
    if (!password_verify($pass, $otp["pass"])) {
      $this->error = "Incorrect OTP.";
      return false;
    }

    // (E5) OK - DELETE OTP REQUEST
    $this->query("DELETE FROM `otp` WHERE `email`=?", [$email]);
    return true;
  }
}

// (F) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (G) OTP SETTINGS
define("OTP_VALID", "15"); // otp valid for n minutes
define("OTP_LEN", "8");    // otp length

// (H) NEW OTP OBJECT
$_OTP = new OTP();
Enter fullscreen mode Exit fullscreen mode
  • (A, B, H) When $_OTP = new OTP() is created, the constructor connects to the database. The destructor closes the connection.
  • (C) Helper function to run SQL query.
  • (F & G) Database & OTP settings - Change to your own.
  • (D) Step 1 of the OTP process, the user requests for an OTP. Pretty much "generate a random string, save into database, and send it to the user via email".
  • (E) Step 2 of the OTP process, user clicks on the link in email and enters the OTP. This function then verifies the entered OTP against the database.

(3) HTML PAGES

<!-- (A) OTP REQUEST FORM -->
<form method="post" target="_self">
  <label>Email</label>
  <input type="email" name="email" required value="jon@doe.com">
  <input type="submit" value="Request OTP">
</form>

<?php
// (B) PROCESS OTP REQUEST
if (isset($_POST["email"])) {
  require "2-otp.php";
  $pass = $_OTP->generate($_POST["email"]);
  echo $pass ? "<div class='note'>OTP SENT.</div>" : "<div class='note'>".$_OTP->error."</div>" ;
}
?>
Enter fullscreen mode Exit fullscreen mode

This is just a dummy form. In your own project, use $_OTP->generate(EMAIL) to create the OTP and send it to the user.

<!-- (A) OTP CHALLENGE FORM -->
<form method="post" target="_self">
  <label>Email</label>
  <input type="email" name="email" required value="jon@doe.com">
  <label>OTP</label>
  <input type="password" name="otp" required>
  <input type="submit" value="Go">
</form>

<?php
// (B) PROCESS OTP CHALLENGE
if (isset($_POST["email"])) {
  require "2-otp.php";
  $pass = $_OTP->challenge($_POST["email"], $_POST["otp"]);
  // @TODO - DO SOMETHING ON VERIFIED
  echo $pass ? "<div class='note'>OTP VERIFIED.</div>" : "<div class='note'>".$_OTP->error."</div>" ;
}
?>
Enter fullscreen mode Exit fullscreen mode
  • The user clicks on the link in the email and lands on this page.
  • Enter the OTP, and $_OTP->challenge() will do the verification.
  • Thereafter, do whatever is required.

IMPROVEMENTS

This is just a simplified example to help beginners to grasp the concept and process flow. At the bare minimum, I will recommend:

  • Enforce the use of HTTPS.
  • Add tries to the otp table.
  • Modify function challenge () - On every wrong password attempt, tries + 1.
  • Do something if (tries >= N). Lock the user's account, freeze the transaction, require manual admin intervention, suspend for a certain time, etc...

THE END

That's all for this condensed tutorial. Here are the links to the GIST and more.

Gist | Simple PHP MYSQL OTP - Code Boxx

Top comments (1)

Collapse
 
pcmagas profile image
Dimitrios Desyllas

First and foremost I think this piece of code:

 $alphabets = "abcdefghijklmnopqrstuwxyzABCDEFGHIJKLMNOPQRSTUWXYZ0123456789";
    $count = strlen($alphabets) - 1;
    $pass = "";
    for ($i=0; $i<OTP_LEN; $i++) { $pass .= $alphabets[rand(0, $count)]; }

Enter fullscreen mode Exit fullscreen mode

Wont offer good randomness because rand offer time-based randomness. I suggest using the following approach:

// In in class use const
define(DEFAULT_STR_LENGTH,100);
function cryptographicallySecureRandomString(int length=DEFAULT_LENGTH){

       if($length <0){ 
              $length = -$length;
       }elseif($length==0){
          $length = DEFAULT_LENGTH;
       }

        $string = base64_encode(openssl_random_pseudo_bytes(2*$length));
        $string = preg_replace('/[^A-Za-z0-9]/',"",$string);

        return substr($string,0,$length);
}
Enter fullscreen mode Exit fullscreen mode

I use the cryptographically-secure approach openssl_random_pseudo_bytes in order to ensure that I have good enough randomness at my passwords. Also random_bytes can be used as well.

Also it seems a bit faster instead of looping just take a big pool of random chars remove unwanted ones (non-printable ones) and return it.

The base64_encode Ensures that all random bytes will be printable as well and from the printable character pool , then we remove non-alphanumeric ones and then we trim the ones that are not in length.