[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-01-04
2
Medium Priority
?
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1200 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 34

Assisted Solution

by:Slick812
Slick812 earned 800 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

649 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