doushu7588 2018-05-02 10:49
浏览 69


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   *      *
* Jane      * McBean  * 0584383   *   *

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
                         AS candidate_id,
                                  FROM event_candidate
                                  WHERE CONCAT_WS(' ', event_candidate.firstname, event_candidate.surname, event_candidate.telephone, LIKE ?

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
                     AS candidate_id,
                              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(, UNHEX(:key))) LIKE ':search'
$Search_list->bindParam(':key', $Site_Key, PDO::PARAM_INT);
$Search_list->bindParam(':search', $Search, PDO::PARAM_STR);

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.

  • 写回答

2条回答 默认 最新

  • dpjuppr1361 2018-05-02 12:08

    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


    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
  • dpbl91234 2018-05-02 13:15

    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.)




  • ¥15 求一个智能家居控制的代码
  • ¥15 ad软件 pcb布线pcb规则约束编辑器where the object matpcb布线pcb规则约束编辑器where the object matchs怎么没有+15v只有no net
  • ¥15 虚拟机vmnet8 nat模式可以ping通主机,主机也能ping通虚拟机,但是vmnet8一直未识别怎么解决,其次诊断结果就是默认网关不可用
  • ¥20 求各位能用我能理解的话回答超级简单的一些问题
  • ¥15 yolov5双目识别输出坐标代码报错
  • ¥15 这个代码有什么语法错误
  • ¥15 给予STM32按键中断与串口通信
  • ¥15 使用QT实现can通信
  • ¥15 关于sp验证的一些东西,求告知如何解决,
  • ¥35 关于#javascript#的问题:但是我写的只能接码数字和字符,帮我写一个解码JS问题