Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com Sep 7 '12 at 22:32

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

4  
Euh, by decrypting them, perhaps? –  H2CO3 Sep 7 '12 at 20:00
    
@H2CO3 This is the trivial answer, I'm obviously won't do. –  SteAp Sep 7 '12 at 20:04
    
this doesn't make sense. Then how do you want to search in them? –  H2CO3 Sep 7 '12 at 20:05
1  
This can't be a serious question. The only way to search the encrypted fields is to decrypt them before searching. –  Ken White Sep 7 '12 at 20:06
3  
There are techniques for searching encrypted databases (such as CryptDB), although they obviously come with tradeoffs against security and/or efficiency. However, this seems more like a crypto than a programming question to me; I've recommended that it be migrated to crypto.SE. –  Ilmari Karonen Sep 7 '12 at 21:10

5 Answers 5

up vote 4 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.

share|improve this answer
    
You could split it into blocks and hash each block individually, and compare against any of the blocks. –  arxanas Sep 7 '12 at 20:06
    
Thanks! That's what I am aware of. Would like to keep the question open to probably get 'exotic' solutions. –  SteAp Sep 7 '12 at 20:06
    
you could probably extend this by making a "full text" index of hashes, if you needed to, but it could get complicated really fast. –  Jeremy Holovacs Sep 7 '12 at 20:11
    
This isn't actually an answer to the question asked; you're not "searching the encrypted text", you're setting up a new searchable field (column) specifically designed for searching. They're not the same thing. (Not downvoting, but not upvoting either for that reason.) –  Ken White Sep 7 '12 at 20:17
1  
@KenWhite, well, the meaning was clear to me, and apparently to most other people as well. It could have been worded better, but I do think you're being overly critical. –  Jeremy Holovacs Sep 7 '12 at 22:05

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
    
Thank you very much! I'll check CryptDB definitely! –  SteAp Sep 8 '12 at 16:42

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
    
Just the fact, that I didn't accept an answer yet, does not mean, that I don't like an answer. Just want to keep the question open to probably get more replies. –  SteAp Sep 8 '12 at 16:39
    
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
    
Additionally, I'd like to know, why MySQL is a less acceptable solution as other DB-systems. Could you explain? –  SteAp Sep 8 '12 at 16:44

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" http://www.cs.berkeley.edu/~dawnsong/papers/se.pdf

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

Your Answer

 
discard

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.