Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt.

How would one search those fields anyway?

Decrypting each record step by step is no option: Suppose I have multiple of thousands of records. It would take too much time and space to decrypt each record and check if each single record matches the search.

UPDATE 2012-09-07

Adding further details to the database schema would be OK, since I'm about to implement a new application. Furthermore, I need to extend applications currently running in production. But even for those application, adding further details would be OK.

UPDATE 2012-09-08

Encryption is the kernel of this question.

Access restrictions, as proposed by some answers, already apply - but do not fit the formal requirement to encrypt data.

This formal requirement is not Payment Card Industry Data Security Standard [PCI].

share|improve this question

migrated from Sep 7 '12 at 22:32

This question came from our site for professional and enthusiast programmers.

Only encrypt the sensitive data, and search for your records by other data such as by record id. – user25698 Jul 6 '13 at 17:52
up vote 6 down vote accepted

Obviously they are not meant to be viewed, therefore searching on them would be problematic.

One trick I have used in the past is to hash the encrypted data before encrypting it, and storing the hash in an indexed column. Of course, this only works if you are searching on the whole value; partial values will not have the same hash.

You could probably extend this by making a "full text" index of hashes, if you needed to, but it could get complicated really fast.

share|improve this answer
I've used the hash technique before. If the things you search for are finite, then this is actually a pretty good solution. You can index a hash pretty effectively too. Obviously you can hash subsets of the data, if you need to, rather than the entire field. – d11wtq Sep 8 '12 at 2:32

You may want to take a look at CryptDB. It's a front end for MySQL and PostgreSQL that allows transparent storage and querying of encrypted data. It works by encrypting and decrypting data as it passes between the application and the database, rewriting queries to operate on the encrypted data. and by dynamically adjusting the encryption mode of each column to expose only as much information as needed for the queries the application uses.

The various encryption methods used by CryptDB include:

  • RND, a fully IND-CPA secure encryption scheme which leaks no information about the data (except its presence and, for variable-length types, length) but only allows storage and retrieval, no queries.

  • DET, a variant of RND which is deterministic, so that two identical values (in the same column) encrypt to the same ciphertext. Supports equality queries of the form WHERE column = 'constant'.

  • OPE, an order-preserving encryption scheme that supports inequality queries such as WHERE column > 'constant'.

  • HOM, a partially homomorphic encryption scheme (Paillier) which allows adding encrypted values together by multiplying the ciphertexts. Supports SUM() queries, addition and incrementing.

  • SEARCH, a scheme that supports keyword searches of the form WHERE column LIKE '% word %'.

  • JOIN and OPE-JOIN, variants of DET and OPE that allow values in different columns to be compared with each other. Support equality and range joins respectively.

The real power of CryptDB is that it adapts the encryption method of each column dynamically to the queries it sees, so that the slower and/or less secure schemes are only used for columns which require them. There are also various other useful features, such as chaining encryption keys to user passwords.

If you're interested, you're well advised to take a look at the papers linked from the CryptDB website, particularly "CryptDB: Protecting Confidentiality with Encrypted Query Processing" by Popa, Redfield, Zeldovich and Balakrishnan (SOSP 2011). Those papers also describe the various security and performance tradeoffs involved in supporting different query types in more detail.

share|improve this answer

I don't understand why the current answers haven't questioned the requirements fully, so I'll ask and leave it as an answer.

What are the business reasons? What data do you need to encrypt and why? If you're looking for PCI compliance, I could write an essay.

Questions about your requirement:

  • Will you need to return a exists/not exists as a result, or the actual data?
  • Do you require a LIKE '%OMG_SEKRIT%' capability?
  • Who cannot see the data and why?

RDBMS security is normally done on a permissions basis that is enforced by user/role. The data is normally encrypted by the RDBMS on disk, but not in the columnar data itself, as that doesn't really make any sense for an application designed to efficiently store and retrieve data.

Restrict by user/role/api. Encrypt on disk. If you're storing more important data I'd love to know why you're using MySQL.

share|improve this answer
Primarily, I need to find exists/not exists and then locate the specific record. Full LIKE support would be fine. But I wonder, that anything more than matching of words will be possible. Authorized user are allowed to see data. The app decrypts those items, a legitimate user has rights to see. Permission base schemas are no option. – SteAp Sep 8 '12 at 16:41

I'm looking into this and came across your question. I'm leaning towards the approach outlined in section 5.4 of the paper "Practical Techniques for Searches on Encrypted Data"

The basic gist is to create an index that contains encrypted keywords that are present in the encrypted search document. The trick is to also encrypt the locations in the document (or database) where those keywords are present.

share|improve this answer

Programmatically, an efficient solution is to

  1. retrieve ALL of the records for ONLY the field you are searching against with the record id
  2. decrypt those into a temporary table
  3. perform the search against that table
  4. use the id's to retrieve the full records (all fields) that match the search criteria
  5. decrypt those and return them to the user

The point is that 1 and 4 are significantly smaller sets of data than retrieving and decrypting all fields of all records in the beginning.

Hope that helps.

share|improve this answer

Assumming you are searching in SQL and against the full value and not partial (e.g. LIKE 'value%')...when capturing the search data, encrypt that data using the same algorithm used when the data was encrypted and search for that.

For example:

What would have been:

SELECT FieldA, FieldB 
FROM Table1 
WHERE FieldC = 'Value'

Might instead look like:

SELECT FieldA, FieldB 
FROM Table1 
WHERE FieldC = 'hsk&%67ghhks83'
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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