So, we've been using the standard ColdFusion Encrypt/Decrypt functions in our development (AES/CBC/PKCS5Padding), and I'm wondering how we're going to manage string comparisons in our queries. There are a few fields (First Name, Last Name) that we need to be able do partial string matches against, and I'm really unsure about the best way to do this.
1) Is there a way to use AES_DECRYPT in mysql for this? Seems like a likely candidate, but I'm not sure how to handle the CBC/PKCS5Padding aspect of the encryption algorithm if so.
2) We could cache a decrypted and slim (i.e. just first name, last name, and an id) query to do queries against, but something feels weird about that.
3) We support matching the first n letters, and store an encrypted version of those in the record? E.g. If the name is SMITH and we want to support matching on the first three characters, we'd store encrypted versions of 'S', 'SM', 'SMI'. This seems weird too.
4) We could not support partial string matches. This is obviously the least desirable of the three.
We're running mySQL 5 and we'll soon be moving our DB onto a machine separate from our CF Server.
This seems like a common enough problem to have a relatively standard answer, but I can't seem to find anything. Thanks for the help!
AdChoices