Finding a patient by an encrypted email address

New in Symfony 8.2 (in development). With #[Encrypted], a column is encrypted in the database, and Doctrine decrypts it when it reads the entity back. But encryption is randomized: the same address, encrypted twice, never gives the same bytes. A WHERE email = ? therefore never finds anything, and a patient can no longer be found by their address. The blind index solves this.

The idea

Next to the encrypted column, you keep a keyed fingerprint of the value, an HMAC-SHA256: the same address always gives the same fingerprint, but computing it takes the key. Whoever reads the database sees fingerprints, with no way to link them to an address; they do see, however, which patients share one, which encryption alone used to hide. So you only index varied values looked up by equality: an address, a file number, not a country or a birth year. To search, you compute the fingerprint of the requested address and compare it with those in the database.

Since Symfony 8.2

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

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

#[BlindIndexed] says where the fingerprint comes from: from the email property, through the index named patient-email. A listener recomputes it on every flush() that writes the entity, on insert as well as on update. You don't have to think about it, and there is no risk of a row whose fingerprint was never written, which no search would ever find.

The index is a service, declared with its data key and its projection, the form of the value it indexes. The Email projection normalizes the domain, the only part of an address the standard declares case-insensitive: ada@Example.ORG and ada@example.org then share a fingerprint. The part before the @ keeps its case: Ada@example.org is another address. If your application must treat the two as one, normalize the address where it comes in, before saving it: that is not the index's call.

The search goes through this index, which computes the fingerprint of the searched address:

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

The listener only sees what goes through flush(): a row inserted in SQL has no fingerprint, and a row changed by a bulk UPDATE keeps its old one, which then points to an address it no longer holds.

Your task

The patients' addresses are already encrypted, and config/services.yaml declares the patient-email index. But PatientRepository::lookUpByEmail() still searches the encrypted column, so the /patients/search preview finds nobody. It also shows, under inDatabase, what the database holds.

1. Add the emailIndex column to Patient, filled by the patient-email index.
2. Make lookUpByEmail() search by the fingerprint of the address.

The address must stay encrypted.