Blind Index: Searching an Encrypted Column Without Decrypting

A blind index lets you search an encrypted column without decrypting it. You store a keyed digest of the value next to the ciphertext and search the digest. It works well for equality, and it costs you some privacy.

The problem: ciphertext cannot be searched

Picture a support agent saying, “I need the customer with this email address.” The emails are encrypted, so you cannot write WHERE Email = ‘x’. Encryption adds random bytes, so the same email gives a different ciphertext every time. Decrypting every row for every lookup is slow and puts plain text in memory.

A blind index solves this with a second column. It holds a fixed-size digest of the normalized email, mixed with a secret. You index that column and search it.

Build the table with a digest beside the cipher

The demo uses temp tables. One holds the secret, which I keep out of the data table on purpose. The other holds the ciphertext and the digest, with an index on the digest.

DROP TABLE IF EXISTS #BlindKey;
DROP TABLE IF EXISTS #BlindDemo;
CREATE TABLE #BlindKey (Secret varbinary(32) NOT NULL);
INSERT #BlindKey VALUES (CRYPT_GEN_RANDOM(32));

CREATE TABLE #BlindDemo (Id int PRIMARY KEY, Cipher varbinary(8000) NOT NULL, Digest binary(32) NOT NULL);
CREATE INDEX IX_BlindDemo_Digest ON #BlindDemo (Digest);

Now four rows. Every email is trimmed and lowercased before it is encrypted and hashed. Rows 3 and 4 hold the same address, typed in different cases.

DECLARE @Secret varbinary(32) = (SELECT Secret FROM #BlindKey);

INSERT #BlindDemo (Id, Cipher, Digest)
SELECT v.Id, ENCRYPTBYPASSPHRASE(N'Demo phrase', n.Email),
       HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + CONVERT(varbinary(max), n.Email))
FROM (VALUES (1, N' Sample@Example.test '),
             (2, N'other@example.test'),
             (3, N'repeat@example.test'),
             (4, N'REPEAT@example.test')) AS v (Id, RawEmail)
CROSS APPLY (SELECT LOWER(TRIM(v.RawEmail)) AS Email) AS n;

A word of warning. HASHBYTES is a plain hash, not a native HMAC. This construction teaches the idea. For production, let your application compute a reviewed keyed hash with a proper library.

Search without decrypting every row

To search, apply the same steps to the input and compare digests. Only matching rows get decrypted, to confirm the candidate. The screenshot shows one row, with a 32-byte digest.

DECLARE @Secret varbinary(32) = (SELECT Secret FROM #BlindKey);
DECLARE @Input nvarchar(320) = LOWER(TRIM(N'sample@example.test'));
DECLARE @Search binary(32) = HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + CONVERT(varbinary(max), @Input));

SELECT Id, CONVERT(nvarchar(320), DECRYPTBYPASSPHRASE(N'Demo phrase', Cipher)) AS Candidate,
       DATALENGTH(Digest) AS DigestBytes
FROM #BlindDemo
WHERE Digest = @Search
ORDER BY Id;

Normalize first, or miss the row

The digest of “Sample@Example.test ” and the digest of “sample@example.test” are completely different. If a search skips the trimming and lowercasing, it finds nothing and nobody gets an error. The query below runs three searches: normalized, raw and a prefix.

DECLARE @Secret varbinary(32) = (SELECT Secret FROM #BlindKey);
DECLARE @Raw nvarchar(320) = N' Sample@Example.test ';
DECLARE @Prefix nvarchar(320) = N'sample@';

SELECT (SELECT COUNT(*) FROM #BlindDemo
        WHERE Digest = HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + CONVERT(varbinary(max), LOWER(TRIM(@Raw))))) AS NormalizedInput,
       (SELECT COUNT(*) FROM #BlindDemo
        WHERE Digest = HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + CONVERT(varbinary(max), @Raw))) AS RawInput,
       (SELECT COUNT(*) FROM #BlindDemo
        WHERE Digest = HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + CONVERT(varbinary(max), @Prefix))) AS PrefixSearch;

Only the normalized input finds a row. The raw input and the prefix both find none. So a blind index supports equality only. Write the normalization rule down once. Every insert, search, backfill and rotation must follow it. NULLs and empty strings need their own rule.

What the digest gives away

Now the price. Rows 3 and 4 hold the same address. Their ciphertexts differ, but their digests are equal. Anyone who can read the table can see that two rows share a value, without decrypting anything.

SELECT COUNT(DISTINCT Cipher) AS DistinctCiphertexts, COUNT(DISTINCT Digest) AS DistinctDigests
FROM #BlindDemo
WHERE Id IN (3, 4);

SELECT COUNT(*) AS RowsSharingOneDigest
FROM #BlindDemo
GROUP BY Digest
HAVING COUNT(*) > 1;

Two ciphertexts, one digest, and one group of two rows. Counting repeats is easy. That is the equality leak. If someone steals the secret, they can also test guessed emails against the digests. So keep the secret away from people who can read the table, and never export digests as handy public ids.

Rotating the secret means decrypting every row

Sooner or later the secret changes. Every digest then becomes stale. The search finds nothing until you rebuild each digest, and that needs the plain value, so each row must be decrypted once. Plan this as a batch job, with a version column or a second digest column during the switch.

UPDATE #BlindKey SET Secret = CRYPT_GEN_RANDOM(32);
GO
DECLARE @Secret varbinary(32) = (SELECT Secret FROM #BlindKey);
DECLARE @Search binary(32) = HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + CONVERT(varbinary(max), N'sample@example.test'));

SELECT COUNT(*) AS MatchesAfterRotation FROM #BlindDemo WHERE Digest = @Search;

UPDATE #BlindDemo
SET Digest = HASHBYTES('SHA2_256', CONVERT(varbinary(max), @Secret) + DECRYPTBYPASSPHRASE(N'Demo phrase', Cipher));

SELECT COUNT(*) AS MatchesAfterBackfill FROM #BlindDemo WHERE Digest = @Search;
GO
DROP TABLE IF EXISTS #BlindDemo;
DROP TABLE IF EXISTS #BlindKey;

Right after the rotation there are no matches. After the backfill the row is back. Old backups still carry the old digests, so include them in your plan.

Before you ship a blind index, decide how much equality you are willing to show.

A blind index is not hidden data, it is an equality clue you must protect.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.