PII, or Personally Identifiable Information, involves information that may include the following, among other data:

  • First Name
  • Last Name
  • Email Address
  • Phone (any of them)
  • Billing Address
  • Shipping Address

Security researchers and legislation are recommending we encrypt the data. In my case, I'm using PHP7 and MariaDB. So, I can store encrypted PII using the latest recommended and battle-tested encryption APIs. However, now when I need an admin system, I need to be able to:

  • Sort data by PII table columns
  • Do case-insensitive partial keyword searches

Unfortunately, I'm not clear on how to do that smoothly with encrypted data stored in MariaDB (and which might also use PHP7).

  • Speak with your ISO, but I have the impression you will have to plan on row level encryption. I believe there are some services that claim to produce cryptext that lexegrapically ordered the same way as plaintext. Also not all of the address is pii. Consider the zip and even the street most of the time. Work with your iso to clarify how much actually needs to be encrypted. May 23, 2019 at 10:33
  • You dont need to sort data to put into database. Just get the encrypted data loaded into business layer/presentation layer and sort then show.
    – Sazzad Hissain Khan
    May 23, 2019 at 11:08
  • 2
    Databases of encrypted data are a thing, but quite unusual. What are your actual security requirements here? Most organizations are happy with less difficult security measures, such as fine grained access controls, or full disk encryption. If you really need to process the data without having access to the plaintext data, look into research areas such as homomorphic encryption.
    – amon
    May 23, 2019 at 13:24

1 Answer 1


Typical implementations that I'm familiar with, dealing with PHI (protected health information), which has stricter rules than simple PII, use these technical components:

  • Data-at-rest encryption, which includes either database-level encryption where supported by the DBMS or disk-level encryption, as well as backup and archived transaction log encryption.
  • Data-in-transit encryption, which essentially means TLS or IPSec for all network links.
  • Encryption key management system.
  • Row- and column-level access control. This should be implemented at the database level if the DBMS is capable of it, or in the application layer otherwise.
  • Network access control through explicit whitelisting (all network paths and ports are locked down by default and only open where needed).
  • Security monitoring of network traffic, OS and database logs for unusual access patterns.
  • Audit logging of all access paths, both at the database level (using database wire protocol capture if the DBMS has no native audit facility) and at the application level.
  • Secure erasure of data beyond its retention period.

This should cover most compliance requirements when implemented properly and augmented by the necessary organizational measures (identity and access management, separation of duties etc.)

The corollary of the above is that MySQL/MariaDB may not be a good choice for your use case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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