如何对加密列数据排序？

Question

我已经完成了对加密和非加密列的搜索，但对于排序，非加密字段排序成功，但加密字段不排序。

加密列:名字、姓氏、电子邮件

Encrypt函数

// Encrypt Data
function encrypted_data($decrypted){
    if($decrypted != NULL){
        $method = 'aes-256-cbc';
        // Must be exact 32 chars (256 bit)
        $salt = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
        $password = substr(hash('sha256', $salt, true), 0, 32);
        // IV must be exact 16 chars (128 bit)
        $iv = chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0) . chr(0x0);
        $encrypted = base64_encode(openssl_encrypt($decrypted, $method, $password, OPENSSL_RAW_DATA, $iv));
        return $encrypted;
    } else {
        return "";
    }
}

用于搜索的代码

if(isset($_POST['search']) && isset($_POST['search']['value']) && !empty($_POST['search']['value']))
{
    $search_keyword = trim($_POST['search']['value']);
    $encrypted = encrypted_data($search_keyword);
    
    $search_query = "AND id like '%$search_keyword%' OR pin_local_id like '%$search_keyword%' OR firstname like '%$encrypted%' OR lastname like '%$encrypted%' OR email like '%$encrypted%'";

    $sql .= $search_query;
    $count_sql .= $search_query;
}

排序代码

if(isset($_POST['order'][0]['dir']))
{
    $db_orderby = $_POST['order'][0]['dir'];

    if( isset($_POST['order'][0]['column']) && $_POST['order'][0]['column'] == 1 ) {
        $db_field_name = 'ID';
        $sql .= " order by $db_field_name $db_orderby ";
    } else if( isset($_POST['order'][0]['column']) && $_POST['order'][0]['column'] == 2 ) {
        $db_field_name = 'pin_local_id';
        $sql .= " order by $db_field_name $db_orderby ";
    } else if( isset($_POST['order'][0]['column']) && $_POST['order'][0]['column'] == 3 ) {
        $db_field_data = $_REQUEST['columns'][3]['data'];
        $db_field_name = 'firstname';
        $sql .= " order by $db_field_name $db_orderby ";
    } else if( isset($_POST['order'][0]['column']) && $_POST['order'][0]['column'] == 4 ) {
        $db_field_name = 'lastname';
        $sql .= " order by $db_field_name $db_orderby ";
    } else if( isset($_POST['order'][0]['column']) && $_POST['order'][0]['column'] == 5 ) {
        $db_field_name = 'email';
        $sql .= " order by $db_field_name $db_orderby ";
    }
} else {
    $sql .= " ORDER BY ID ASC ";
}

Answer 1

如果数据是在PHP中加密的，则不能使用MYSQL进行排序，因为MYSQL将根据加密的base64而不是原始值对数据进行排序。MYSQL永远不会公开原始的、解密的值，因此永远不能基于它进行排序。

您需要从Mysql中检索加密数据，在PHP中对其进行解密，然后使用usort根据解密数据的值对数组进行手动排序。