[PHP-DB] Searchable/Sortable Database Fields with MySQL/PHP
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
Re: [PHP-DB] Searchable/Sortable Database Fields with MySQL/PHP
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
Re: [PHP-DB] Searchable/Sortable Database Fields with MySQL/PHP
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