Forelse learn
Start

Encrypting columns with Symfony 8.2: what #[Encrypted] protects, and what it doesn't

Arnaud Pointet · · 10 min read

Lire cet article en français

This article starts from an exercise you can do in your browser: Encrypting the sensitive columns of an entity →

Symfony 8.2 brings a new component, KeyManagement, still experimental. Its Doctrine bridge encrypts a column with an attribute, and an encrypted column can even stay searchable thanks to a blind index.

It takes very little code. But encrypting a column is not a box to tick: you need to know what you are protecting against, accept what the database will no longer be able to do, and plan for the day you encrypt a column that already holds data. This article works through those questions, using a table of patients as the example.

Two attributes

Encrypting a patient's phone number and date of birth comes down to two lines, one per column:

use Symfony\Component\KeyManagement\Bridge\DoctrineOrm\Attribute\Encrypted;

#[ORM\Column(nullable: true)]
#[Encrypted(key: 'patient.contact')]
private ?string $phone = null;

#[ORM\Column(type: Types::DATE_IMMUTABLE, nullable: true)]
#[Encrypted(key: 'patient.identity')]
private ?\DateTimeImmutable $birthDate = null;

Doctrine writes an encrypted value to the database, and decrypts it when it reads the entity back. The property keeps its PHP type: the column's Doctrine type converts the value first (a date becomes text), then the result is encrypted. There is no Doctrine type to write and no listener to register: when the container is compiled, Symfony finds the marked properties and declares the types they need.

Encryption is randomized: the same value, encrypted twice, gives two different results. That is deliberate, otherwise comparing the column would be enough to tell which patients share a number. The flip side is that WHERE email = ? no longer finds anything. To find a patient by their encrypted email address, you add a blind index:

#[ORM\Column]
#[Encrypted(key: 'patient.contact')]
private string $email;

#[ORM\Column(length: 64)]
#[BlindIndexed('email', 'patient-email')]
private string $emailIndex = '';

The emailIndex column holds a keyed digest of the address: an HMAC-SHA256, computed under a subkey of its own, derived from the index's key and its name, patient-email. The same address always gives the same digest, but you need the key to compute it, and two indexes never give the same digest for the same value. A listener recomputes it on every flush() that inserts or updates the entity, and the lookup compares digests:

$this->findOneBy(['emailIndex' => $this->index->of($email)]);

What it protects against

Before encrypting anything, ask who you want to stop from reading. Column encryption protects against people who read the database without going through the application:

  • a backup left on poorly protected storage;
  • a copy of the production database on a developer's laptop, or in a staging environment;
  • an SQL injection that dumps the table: it gets encrypted bytes, not phone numbers;
  • read access to the database granted for support or reporting.

It does not protect against the application itself. The application has access to the keys, since it decrypts every time it loads an entity, and holds them in memory for the duration of a request. Someone who takes over the application server, or gets it to run their code, reads the data in clear, just as the application does. And once decrypted, a value is a string like any other: it ends up in the logs if you log the entity, in Sentry with an exception's stack trace, in a cache, in a CSV export or in a search engine. Encryption stops at the database; vigilance has to go further.

That leaves the question of the key. The component uses envelope encryption: each value is encrypted with a data key, which is itself encrypted with a master key that stays in a KMS (AWS KMS, Google Cloud KMS, Vault…). Without any particular configuration, each value carries its own encrypted data key, and every read and every write calls the KMS. With a key store (key_management.store), the encrypted data keys are kept in a table of the database: all the values of a scope (patient.contact) share one key, and the KMS is called once per key and per process. Without the master key, those stored keys are useless.

So where the master key lives decides how much protection you actually get. If the master key sits in the .env file, on the same server that also runs the database backups, someone who gets the whole server gets both. A local KMS with its key in the .env file is fine for development; in production, the master key lives somewhere other than the database, and ideally never leaves the KMS.

What the database can no longer do

An encrypted column is an opaque binary column. The database can no longer do anything with it, and that goes beyond WHERE:

  • No sorting, no partial search. No ORDER BY, no LIKE, no date comparison. That is why, in our patient table, the name stays in clear: the list is sorted by name, in SQL. Only encrypt what needs to be, and keep in mind that an encrypted date of birth no longer lets you look for "patients born before 1950".
  • No uniqueness constraint. Two identical addresses give two different encrypted values, so a UNIQUE index on the encrypted column never rejects anything. Put it on the blind index column instead: two identical addresses have the same digest there, as far as the projection is concerned (see below).
  • No length. The type declares an unbounded binary column (LONGBLOB, BYTEA…), whatever length the mapping says. That length describes the plaintext, and the encrypted result is longer.
  • A value is not bound to its row. The encryption does not know which row it is writing to. Someone who can write to the database can therefore copy one patient's encrypted phone number into the same column of another patient, and it will decrypt without error. If that risk matters to you, bind the value to its row yourself, for example by including the row's identifier in it and checking it after reading.

What the blind index reveals

A blind index does not reveal the addresses, but it does reveal something encryption was hiding: which rows share the same value, and how often each value occurs. For an email address, that is almost nothing, since nearly all addresses are different. For a column with few possible values, it is much worse: a blind index on country of birth or blood type shows the distribution at a glance, and knowing the value of a single row is enough to know the value of every row that carries the same digest. So only index varied values that are looked up by equality: an email address, a file number, a social security number.

Two other points are specific to the blind index.

Its key never changes. With a key store, the data key of an encrypted column is renewed on its own: after thirty days, the next write creates a new one, and the old ones stay readable. This is not hygiene: each value is encrypted with a random 96-bit nonce, and the standard (NIST SP 800-38D) caps a key at 2^32 encryptions in that case. The index key, on the other hand, must never be renewed: every digest already written was computed with it, and a new key would no longer find anyone. Changing that key means recomputing the whole column.

The projection decides what counts as equal. For an email address, you pick the Email projection, which normalizes the domain (its case, and the ASCII form of an internationalized domain) and ignores whitespace around the address: ada@Example.ORG and ada@example.org give the same digest. It leaves the part before the @ alone, since the standard says it is case-sensitive. If your application treats Ada@example.org and ada@example.org as the same person, normalize the address before saving it: the index compares what it is given.

Encrypting a column that already exists

On an empty database, adding the attribute is all it takes. In a real application, the phone column already holds thousands of numbers in clear, and that is where it gets tricky.

Adding #[Encrypted] changes the column's type: the migration Doctrine generates turns it into a binary column, but encrypts nothing. On PostgreSQL, it even fails as generated: you need to add USING phone::bytea (and USING birth_date::text::bytea for a date) for the database to accept the conversion. The numbers stay in clear, and on the first read, Doctrine rejects what it finds:

Doctrine\DBAL\Types\Exception\ValueNotConvertible:
Stored value is not a valid KeyManagement envelope.

Every row that is not encrypted makes loading its entity fail. And you cannot encrypt through the ORM: it cannot load those entities, and even if it could, it would see unchanged values and write nothing. You have to go through DBAL, asking Doctrine for the name of the type Symfony declared for the column:

use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Types\Type;

$metadata = $entityManager->getClassMetadata(Patient::class);
$phone = Type::getType($metadata->getTypeOfField('phone'));
$connection = $entityManager->getConnection();
$platform = $connection->getDatabasePlatform();

foreach ($connection->iterateAssociative('SELECT id, phone FROM patient ORDER BY id') as $row) {
    $connection->update(
        'patient',
        ['phone' => $phone->convertToDatabaseValue($row['phone'], $platform)],
        ['id' => $row['id']],
        ['phone' => ParameterType::BINARY],
    );
}

getTypeOfField() returns the name of the type Symfony declared for that column (here encrypted_string@patient.contact), and that type encrypts exactly the way the application will. For a date, first rebuild the DateTimeImmutable from the stored string, for example with Type::getType('date_immutable')->convertToPHPValue(), then pass it to convertToDatabaseValue(), as the ORM would.

As written, this script is not idempotent: run again, it would encrypt the already encrypted rows a second time. To make it idempotent, skip the rows that Envelope::fromBytes() accepts: a plaintext value is never a valid envelope, and the method throws an exception on it. Finally, between the schema migration and the end of the script, every row that is not yet encrypted is unreadable. There are two ways to handle that.

  • A short maintenance window. Run the schema migration and the script back to back, with the application stopped. It is the simplest option. With a key store, where the KMS is called once per scope, a few hundred thousand rows are encrypted in a few minutes. Without one, each row costs a KMS call, which is slow and billed.
  • A parallel column. Add an encrypted column next to the old one; the application writes to both and reads the old one. A script fills the new one in batches, only touching the rows where it is empty. Then switch reads over, and drop the old column. It takes longer, but nothing stops.

Either way, don't forget the data that is not in the table: backups taken before the migration still hold the numbers in clear, and will for as long as they are kept.

The blind index has the same problem. The listener only computes a digest for the entities a flush() inserts or updates: rows that existed before the column was added keep an empty digest until they are modified, and no search finds them until then. So fill them once, with the same kind of script: read the address back with the encrypted type's convertToPHPValue(), then write $index->of() into the column. The same trap awaits any bulk UPDATE, in SQL or in DQL: it changes the address without changing the digest, and the search then returns a row that no longer has that address.

What I take away from it

#[Encrypted] handles the most technical part: sound encryption, data keys renewed on their own, a master key you can change without rewriting rows, and even move to another provider when the data keys are kept in a store. Writing all that by hand, with a home-made Doctrine type, was a source of mistakes.

But column encryption is still a design decision. It protects the database, not the application; it takes sorting, searching and uniqueness away from the database; and the blind index, which makes a column searchable again, reveals what encryption was hiding. Before adding the attribute, it is worth writing down who you are protecting against, where the master key lives, and how you will encrypt the data that is already there.

To try it without installing anything, two Practice exercises run in your browser: encrypted columns and blind index.

← All articles