SQL / PHP query a table and decrypt the data from the query

Question

I am posting data from a form and encrypting it using a key (stored in mykey.php as $key) using the following functions(stored in functions.php file).

<?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);
}

?>

The data is posted to the database successfully and the column data is encrypted. I am now trying to retrieve data by entering an email address into a form and retrieving all records related to that email.

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

Then in my query I'm trying to select using the decrypted values. However no records are returned It worked fine when there was no encryption (also only some fields are encrypted).

Here is all of the code

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

}

Can you just do the encryption at the DB level? I think that would be easier, `aes_encrypt`/`aes_decrypt`. You also are open to SQL injections, don't put `$table` in without any check. Use a whitelist to verify the table name...I also wouldn't bund the `1`, just keep it in the query, `AND active= 1` — user3783243, May 18 '22 at 16:01
I don't understand this logic. the column data is encrypted means your need to encrypt your data, not decrypt it. And why you're decrypting the data from HTML form at all? — Your Common Sense, May 18 '22 at 16:35
Thanks for the comments. What I’m trying to do is encrypt the “add person” form data in the database. Then do lookups on that data based on a user input e.g post email would return all records with that email. As the email is encrypted in the database I need to decrypt it to compare against the user input. — OnTarget, May 19 '22 at 05:57

OnTarget · Accepted Answer · 2022-05-22T21:07:06.110

Following @YourCommonSense advice setup an array to check if table is whitelisted

<?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);

}

?>

At first you were saying that email is encrypted and now it is not. So what is encoded here and why? and why you're selecting the same data twice? — Your Common Sense, May 22 '22 at 10:16
So it leaves us with two new questions: why not just encrypt the entered email? and why encrypt the data at all, if such encryption can be easily abandoned. Let alone the old questions, why allowing a deliberate SQL injection and why selecting the same data twices — Your Common Sense, May 22 '22 at 10:46
Thanks - correct the email was encrypted originally but now this is left unencrypted for the query - other personal data is encrypted. I removed the duplicate select statement - code updated above. To fix the $post vulnerability you suggest bind in your post here - https://codereview.stackexchange.com/questions/230940/pdo-is-this-registration-secure-and-well-coded - is this therefore the fix i require? — OnTarget, May 22 '22 at 10:50
This looks much better, but why do you have try-catch around the whole thing? Also, why set pdo to null at the end of the code? — Dharman, May 23 '22 at 08:40

SQL / PHP query a table and decrypt the data from the query

1 Answers1