I am encrypting and decrypting my MySQL contact table in PHP. That is all fine and everything but I am making the data grid so that it has a single search box that does a full text search on all the columns. The problem now is that the search is being done against the encrypted data and not the original data.
So I am forced to do the full text search in PHP. This requires me to iterate over each row, but I am doing this anyway because I need to decrypt it.
My concern lies with the speed at which my code does the full text search, I want to know if there is a better, faster way to do this?
$result = Some PDO query that does a PDO::FETCH_ASSOC, query has about 8 columns
$searchPhrase = strtolower($searchPhrase);
$aes = new AES(DATABASE_KEY, DATABASE_IV);
$count = count($result);
for($ii = 0; $ii <$count; $ii++ )
{
$result[$ii]["ContactName"] = $aes->Decrypt($result[$ii]["ContactName"]);
$result[$ii]["ContactDescription"] = $aes->Decrypt($result[$ii]["ContactDescription"]);
/*More encrypted columns*/
if (!empty($searchPhrase)) //Only do search when search phrase not empty
{
$filterRow = array_slice($result[$ii], 3); // Do not take first 3 columns
$data = strtolower(implode("", $filterRow)); //Flaten row as one string, all lowercase
if (strpos($data, $searchPhrase) === FALSE) //Now doing elimentation from array if not match
unset($result[$ii]);
}
}
The only secure and efficient way to search encrypted data in PHP + MySQL is via exact match, using a technique called blind indexing.
E(M, K1) || H(E(M, K1), K2) (e.g. a message encrypted with defuse/php-encryption) in one columnH(M, K3) in a second columnIn pseudo-code
$cipher = Crypto::encrypt($message, $encryptionKey); // takes care of K1 and K2 for you
$blind_idx = hash_hmac('sha384', $message, $unrelatedKey); // K3
For what it's worth, encrypting database columns with symmetric encryption like AES doesn't add to your information security very much. That's because the DATABASE_KEY must be available to your programs. So a cybercriminal who penetrates your system will easily find and steal your key, and then easily steal your data.
If your columns' values are encrypted your database system can't generate a useful fulltext index. Therefore, the substring search strategy in your question is close to optimal.
But, seriously, spend your time and money on a good firewall. Protect your database and web servers from intruders, rather than trying to stop intruders from stealing columns of data once they have taken over your systems.
Take a look into the mysql function AES_DECRYPT. Essentially, you could retrieve all of the data from the database in plain format or run queries against it or just tell mysql to go row by row to decrypt the data before comparing your value with it. Take a look at the following: