How to Search Encrypted Database Fields Securely with Blind Indexes

When storing highly sensitive data such as Social Security Numbers (SSN) in a database, encryption is mandatory, but naive deterministic schemes (e.g., AES‑128‑ECB) leak patterns because identical plaintexts produce identical ciphertexts.

Insecure example (do not use):

<?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
            )
        );
    }
}
?>

Secure designs must use nondeterministic (randomized) authenticated encryption so that the same plaintext encrypted with the same key yields different ciphertexts, preventing pattern leakage and chosen‑ciphertext attacks.

Secure encryption search becomes simple

Authenticated encryption schemes such as XSalsa20‑Poly1305, XChaCha20‑Poly1305, or NORX64‑4‑1 provide ciphertexts that are indistinguishable from random noise and are protected by authentication tags.

However, searchable encryption still requires a way to locate rows without decrypting every record. The recommended solution is a blind index : store a deterministic hash (e.g., HMAC or a key‑derived hash using PBKDF2, scrypt, Argon2) of the plaintext in a separate column. The blind index key is different from the encryption key and never reaches the database server.

Implementing character search on encrypted data

Example PostgreSQL schema with a blind index column:

CREATE TABLE humans (
    humanid BIGSERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    ssn TEXT,          -- encrypted
    ssn_bidx TEXT       -- blind index
);
CREATE INDEX ON humans (ssn_bidx);

PHP helper functions for encryption, decryption, blind‑index generation, and lookup:

<?php
/* This is not production‑grade code; it prioritises readability. */
function encryptSSN(string $ssn, string $key): string {
    $nonce = random_bytes(24);
    $ciphertext = sodium_crypto_secretbox($ssn, $nonce, $key);
    return bin2hex($nonce . $ciphertext);
}
function decryptSSN(string $ciphertext, string $key): string {
    $decoded = hex2bin($ciphertext);
    $nonce = mb_substr($decoded, 0, 24, '8bit');
    $cipher = mb_substr($decoded, 24, null, '8bit');
    return sodium_crypto_secretbox_open($cipher, $nonce, $key);
}
function getSSNBlindIndex(string $ssn, string $indexKey): string {
    return bin2hex(
        sodium_crypto_pwhash(
            32,
            $ssn,
            $indexKey,
            SODIUM_CRYPTO_PWHASH_OPSLIMIT_MODERATE,
            SODIUM_CRYPTO_PWHASH_MEMLIMIT_MODERATE
        )
    );
}
function findHumanBySSN(PDO $db, string $ssn, string $indexKey): array {
    $index = getSSNBlindIndex($ssn, $indexKey);
    $stmt = $db->prepare('SELECT * FROM humans WHERE ssn_bidx = ?');
    $stmt->execute([$index]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>

The blind‑index approach works for exact matches. It leaks minimal metadata (e.g., whether two rows share the same SSN) but avoids exposing plaintext. Limitations include inability to perform range or partial matches without additional indexes.

Fuzzy search on encrypted data

For partial or case‑insensitive searches, create additional blind‑index columns in a separate table (e.g., humans_filters) that store transformed values such as lower‑cased substrings or first letters.

CREATE TABLE humans (
    humanid BIGSERIAL PRIMARY KEY,
    first_name TEXT,   -- encrypted
    last_name TEXT,    -- encrypted
    ssn TEXT           -- encrypted
);
CREATE TABLE humans_filters (
    filterid BIGSERIAL PRIMARY KEY,
    humanid BIGINT REFERENCES humans(humanid),
    filter_label TEXT,
    filter_value TEXT
);
CREATE INDEX ON humans_filters (filter_label, filter_value);

Each filter column uses its own blind‑index key, allowing queries like “first letter of last name” or “first three letters of surname” while keeping the underlying plaintext hidden.

Time and storage trade‑offs

Blind indexes can be truncated (e.g., to 16, 32, or 64 bits) and treated as Bloom filters to reduce storage and improve performance, at the cost of occasional false positives that are resolved by decrypt‑and‑verify on the application side.

Overall, the solution balances security, performance, and developer effort, but it is only suitable for exact‑match queries and requires careful key management and separation of application and database servers.