Solved

Storing and searching  encrypted data in the database for a web application

Posted on 2013-01-04
2
351 Views
Last Modified: 2013-01-07
Linux, Apache, MySQL, PHP

I have developed a web form which asks an applicant to supply personal information that I want to make sure is always protected.  Currently I use SSL and encrypt the data in the database.

I am in the process of developing a web application where I can search the information I've collected from the above process.  The challenge is that the data in the database is encrypted so if I'm trying to search by a name like 'john' I won't find it because it doesn't exist in the database that way.  It exists in its encrypted form.  I have many thousands of records so decrypting the data on the fly is not ideal.

I'm looking for a recommendation for a better way to store and search for data while always making sure the data is protected.
0
Comment
Question by:Whah
2 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 38746533
This is a great question, and one that does not lend itself to a ready-made solution.  Let me suggest one or two design ideas.

You have many thousands of records... But in terms of server speed and power, that may not really be a big burden.  You might start by writing a SELECT * query and retrieving all the rows.  Then write another SELECT * query, retrieve all the rows and decrypt them one at a time.  Compare the time differences.  If it's not great, you might be able to consider decrypting on the fly.

You might consider having a discreet set of search fields.  You could keep these in clear text, enabling the FULLTEXT search capability or a WHERE ... LIKE clause.

You might consider having a discreet set of search fields that are separately encrypted. When you need to query, you can encrypt the query string and match it against the encrypted search fields.

If performance is an issue, you might consider a "down-select" process.  For example, if I wanted to inquire about clients in Washington state, your first step could be CREATE TEMPORARY TABLE ENGINE=MEMORY and SELECT only those rows that reflect data about Washington state.

If you want to tell us a little more about the method of encryption there may be some more ideas, too.

Not to beat you up, but there may be a flaw in this process.  If the client does not provide encrypted data to your server (but instead you receive clear text and encrypt it on the server) then the client does not really control the data.  It can be subpoenaed and you would be legally obligated to decrypt it and expose it.
0
 
LVL 33

Assisted Solution

by:Slick812
Slick812 earned 200 total points
ID: 38747057
greetings  Whah , , , You say you need a "better way to store and search for data" and having "protection" (encryption) in your database. . .  In my experience, Searching for any "altered" to binary (encrypt) text (john in your example), , may not be good for you to try. First if you are concerned with security, then the DATA in the encrypted form, should NOT be able to decrypt, except with a SECURE access, but these definitions and methods of SECURE access are your own, , , You might consider that if any search can decrypt all the secure data, then how much security is really there?
If you need to search and not decrypt, you may have to add a "MAC HASH of the PlainText data", table or field that you can search to get or narrow down the fields that are returned from the database.
$searchMAC = hash_hmac('crc32b', $PlainText, $user.'*$!@}');
but I would change this to have non-encrypted searchables, such as NAME (john) , EMAIL (john@host.com), and not search the "Private" encrypted data (private usually means no access, search or otherwise). AND have a method that you can only decrypt the "Personal" data if the user gives or has database stored secure key or password.
But you may give us more info about what your setup is, and why you need to search supposedly "Private encrypted data".
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now