Nicollet.Net

← POST Requests | Delayed Requests →

The next step is to actually populate the authentication model with relevant code. The responsibilities of the model are fairly well outlined: to handle the authentication of existing users (function AuthenticationModel::Login) and the registration of new users (function AuthenticationModel::Register).

The password will not be stored on the system, instead, it will be generated on a salt-and-hash method in order to be un-retrievable.

Why go to all this length instead of storing the plaintext password in the database (plus, doing so would let us send the password back to the user instead of forcing him to reset it)? The reason is security: there are inevitably times where the database contents will have to be accessed by something other than the system itself. This could be an attack on the database that reveals the contents of our authentication tables, or this could be an export feature that has to output the passwords in an importable format (therefore, plaintext). And showing user passwords to anyone is a bad idea. By storing a hash of the password, we can determine if a password matches the hash (by hashing it), which adds a whole new layer of security because retrieving a password from a hash is not easy, but even that is not perfectly secure: an attacker might notice that his password hash is the same as that of another user, or use a hash dictionary to reverse common passwords. By using a random salt and hashing (password + salt) instead of just (password), we make this process much harder because two users may have different salts even if they have the same password, and salts are difficult to find with a dictionary.

Last but not least, it could be interesting to retrieve the user that is tied to a certain authentication token (function AuthenticationModel::GetUser): separating tokens and users is important, because it allows us to invalidate a token at will should the need arise, for instance when an account is stolen—even if, until then, a token will be nothing more than an user identifier behind the scenes.

Once the responsibilities are known, one must design the underlying database storage for the user authentication data. This will be done using two tables: an user table, containing all the user identifiers, and an authentication table containing the e-mail and password used for connecting.

CREATE TABLE `user` (
  -- An unique identifier used across the system
  `user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -- The display name
  `user_name` VARCHAR(255) NOT NULL
);

CREATE TABLE `authentication` (
  -- Foreign key to join with the user table
  `user_id` INT NOT NULL UNIQUE KEY REFERENCES `user`(`user_id`) ,
  -- The login mail (used only for retrieving password)
  `auth_mail` VARCHAR(255) NOT NULL PRIMARY KEY,
  -- The MD5 hash of (salt + pass)
  `auth_pass` VARCHAR(32) NOT NULL,
  -- A two-character salt used for hashing
  `auth_salt` VARCHAR(2)
);

Then, we design the requests that match our previous functions.

-- 1. Login (mail, password)
SELECT `user_id` FROM `authentication`
WHERE  `auth_mail` = ?
  AND  `auth_pass` = MD5(CONCAT(`auth_salt`, ?))
LIMIT  1

-- 2. CreateUser(name)
INSERT INTO `user`(`user_name`) VALUES(?)

-- 3. isMailAvailable(mail)
SELECT COUNT(*) FROM `authentication` WHERE `auth_mail` = ? LIMIT 1

-- 4. RegisterUser(id,mail,salt,pass,salt)
INSERT INTO `authentication`(`user_id`, `auth_mail`, `auth_pass`, `auth_salt`)
VALUES (?, ?, MD5(CONCAT(?,?)), ?)

The usual requests fall into six main categories:

1. SELECT requests extract an array of rows (which in turn will be represented in PHP as hashes mapping column names to column values).
2. One-row SELECT requests, which return a single row (or null, if there was none).
3. SELECT COUNT requests, which work the same way as one-row requests.
4. INSERT requests, which return the last insertion identifier (to know what has just been inserted);
5. UPDATE requests, which return nothing.
6. DELETE requests, which return nothing.

The request-making process is, in terms of code, quite long. Once you have connected to the database, you need to construct the request, escape the arguments, retrieve the result (with a failure, if need be), process the result, and you finally get the data. Or, if you’re using the improved database interface, you will be preparing a statement, feeding it the input and output variables, running it, and traversing the result set.

All of this can be abstracted away behind a single function call with the following semantics:

$query = '`user_id` FROM `authentication` ' .
         'WHERE `auth_mail` = ? ' .
         'AND `auth_pass` = MD5(CONCAT(`auth_salt`, ?))';

$slave = DBConfig::Slave();

$id = DBUtil::SelectOne($slave, $query, 'ss', $login, $password);

You use the appropriate member of DBUtil (DBUtil::Select, DBUtil::SelectOne, DBUtil::Count, DBUtil::Insert, DBUtil::Update, DBUtil::Delete), specify the target database, specify the query to be performed, the type and number of arguments (if any), and the arguments for the query (they will be automatically escaped). The function runs the query, aborts the script with an error if it fails, and returns the appropriate kind of data. This reduces the specification of database requests to the bare minimum.

In the real world, high-traffic websites tend to have performance issues. Since one cannot improve the quality of hardware forever, and there is only so much that can be done to improve the speed of the code, a classic approach to optimization is concurrency: instead of a single server responding to requests, use two servers. In theory, this would double the performance of your system. And this is actually the case when your server is merely about distributing static read-only data. In practice, servers have to support write operations, and write operations need to be synchronized (or else you end up with two users that have the same user identifier, and that would be bad). The problem is that synchronization takes time, and tends to use locks (which end up clogging read-only requests as well). The solution is to use a master/slave database setup: a master database handles all write operations, as well as read operations that have to be done within transactions with write operations, and then dispatches the result to slave databases that are queried by standalone read-only scripts. While this does not allow you to scale write requests, it at least lets you scale read requests.

The implementation approach is quite classic: a single private function implements the entire query mechanism, and the six public members merely call this function with the appropriate arguments and reprocess the underlying query. But first, we need to know what our databases are, which means we have to design our DBConfig class first:

<?php // config/db.php

  class DBConfig
  {
      private static $master = null;

      public static function Master()
      {
          if (self::$master === null) {
              self::$master = new mysqli("localhost", "user_jitbrain", "password", "jitbrain");
          }

          return self::$master;
      }

      public static function Slave()
      {
          return self::Master();
      }
  }

Right now, we have no actual master/slave configuration, so we really just redirect all slave requests to the master. This configuration class merely contains the information required for connecting to the master database, along with some classic lazy initialization (so that the connection only happens once). If the connection fails, an error happens and is appropriately handled by the error system. Since it is non-recoverable, we do not attempt to handle it in a clean fashion.

The next step is the database utility class for performing requests:

<?php // utils/db.php

  class DBUtils
  {      
      private static function _query(mysqli $mysqli, $query, $input)
      {
          $statement = $mysqli->prepare($query);

          if (!$statement) {
              throw new exception($mysqli->error);
          }

          array_shift($input);
          array_shift($input);

          if (count($input) > 1)
              call_user_func_array(array($statement, 'bind_param'), $input);

          $statement->execute();

          $results = array();
          $row = array();
          $meta = $statement->result_metadata();
          while ($meta && $field = $meta->fetch_field()) {
              $row[$field->name] = null;
              $results[] = &$row[$field->name];
          }

          if (count($results) > 0) {
              if (count($results) == 1) {
                  $row = null;
                  $statement->bind_result($row);
              }

              else {
                  call_user_func_array(array($statement, 'bind_result'), $results);
              }

              $output = array();

              while ($statement->fetch()) {
                  $output[] = $row;
              }
          }

          else {
              $output = $statement->insert_id;
          }

          $statement->close();
          return $output;
      }

      public static function Begin(mysqli $sql)
      {
          $sql->query('START TRANSACTION');
      }

      public static function Commit(mysqli $sql)
      {
          $sql->query('COMMIT');
      }

      public static function Rollback(mysqli $sql)
      {
          $sql->query('ROLLBACK');
      }

      public static function Select(mysqli $sql, $query, $types = '')
      {
          $args = func_get_args();
          return self::_query($sql, "SELECT " . $query, $args);
      }

      public static function SelectOne(mysqli $sql, $query, $types = '')
      {
          $args = func_get_args();
          $data = self::_query($sql, "SELECT " . $query . " LIMIT 1", $args);

          if (count($data) > 0)
              return $data[0];
          else
              return null;
      }

      public static function count(mysqli $sql, $query, $types = '')
      {
          $args = func_get_args();
          $data = self::_query($sql, "SELECT COUNT(*) FROM " . $query, $args);

          return $data[0];
      }

      public static function Update(mysqli $sql, $query, $types = '')
      {
          $args = func_get_args();
          self::_query($sql, "UPDATE " . $query, $args);
      }

      public static function Delete(mysqli $sql, $query, $types = '')
      {
          $args = func_get_args();
          self::_query($sql, "DELETE " . $query, $args);
      }

      public static function Insert(mysqli $sql, $query, $types = '')
      {
          $args = func_get_args();
          return self::_query($sql, "INSERT INTO " . $query, $args);
      }

      public static function Query(mysqli $sql, $auery, $types = '')
      {
          $args = func_get_args();
          return self::_query($sql, $query, $args);
      }
  }

As advertised, we have six specialized query functions that prepend and append the necessary elements to the query, a generic query function, and a private query function that performs the actual voodoo based on prepared statements.

What does a query return?

If it returns several columns, it will return an array of rows, where the rows are hashes. If it returns a single column, it will return an array of values. If it returns nothing, it will return the last insert id (which means nothing in most cases, so don’t worry).

With this database utility, we can write down the queries in our authentication model:

<?php // models/authentication.php

  class AuthenticationModel
  {
      public static function Login($mail, $pass)
      {
          $query =
          '`user_id`
           FROM  `authentication`
           WHERE `auth_mail` = ?
           AND   `auth_pass` = MD5(CONCAT(`auth_salt`, ?))';

          return DBUtils::SelectOne(DBConfig::Slave(), $query, 'ss', $mail, $pass);
      }

      public static function isMailAvailable($mail, $transaction = false)
      {
          $query = '`authentication` WHERE `auth_mail` = ?';

          $database = $transaction ? DBConfig::Master() : DBConfig::Slave();

          $count = DBUtils::count($database, $query, 's', $mail);

          return($count == 0);
      }

      // Returns user identifier, null if the mail is already
      // registered for another user.
      public static function Register($mail, $name, $pass)
      {
          $create_user_query = '`user`(`user_name`) VALUES (?)';

          $register_user_query = 
          '`authentication`(`user_id`, `auth_mail`, `auth_pass`, `auth_salt`)
           VALUES (?, ?, MD5(CONCAT(?,?)), ?)';

          $salt = md5(uniqid());
          $salt = substr($salt, 0, 2);

          $master = DBConfig::Master();

          DBUtils::Begin($master);

          if (!self::isMailAvailable($mail, true)) {
              DBUtils::Rollback($master);
              return null;
          }

          $user = DBUtils::Insert($master, $create_user_query, 's', $name);
          DBUtils::Insert($master, $register_user_query, 'issss',
                          $user, $mail, $salt, $pass, $salt);

          DBUtils::Commit($master);

          // see "self::GetUser"
          $token = $user;
          return $token;
      }

      public static function GetUser($token)
      {
          // Right now, tokens are users.
          return $token;
      }
  }

All that is left to do is install the database and watch things run (although arguably, there is still not much to see, yet).

An interesting thing above is the nature of the IsMailAvailable function, which takes a second “transaction” argument. What for? If we call the function because we need to know if, at the present time, the address is available, we probably don’t care about having fresh information (after all, it will take at least a few milliseconds before we submit a registration request) so we can query the slave and gain scalability benefits. If we call the function because a certain process cannot succeed otherwise, we need the freshest possible value, so we query the master. The registration function does exactly that: it early-aborts the process if it determines that the e-mail is taken, and if it isn’t then the transaction is guaranteed to keep anyone else from modifying the value until the user is safely registered.

Transactional code will always look the same: you begin a transaction, check the prerequisites and rollback if they fail, run the updates, and commit. In theory, you don’t need a lot of transactions, because few things have to happen synchronously (even better, if you can manage to avoid working on the master database, you can usually accept stale data and thus avoid the overhead of transactions entirely).

← POST Requests | Delayed Requests →