Oh! Also, there's built in mysql functions for encryption, I forgot about
that, so you can still search, like this:
insert into table set name_field = AES_ENCRYPT('Some name', 'secret key');
select * from table where AES_DECRYPT(name_field,'secret key') LIKE '%some';
Make sense? You'll want an SSL connection to the database of course, and
anyone that has any decent access to the server memory would be able to get
the encryption key, but if you're careful it would work.
-Micah
On Tuesday 12 July 2005 2:53 pm, Micah Stevens wrote:
Just do all your searching/sorting in PHP.. it would be slower, and if your
dataset is very large (sounds like it might be the case) it would be
impossible.. So that might be out of the question..
A bit of system engineering might find a solution too, consider which
fields you need to search/sort by, and by possibly limiting those somewhat
to just what is absolutely necessary, you might be able to get by not
encrypting those columns.
Another idea would be to provide hinting columns, essentially providing
just enough data in those columns to be able to sort with, but not enough
to give away the data. i.e. just the first 2 characters of each name.
This would allow you to search and get a smaller dataset from the database,
something you could decrypt in php, and then search further, possibly
making it manageable.
Hope that helps,
-Micah
On Tuesday 12 July 2005 2:36 pm, Matt McNeil wrote:
Greetings,
I need to securely store lots of sensitive contact information and
notes in a (MySQL or other freely available) database that will be
stored on a database server which I do not have direct access to.
This database will be accessed by a PHP application that I am
developing. However, I also need to be able to search/sort these data
with the database functions (SELECT, ORDER BY, etc) so simple PASSWORD
style encryption of specific fields would not work. (For example, I
need to encrypt
contacts' names, but need to be able to sort results by name). (I
realize I could load the entire table into memory with PHP and
process/search/sort it there, but
that's obviously not a very good solution). Ideally I would like to
encrypt entire tables. An encrypted file system is not really an
option, because the goal is to prevent loss if the database server is
hacked (in addition, I wouldn't be able to install an encrypted file
system on the database server).
My sense is that this is a difficult problem. However, I made the
mistake of promising this functionality,
so I'm scrambling to figure out some kind of solution. Any
suggestions?
Thanks so much!
Matt
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
