Reasonable security through architecture

As a web developer, I am often asked to build pages to collect basic users’ data. The first goal of every advertising campaign, for example, is to collect a large number of emails or phone numbers from people who could be interested in buying some service / product, in order to call them back, contact them, whatever.

For many years, sadly but truly, nobody has really cared about how those data were stored in databases. They haven’t been considered “too sensitive” data, still their privacy should have been granted in some way.

It was a grey area nobody has ever paid too much attention to.

For many years Earth biggest companies haven’t even cared about encrypting passwords of the users, but this is another, possibly even worse, story. If you are interested in some speculation about password encryption and PHP, here’s another article.

So guess how much attention those companies (and smaller ones) have been paying to less sensitive information such as emails or phone numbers.

Today, after many leaks from famous and less famous companies, and after that GDPR kicked in here in Europe, some companies (most of them, to be honest) are starting to be aware of the risks related to storing a large amount of users’ data without any encryption.

A typical bad scenario, which I found in many old-school projects, is to collect names, mails and phone numbers and store them, unencrypted, in a database located on the same host of the web server.

This configuration exposes the project to many threats. Especially, if some attackers gain access to database, they can freely retrieve any data of all the users collected by the application.

• A better scenario would be to collect names, mails and phone numbers, encrypt and store them in a database hosted on a server different than the web server.
• The encryption keys should be stored at least on the web server, never on the database.
• The web server should be running a decent Web Application Firewall to keep it as safe as possible.

The prototype that I am sharing with you stores the keys in a configuration file inside the public folder of your web server, to keep things simple and let almost everybody install and test the prototype in a super basic LAMP environment hosting.

A safer approach would be to save the keys in a file outside your public folder, for example. This goal can be reached with little effort for an average experienced developer, and it’s not part of this article.

Other, even safer, options are available if you can spend a little bit more on your project, but this article’s goal is to show how to provide a reasonable safe environment for your small low budget projects, collecting “not too sensitive” data.

Obviously, if you are going to store credit cards, medical information, military intelligence and so on, you shouldn’t rely on this simple architecture.

A basic working prototype made with PHP and MySql

The main problem, when developing an application that encrypts data, is to check or search for those data, once they have been encrypted. We will follow the solution proposed by ParagonIE team in this article, trying to keep things as simple as possible.

I will describe how to build a typical landing page that encrypts private data, but still allows us to check if a user with the same email or phone has already been stored, before saving a new user.

Moreover, we will build a very basic admin panel (for testing purposes only), with a feature that allows us to search for an email or a phone in the database, even if they are encrypted.

Before starting, I am assuming here that you are not using Laravel or any other PHP Framework for your small project, in order to keep this article as generic as possible. Laravel has a pretty built-in encrypter/decrypter that works just out of the box. You can find some information about it in its official documentation and in some interesting article here.

This prototype relies on Sodium crypto libraries, which are included as PHP extensions in currently supported versions of PHP (7.1, 7.2, 7.3). To be sure, check your

phpinfo()

You can download a demo source code of my prototype on github, and preview the final result here. Do not use this prototype as it is in production environments. Use it freely to build your own encryption system and deepen your encryption knowledge.

Basic setup

First of all we are going to build a simple html form that should look like this one below.

We set all form fields as compulsory, using Bootstrap built-in validation. This form will post data to a save.php script. And that’s it.

In /_sql folder you can find a dump of the database, consisting of a single table named “subscribers”. Here’s its structure.

CREATE TABLE subscribers (
  id int(11) NOT NULL,
  first_name varchar(255) DEFAULT NULL,
  last_name varchar(255) DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  phone varchar(255) DEFAULT NULL,
  privacy char(1) DEFAULT NULL,
  source varchar(255) DEFAULT NULL,
  ip varchar(255) DEFAULT NULL,
  email_hash varchar(255) DEFAULT NULL,
  phone_hash varchar(255) DEFAULT NULL,
  creation_date datetime DEFAULT NULL) 
  ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE subscribers
  ADD PRIMARY KEY (id),
  ADD KEY email_hash (email_hash),
  ADD KEY phone_hash (phone_hash);

To save those keys as a text, we need to encode them using

base64_encode()

The table fields named email and phone will store the encrypted data, while email_hash and phone_hash are two indexed fields that we will use later to implement search functions on encrypted data. These two fields will store blind indexes, which are basically a keyed hash (HMAC) of the plain text email and phone number.

We can import the file to our MySql/MariaDB database.

In root folder we can find a little script called generate_key.php.
Its task is to generate two random keys for our project. We only need to run this script once, take note of the keys and get rid of the script from our server.

The scripts uses

random_bytes()

<?php
// Generating your encryption key
$private_key = random_bytes(SODIUM_CRYPTO_SECRETBOX_KEYBYTES);
// Generating your blind index key
$index_key = random_bytes(SODIUM_CRYPTO_PWHASH_SALTBYTES);

To save those keys as a text, we need to encode them using base64_encode() function, as below.

<?php echo base64_encode($private_key);?>
<?php echo base64_encode($index_key);?>

Once we created our keys for the project, we can write them down in our configuration file, located in /includes folder. If you downloaded the source code from git, you can edit line 50 and 51 of config-sample.php and rename the file as config.php. Don’t forget to add your database connection data on line 20–23, too.

<?php 
// generate values using /generate_key.php script
$private_key = ""; // INSERT PRIVATE KEY HERE
$index_key = ""; // INSERT BLIND INDEX KEY HERE

We can now publish the project to our web server and check that everything is working, by trying to submit a new subscription to the system. We should see a new record in the database table, as well as on the backoffice demo page.

The backoffice page shows the encrypted value actually stored in the database for email and phone number on mouse over.

Moreover, if we try to search for an encrypted email among the subscribers, we can find it quickly:

A deeper look at the code

To understand how this prototype works, we need to have a deeper look at our Data Access Object, in /includes/dao.php

getBlindIndex() function gets a string as a parameter, in our case email and phone number, and it creates a blind index using our previously generated blind index key and sodium_crypto_pwhash(). This blind index will be used to perform a search of encrypted values in our database. Keeping encrypted values searchable is an interesting matter, well covered by this article by ParagonIE, where you will also find more advanced solutions.

Here’s the snippet of the function that deals with the creation of blind indexes in our database, to later store the keyed hashes in email_hash and phone_hash table fields

<?php 
public function getBlindIndex($string)
	{
    $index_key = base64_decode($this->index_key);
    return bin2hex(
        sodium_crypto_pwhash(
            32,
            $string,
            $index_key,
            SODIUM_CRYPTO_PWHASH_OPSLIMIT_MODERATE,
            SODIUM_CRYPTO_PWHASH_MEMLIMIT_MODERATE
        )
    );
	}

insert() function writes a new record in subscribers table, encrypting email and phone number, and creating two blind indexes to enable searches on them. Here’s where the encryption happens. The function retrieves the encryption key (l. 47 in dao.php), creates two separate nonces for encrypting email and phone number (l. 50–51), encrypts email and phone number using sodium_crypto_secretbox() (l. 53–54) and encode them for safe saving to database (l. 56–57). Here’s a snippet of the funciton.

<?php
function insert($first_name,$last_name,$email,$phone,$privacy,$source,$ip) {
    if ($first_name.''=='' || $last_name.'' == '' || $email.'' == '' || $phone.'' == '' || $privacy.'' == '') {
      return false;
     } 
     else {
      // create blind index for encrypted searchable fields
      $email_hash = $this->getBlindIndex($email);
      $phone_hash = $this->getBlindIndex($phone);
      // get key for encryption
      $key = $this->key;
      $key = base64_decode($key);
      // get nonce for encryption
      $nonce_email = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);
      $nonce_phone = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);
      // encrypt data
      $email = sodium_crypto_secretbox($email, $nonce_email, $key);
      $phone = sodium_crypto_secretbox($phone, $nonce_phone, $key);
      // encode data for saving into db
      $email = base64_encode($nonce_email . $email);
      $phone = base64_encode($nonce_phone . $phone);
      try {
        $query = $this->db->prepare('INSERT INTO subscribers (first_name, last_name, email, phone, privacy, email_hash, phone_hash, creation_date, source, ip) VALUES (:first_name,:last_name,:email,:phone,:privacy,:email_hash,:phone_hash,:creation_date,:source,:ip)');
        $query->bindParam(':first_name', $first_name);
        $query->bindParam(':last_name', $last_name);
        $query->bindParam(':email', $email);
        $query->bindParam(':phone', $phone);
        $query->bindParam(':privacy', $privacy);
        $query->bindParam(':email_hash', $email_hash);
        $query->bindParam(':phone_hash', $phone_hash);
        $query->bindParam(':creation_date', date('Y-m-d H:i:s'));
        $query->bindParam(':source', $source);
        $query->bindParam(':ip', $ip);
        $query->execute();
        $id = $this->db->lastInsertId();
        return $id;
      }
      catch(PDOException $e) {
        //echo "Error: " . $e->getMessage();
        //exit();
        return false;
      }
     }
  }
?>

Our data access object in dao.php has other methods for listing the records in subscribers and count them, but I will focus on two more methods.

The first one is dec(), the function that we need to decrypt encrypted data. Here’s a snippet

<?php 
public function dec($string) {
    $decoded = base64_decode($string);
    $key = base64_decode($this->key);
    $nonce = mb_substr($decoded, 0, SODIUM_CRYPTO_SECRETBOX_NONCEBYTES, '8bit');
    $ciphertext = mb_substr($decoded, SODIUM_CRYPTO_SECRETBOX_NONCEBYTES, null, '8bit');				
    try  {
      $plaintext = sodium_crypto_secretbox_open($ciphertext, $nonce, $key);
      return $plaintext;
    } 
    catch (Error $ex) {
      return "Cannot decrypt data.";
    } 
    catch (Exception $ex) {
      return "Cannot decrypt data.";
    }
  }
?>

dec() function gets an encrypted string as input, retrieve the encryption key, separate the nonce from the encrypted text and then decrypt it.

This function can be used while we loop our records from subscribers table, to decrypt data in our backoffice demo page, (l. 125–126). Here’s a snippet that shows its very basic usage (paginate your rows or your server will start sweating).

<td>
  <span class="moreinfo" data-toggle="tooltip" title="string stored in database:<?php echo $row['email'];?>">
    <?php echo $dao->dec($row['email']);?>
  </span>
</td>
<td>
  <span class="moreinfo" data-toggle="tooltip" title="string stored in database: <?php echo $row['phone'];?>">
    <?php echo $dao->dec($row['phone']);?>
  </span>
</td>
                                     

Looking at save.php script, we can notice that before saving a new subscriber to database, we check if a user with the same email or phone has already subscribed

// check if mail already exists
$email_hash = $dao->getBlindIndex($email);
if ($dao->exists_email($email_hash)) {
  header("location: index.php?e=email_exists");
  exit();
}

// check if phone already exists
$phone_hash = $dao->getBlindIndex($phone);
if ($dao->exists_phone($phone_hash)) {
  header("location: index.php?e=phone_exists");
  exit();
}

$new_subscriber = $dao->insert($first_name,$last_name,$email,$phone,$privacy,$source,$ip);
if (!$new_subscriber) {
  header("location: error.php");
  exit();
}
else {
  header("location: thankyou.php?id=".$new_subscriber);
  exit();
}

Function exists_mail() in dao.php check the existence of the same email by simply checking if the same blind index exists in email_hash field of the table.
For better performance, we set an index on this email_hash field.
Same logic occurs for exists_phone() function

function exists_email($email) {
	if (!isset($email)) {
		return true;
	}
	try {
		$query = $this->db->prepare("SELECT id  FROM subscribers WHERE email_hash=?");
		$data = Array($email);
		$query->execute($data);
		$all_mails = $query->fetchAll();
		foreach($all_mails as $row) {
			return true;
		}
	} catch (PDOException $e) {
		$this->logger->writeDbError($e->getMessage(),$this);
	}
	return false;
}
  
function exists_phone($phone) {
	if (!isset($phone)) {
		return false;
		exit();
	}
	try {
		$query = $this->db->prepare("SELECT id FROM subscribers WHERE phone_hash=?");
		$data = Array($phone);
		$query->execute($data);
		$all_numbers = $query->fetchAll();
		foreach($all_numbers as $row) {
			return true;
		}
	} catch (PDOException $e) {
		$this->logger->writeDbError($e->getMessage(),$this);
	}
	return false;
}

Once this function is clear, it is pretty straightforward to implement a basic search by email or phone number in our demo backoffice page.
See listItems() function in dao.php for implementation details.
Obviously, the search will give you results only if the full email or full phone number is inputed, while no partial searches are allowed.

Final thoughts and warnings

This article aims at showing how little effort is needed to meaningfully strengthen the privacy of your data in small / medium sized projects.

The prototype described here must be taken as an opportunity to deepen our knowledge of encryption techniques and make it accessible to unexperienced developers and teams.

As stated before, do not use this prototype in production environment, but play with it as much as you want.