SQL / PHP查询表并从查询中解密数据

Question

我正在使用以下函数（存储在functions.php文件中）使用键（存储在mykey.php为$键）的键（存储在mykey.php中）的数据。

<?php
include '../../mykey.php';
//ENCRYPT FUNCTION
function encryptthis($data, $key) {
$encryption_key = base64_decode($key);
$iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length('aes-256-cbc'));
$encrypted = openssl_encrypt($data, 'aes-256-cbc', $encryption_key, 0, $iv);
return base64_encode($encrypted . '::' . $iv);
}

//DECRYPT FUNCTION
function decryptthis($data, $key) {
$encryption_key = base64_decode($key);
list($encrypted_data, $iv) = array_pad(explode('::', base64_decode($data), 2),2,null);
return openssl_decrypt($encrypted_data, 'aes-256-cbc', $encryption_key, 0, $iv);
}

?>

数据已成功发布到数据库，并加密列数据。 我现在正在尝试通过将电子邮件地址输入表格并检索与该电子邮件相关的所有记录来检索数据。

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    
    // Find the required post values
    $email = $_POST['email'];
    

然后，在我的查询中，我尝试使用解密值选择。 但是没有返回记录 当没有加密时（也只有某些字段加密），它可以正常工作。

这是所有代码

include './functions.php';
include './config.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    
    // Find the required post values
    $table = $_POST['appurlkey'];
    $email = $_POST['email'];

    
try {
        
        // Start connection
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // Set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Query
        $sql = "SELECT * FROM $table WHERE addedby_email=:addedby_email AND active=:active ORDER BY id DESC";
        
        
        // Prepare query
        $stmt = $conn->prepare($sql);
        
        // Bind
        $stmt->bindValue(':addedby_email', $encemail);
        $stmt->bindValue(':active',  '1');
        
        // Execute
        $stmt->execute();
        
        if ($stmt->rowCount() > 0) {
            
            
            $msg = "Successfully fetched the list.";
            
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            foreach($rows as $row) {
                
                $myDateTime = DateTime::createFromFormat('Y-m-d H:i:s', $row["created_at"]);
                $created_at = $myDateTime->format('d/m/Y');
                

                
                $data = [
                    "id" => $row["id"],
                    "addedby_email" => $row["email"] ,
                    "addedby_name" => decryptthis($row["addedby_name"],$key),
                    "firstname" => decryptthis($row["firstname"],$key),
                    "lastname" => decryptthis($row["lastname"],$key),
                    "birthdate" => $row["birthdate"],
                    "phone" => decryptthis($row["phone"],$key),
                    "email" => decryptthis($row["email"],$key),
                    "address" => decryptthis($row["address"],$key),
                    "town" => $row["town"],
                    "county" => $row["county"],
                    "postcode" => decryptthis($row["postcode"],$key),                
                    "active" => $row["active"],
                    "created_at" => $created_at
                ];  
                
                $persons[] = [
                    "pt_id" => $row["id"],
                    "name" => decryptthis($row["firstname"],$key) . " " . decryptthis($row["lastname"],$key),
                    "birthdate" => $row["birthdate"],
                    "data" => $data,
                ];
                
            }
            
        } else {
            
            $msg = "No person found.";
            $persons = null;    
        }
        
        $response = [
            "success" => true,
            "message" => $msg,
            "persons" => $persons,
        ];      
        
    } catch(PDOException $e) {
        $msg = "Error while fetching the persons list.";
        // $msg = $sql . "<br>" . $e->getMessage();
        $response = [
            "success" => false,
            "message" => $msg,
            "persons" => null,
        ];      
    }
    
    // Close connection
    $conn = null;

    // Json response
    echo json_encode($response);

}

Answer 1

遵循 @yourCommonSense建议设置一个数组以检查表是否为白色

<?php

include './config.php';
include './functions.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    
    // Find the required post values
    $wList = array("123456");
    $table = $_POST['appurlkey'];
    $email = $_POST['email'];

if(!in_array($table, $wList)) {
   exit();
}
            
    try {
        // Start connection
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // Set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    


    $stmt = $conn->prepare("SELECT * FROM $table WHERE addedby_email = :addedby_email AND active=:active ORDER BY id DESC");
    
    $stmt->bindValue(':addedby_email', $email);
    $stmt->bindValue(':active',  '1');
    $stmt->execute();

    if ($stmt->rowCount() > 0) {
            
            $msg = "Successfully fetched the patients list.";
            
            
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                
                $myDateTime = DateTime::createFromFormat('Y-m-d H:i:s', $row["created_at"]);
                $created_at = $myDateTime->format('d/m/Y');
                
                $data = [
                    "id" => $row["id"],
                    "addedby_email" => $row["addedby_email"],
                    "addedby_name" => decryptthis($row['addedby_name'], $key),
                    "firstname" => decryptthis($row['firstname'], $key),
                    "lastname" => decryptthis($row['lastname'], $key),
                    "birthdate" => decryptthis($row['birthdate'], $key),
                    "phone" => decryptthis($row['phone'], $key),
                    "email" => $row["email"],
                    "address" => decryptthis($row['address'], $key),
                    "town" => $row["town"],
                    "county" => $row["county"],
                    "postcode" => decryptthis($row['postcode'], $key),
                    "patient_type" => $row["patient_type"],
                    "doctor" => $row["doctor"],                    
                    "active" => $row["active"],
                    "created_at" => $created_at
                ];  
                
                $patients[] = [
                    "pt_id" => $row["id"],
                    "name" => $firstname . " " . $lastname,
                    "birthdate" => $birthdate,
                    "data" => $data,
                ];
                
            }
            
        } else {
            $msg = "No patient found.";
            $patients = null;   
        }
        
        $response = [
            "success" => true,
            "message" => $msg,
            "patients" => $patients,
        ];      
        
    } catch(PDOException $e) {
        $msg = "Error while fetching the patients list.";
        // $msg = $sql . "<br>" . $e->getMessage();
        $response = [
            "success" => false,
            "message" => $msg,
            "patients" => null,
        ];      
    }
    
    // Close connection
    $conn = null;

    // Json response
    echo json_encode($response);

}

?>