使用PHP在数据库中进行加密数据搜索

Question

I have a search function that I am upgrading to support AES Encryption and for the life of me I don't know what I'm doing wrong.

I am passing the same queries through a search box as I did before I encrypted the data on the database side and I'm not returning any SQL errors. It just shows zero results every time.

Example of the database before encryption:

*****************************************************
* Firstname * Surname * Telephone * Email           *
*****************************************************
* Bob       * Smith   * 0193847   * [e-mail]      *
* Jane      * McBean  * 0584383   * [e-mail]   *
*****************************************************

Example of the database with encryption:

*****************************************************
* Firstname * Surname * Telephone * Email           *
*****************************************************
* 2d1c5749  * 82559acc* fa3bc41c5 * 759d082559      *
* 13c5802a  * 070e76f8* 70e76f8fe * feaa0ac1635c    *
*****************************************************

The previous working (non encrypted) version:

if(isset($_POST["search"]) && strlen($_POST["search"]) > 3) {
$Search = filter_var($_POST["search"], FILTER_SANITIZE_STRING);
$Search = strip_tags($Search);
$Search_String = filter_var($_POST["search"], FILTER_SANITIZE_STRING);
$Search = "%$Search%";

    $Search_String = filter_var($Search_String, FILTER_SANITIZE_STRING);
            if(strlen(empty($Search_String))){ $error[] = 'The search string is empty';}

if(!isset($error)) {
$Search_list = $dbconn->prepare("SELECT sql_calc_found_rows
                                  event_candidate.id AS candidate_id,
                                  event_candidate.firstname,
                                  event_candidate.surname,
                                  event_candidate.telephone,
                                  event_candidate.email,
                                  FROM event_candidate
                                  WHERE CONCAT_WS(' ', event_candidate.firstname, event_candidate.surname, event_candidate.telephone, event_candidate.email) LIKE ?
                                  "); 
    $Search_list->execute(array($Search));

New code that doesnt return anything:

    $Search = filter_var($_POST["search"], FILTER_SANITIZE_STRING);
    $Search = strip_tags($Search);
    $Search_String = filter_var($_POST["search"], FILTER_SANITIZE_STRING);
    $Search = "%".$Search."%";

$Search_list = $dbconn->prepare("SELECT sql_calc_found_rows
                              event_candidate_demo.id AS candidate_id,
                              event_candidate_demo.firstname,
                              event_candidate_demo.surname,
                              event_candidate_demo.telephone,
                              event_candidate_demo.email,
                              FROM event_candidate_demo
                              WHERE CONCAT_WS(' ', AES_DECRYPT(event_candidate_demo.firstname, UNHEX(:key)), AES_DECRYPT(event_candidate_demo.surname, UNHEX(:key)), AES_DECRYPT(event_candidate_demo.telephone, UNHEX(:key)), AES_DECRYPT(event_candidate_demo.email, UNHEX(:key))) LIKE ':search'
                              "); 
$Search_list->bindParam(':key', $Site_Key, PDO::PARAM_INT);
$Search_list->bindParam(':search', $Search, PDO::PARAM_STR);
$Search_list->execute();

So if i searched for anything realted to Bob it would load the correct record, however I do the same with the encrypted data and it always returns zero results.

I'm not sure if it's the AES_DECRYPT causing the issue or the switch to BindParam.

Answer 1

Data when encrypted becomes to resemble random noise. It will also never be the same(if it does you have bad encryption)

There are two solutions for your problem:

1. Fetch each row from the database, decrypt, compare. <-- Takes a lot of time
2. Store hashed keys, and compare hashes. <-- Way less secure, makes guessing data easier

You will have to chose one of the two evils depending on the sensitivity, need for speed and risk assesment.

Say you go with option 2 you would have two columns for the first name

event_candidate.firstname,
event_candidate.firstname_hash,

in the firstname field you store the encrypted name.
in the firstname_hash you store the hash of the firstname

You can use something like hash('sha512', strtolower($unencrypted_name)) to encrypt the hash.

Then to select data from it you can then do

$select = hash('sha512', strtolower($unencrypted_name));

$Search_list->bindParam(':search', $select, PDO::PARAM_STR);

However, you can then only do exact matches. The entire firstname needs to match, an extra incidental space can obstruct a match.

There are even more secure ways to hash it, using hmacs and other methods. The sample I provided is just to illustrate the base concept. You will have to perform your own risk analysis and sensitivity of the data.

Answer 2

Use "... LIKE :search ..." instead of "LIKE ':search' ...".

Are you sure you are checking errors from PDO correctly? If I run your example I get an Error "Invalid parameter number: number of bound variables does not match number of tokens" and not an empty result. (This is because ':search' is not recognized)

On another note: If you don't know which one of your two changes did cause the problem, why did you do 2 changes at once in the first place?

(Maybe I should have written this as a comment, but I need 50 reputation to be allowed to comment.)