For searching/indexing purposes, is there an easy/clean way to do partial field encryption with MySQL?
For example, using built in features like AES_ENCRYPT() or any other equivalent?
Examples:
As a worse case, I could do a substring, followed by encryption on the desired part then concatenation of the data. I could then do a similar process for decryption. This seems like a lot of work, for something that I hope is built in somewhere.
My googles have not paid off. Does anyone know of a way to do this cleanly?
In the area of payment card processing, one ordinarily maintains separate columns for the last four digits of the card number, and for the encrypted token representing the entire card number.
The duplication of data is minimal.
The last four digits may be kept in cleartext, to help the customer know which payment card is being used. The user's name, address, and the card's expiration date can also be stored in cleartext.
The card verification code (the three- or four- digit code usually shown on the back of the card) must never, under any circumstances, be stored in any form whatsoever once the card is verified.
It's important to understand that symmetric encryption (like AES) in your database is a weak defense against cybercriminals. It's so weak as to be almost useless. That's because your web application software must have the key readily available to encrypt new information. Many attacks on online systems come in through the web application, so the cybercriminal can easily grab the key. Then he has your card data.
If you collect or store payment card numbers (credit card numbers) your business becomes subject to the Payment Card Industry (PCI) Data Security requirements. In particular, you'll eventually have to fill out this Self-Assessment Questionnaire https://www.pcisecuritystandards.org/documents/SAQ_A-EP_v3-1.pdf . This will, in the first year, cost about $50,000 US to do, and in each subsequent year about half that amount.
On the other hand, working through a payment processor like stripe.com or Paypal leverages their PCI Data Security requirements and reduces your exposure.
So think twice about whether you want to collect this information, and talk to your accountant or CFO.
If you must store payment card information, you might consider using an asymmetric (public / private) encryption mechanism. To do this safely, set up a dedicated machine, and generate a key pair (RSA, perhaps) on that machine. Export the public key to your web application, and do not copy the private key. Then switch off the dedicated machine and its disk drives, and disconnect it from the network. (Open it up and remove the wifi and bluetooth adapter cards, and then unplug the ethernet cable.)
Encrypt the cards using the public key on your web application and store them in your database. When you need to use the payment card numbers to process payments or some such thing, you can temporarily switch on the dedicated machine and use it to do your processing.
If you have credit card numbers your system becomes a juicy target for cybercriminals. This all may seem paranoid until you realize that competent, motivated, and unscrupulous people are in fact plotting against you.