I was hoping to get some advice on a particular task I'm trying to implement.

I have a table that stores secure data and returns an ID as a representation of that data. No problems there. So, for example, if a social security number is stored, the code generates a representational ID and stores the social security number in an encrypted fashion in the table. The encryption is done using envelope encryption.

Here's my issue. Every time a new value comes in, I don't want to create a new ID if the data already exists. I need to check to see if the value already exists and, if so, return the existing ID. The problem I have is that the encrypted value is different each time and I certainly can't decrypt every value in the database to check for a duplicate. I could create a one way hash and store that as well but, if I do, I would need to salt it for security purposes and the hash will be different every time.

So I'm hoping to get advice/recommendations on how to achieve this? How to check for duplicates when the value is stored in an encrypted fashion.

Thank you!

  • The usual method as you say is to use a one-way hash. The same value should produce the same hash - which you simply compare (indexing the hashes if necessary). I can't see why you need to use a function that produces a different hash each time for the same value - properly hashed values are secure. – Steve Jan 4 '18 at 18:54
  • @Steve hashes are not reversible (are not simetric). I guess the value is encrypted because they want to be able to keep It hidden and recoverbale. As you commented, the solution would take just a new column to store the hash and compare hashes during the validation. Which algo. depends on the needs. I encourage you to write an answer, I think your solution is both simple and functional. – Laiv Jan 4 '18 at 19:24
  • 1
    @Steve, there are only a billion possible social security numbers, so a rainbow table can be created with relatively modest resources. If you have a different random salt per row, the effort to create a rainbow table multiplies by the number of rows. – Karl Bielefeldt Jan 4 '18 at 21:24
  • 1
    @KarlBielefeldt, I see your point. Maybe you'll have to accept inserting duplicates as the price of security, and occasionally run a de-duplication job which does decrypt all values and identifies any duplicates that have accumulated. – Steve Jan 4 '18 at 21:56

Since a single hash of a value can be vulnerable to brute force, rainbow tables, or even a simple Google search for the hashed value and using a unique salt per row does not allow you to perform keyed lookups you should consider if a keyed hash (HMAC) would meet your needs.

With an HMAC you need to generate a cryptographically random key that is not stored alongside the data, but can be accessed by the application.

You then use the hash_hmac function to calculate the HMAC of a given identifier value (using the key generated from above). That is a unique value that you can then store alongside the data and use to determine if you have already inserted a row with that value before.

You should ensure that you select a strong hashing function (such as SHA256) to ensure that the HMAC is as strong as possible.

Using an HMAC will mitigate the risk of information disclosure via brute force/rainbow tables as long as your secret key is sufficiently random and remains secret. You should protect that key just as you are protecting your encryption keys.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.