I have a table where all columns need to be encrypted (aside from the PK). This will be achieved using AES at the application level. However there is one particular field that needs to remain searchable.

For example a Message table has:

  • MessageTitle (Encrypted)
  • MessageBody (Encrypted)
  • SenderID (Encrypted but matchable)

Given a plainText SenderID I should be able to select all the related messages.

A few constraints / decisions:

  1. I do need to encrypt the data at rest, including this column. I have no choice.
  2. The SenderID is of a fixed format, so if I hashed the value it wouldn't be hard to brute force
  3. I have chosen to encrypt specific fields rather than the whole database (TDE) to mitigate threats other than just the disks being stolen.

Currently my preferred solution is to encrypt the SenderID (and only the SenderID) with a fixed IV. This way the data remains encrypted but we can still match on it in queries.

A downside to this approach is that plaintext attacks could occur on the data; DBAs could create records with different SenderIDs and try to find a matching record. However I feel this can be mitigated at the Encryption Service with suitable access control and monitoring.

Does this seem like a valid solution?

  • Why bother encrypting it at all then? Jul 16, 2013 at 12:10
  • Isn't "encrypting the SenderID with a fixed IV" the same thing as hashing the field ? Well, except for the "revertible" part, of course, but mostly regarding the security properties, it seems to me that you'd be better with a proper crypto hash function
    – Stephane
    Jul 16, 2013 at 12:31
  • What do you mean by "searchable" exactly ? Do you mean you need to be able to find an exact match from a given input (cleartext, I assume) or do you need to be able to get something else out of it (order, partial matching)
    – Stephane
    Jul 16, 2013 at 12:33
  • If the SenderID was plaintext you would know that a message had been sent by a particular user even if you couldn't read the content. For our threat model this is unacceptable data leakage. Jul 16, 2013 at 12:36
  • @Stephane, yes sorry for the confusion. I mean the field needs to be matchable. No sorting, ordering or partial searches required. And yes, we'll have the SenderID in plaintext to execute the search. Jul 16, 2013 at 12:39

2 Answers 2


Given what you've told us, I would still use a hash function: that's exactly what they have been designed for.

You cannot salt your hash value, unfortunately, since you want to be able to find a specific record based only on the cleartext value but you can still used a HMAC algorythm to protect against direct attacks: an attacker will need to have access to the key used in the HMAC computation in order to derive a hash from a given cleartext: assuming you're storing your keys properly (probably the most critical issue in such an application), your SenderID will remain hidden but you'll be able to find it at any time by a simple lookup.

Using a HMAC will also help you if your SenderID has low entropy (although it will not solve THAT issue completely).

Please note that anyone who has (or can gain) access to the key used in your HMAC calculation will be able to try to match given cleartext with your data but that is something that cannot be helped, I think.

Be aware, though, that, while you can (and should) have a different key on each installation, you will NOT be able to change that key afterward or you will lose the ability to find your records. One way around this is to add an encrpyted version of the SenderID within your record: in you need to re-key everything, you can use it to recompute a new hash.

I would, however, suggest that you use a strong, modern hash function for your HMAC. (SHA-3, for instance, even if the final specs haven't been published yet)

  • Thanks Stephane this is an interesting, I briefly considered HMAC but didn't put much time into researching the option. I'll take another look. Jul 16, 2013 at 13:53
  • This does look like a good solution, as you say it was designed for this purpose and is obviously better than simple hashing. Could you provide any information on why the HMAC approach is more secure than a Fixed IV encryption approach? Jul 17, 2013 at 8:50
  • First, a hash/HMAC is supposed to be irreversible. That means that you can actually leak the hash without putting the data it was derived from at risk. Second, you're using standard algorythm in a standard way instead or trying to reinvent your own system: your not placing yourself in a situation where you're introducing new risks because you do not fully understand the consequences of what you're doing (which is always the risk with encryption and security systems).
    – Stephane
    Jul 17, 2013 at 9:16
  • This is the solution I'm going to use. Thanks for your help. Jul 18, 2013 at 8:42

If it's a hash you can compute the hash and search using that value since the same phrase will make the same hash value. You may not necessarily need to salt this hash since I'm assuming the SenderID is unique but salting is never a bad idea. Also why have just a senderID what about password? If you need to make the message body searchable you would have to get the encrypted contents back, decrypt it then look for a match.

For example the word bob will always compute to 9f9d51bc70ef21ca5c14f307980a29d8 using md5 so if the senderID happens to be bob the user will input bob. Your code will hash that string and get 9f9d51bc70ef21ca5c14f307980a29d8 then select * from email where senderid='9f9d51bc70ef21ca5c14f307980a29d8'

  • My concern with this method, is that the SenderID follows a fixed format, so I don't imagine it would take very long to brute force the values even with a "slow" hash. Jul 16, 2013 at 12:46
  • In this case SenderID just identifies a person. It's entirely separate from username/password authentication. Jul 16, 2013 at 12:47
  • @DanRowlands the amount of time it will take to break the hash will depend on the value used. If you are just using integers then it will take no time at all. If you salt those integers it may take longer. If you really want to ratchet up the entropy use a GUID rather than an integer. Jul 16, 2013 at 12:57
  • Unfortunately in my real world problem the SenderID equivalent is a 10 digit integer. Jul 16, 2013 at 13:01
  • That is a problem. I would salt it then. Make a table where you can look up a salt by senderID then mix the salt and senderID to get the salted hash. Jul 16, 2013 at 13:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .