doushu7588 2018-05-02 10:49
浏览 69
已采纳

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

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   * bob@me.com      *
* Jane      * McBean  * 0584383   * jane@mail.com   *
*****************************************************

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.

  • 写回答

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

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 生成的QRCode圖片加上下載按鈕
  • ¥15 板材切割优化算法,数学建模,python,lingo
  • ¥15 科来模拟ARP欺骗困惑求解
  • ¥100 iOS开发关于快捷指令截屏后如何将截屏(或从截屏中提取出的文本)回传给本应用并打开指定页面
  • ¥15 unity连接Sqlserver
  • ¥15 图中这种约束条件lingo该怎么表示出来
  • ¥15 VSCode里的Prettier如何实现等式赋值后的对齐效果?
  • ¥15 流式socket文件传输答疑
  • ¥20 keepalive配置业务服务双机单活的方法。业务服务一定是要双机单活的方式
  • ¥50 关于多次提交POST数据后,无法获取到POST数据参数的问题