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.