On the SitePoint PHP blog today they've reposted an article that was originally posted on the ParagonIE blog about searching encrypted information in database fields from author Scott Arciszewski.
This question shows up from time to time in open source encryption libraries’ bug trackers. This was one of the “weird problems” covered in my talk at B-Sides Orlando (titled Building Defensible Solutions to Weird Problems), and we’ve previously dedicated a small section to it in one of our white papers.
You know how to search database fields, but the question is, How do we securely encrypt database fields but still use these fields in search queries?
Our secure solution is rather straightforward, but the path between most teams asking that question and discovering our straightforward solution is fraught with peril: bad designs, academic research projects, misleading marketing, and poor threat modeling.
They start off with some of the examples of bad ways to perform the searching of encrypted information, mostly around either using poor encryption levels or custom created encryption solutions. With those out of the way, the tutorial moves on to their recommended method: using an authenticated encryption scheme (libsodium) and blind indexing. The key to the method is to use a secondary column for the actual searching process, encrypting the value provided and running the search against that, not the encrypted value itself. The article then covers two questions that need to be asked before putting this method to use. The article ends with a method to enhance the previous searching to allow for "fuzzier" searching through the generation of some additional index values in a joined table.