How to Search on Securely Encrypted Database Fields

We [ParagonIE] get asked the same question a lot (or some remix of it).

This question shows up from time to time in open source encryption libraries' bug trackers. This was one of the "weird problems" covered in my talk at B-Sides Orlando (titled Building Defensible Solutions to Weird Problems), and we've previously dedicated a small section to it in one of our white papers.

You know how to search database fields, but the question is, How do we securely encrypt database fields but still use these fields in search queries?

Our secure solution is rather straightforward, but the path between most teams asking that question and discovering our straightforward solution is fraught with peril: bad designs, academic research projects, misleading marketing, and poor threat modeling.

If you're in a hurry, feel free to skip ahead to the solution.

Towards Searchable Encryption

Let's start with a simple scenario (which might be particularly relevant for a lot of local government or health care applications):

• You are building a new system that needs to collect social security numbers (SSNs) from its users.
• Regulations and common sense both dictate that users' SSNs should be encrypted at rest.
• Staff members will need to be able to look up users' accounts, given their SSN.

Let's first explore the flaws with the obvious answers to this problem.

Insecure (or otherwise ill-advised) Answers

Non-randomized Encryption

The most obvious answer to most teams (particularly teams that don't have security or cryptography experts) would be to do something like this:

<?php
class InsecureExampleOne
{
    protected $db;
    protected $key;

    public function __construct(\PDO $db, string $key = '')
    {
        $this->db = $db;
        $this->key = $key;
    }

    public function searchByValue(string $query): array
    {
        $stmt = $this->db->prepare('SELECT * FROM table WHERE column = ?');
        $stmt->execute([
            $this->insecureEncryptDoNotUse($query)
        ]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    protected function insecureEncryptDoNotUse(string $plaintext): string
    {
        return \bin2hex(
            \openssl_encrypt(
                $plaintext,
                'aes-128-ecb',
                $this->key,
                OPENSSL_RAW_DATA | OPENSSL_ZERO_PADDING
            )
        );
    }
}

In the above snippet, the same plaintext always produces the same ciphertext when encrypted with the same key. But more concerning with ECB mode is that every 16-byte chunk is encrypted separately, which can have some extremely unfortunate consequences.

Continue reading %How to Search on Securely Encrypted Database Fields%

by Scott Arciszewski via SitePoint