How To Search PHP Encrypted Database

Subscribe

This post will show you how to search a PHP encrypted database. This is a follow up on the PHP encryption and decryption tutorials. Many have made the argument that once all the data is encrypted and stored that there is no way to query the database. However, you can query the database and I am going to show you how to do it in this lesson. If you are new to encrypting data in PHP please see our post BEST PHP ENCRYPTION DECRYPTION MYSQL TUTORIAL | PROACTIVE METHODS (a1websitepro.com)

Encrypted Database

Encrypted Database

The Problem of Searching a PHP Encrypted Database

You will need to know how to search your PHP encrypted database. For example, if you wanted to search for my name “Maximus” it would be impossible to do so directly in an encrypted database. This is the whole point of security. If you have thousands of entries, then to print it out on a page and look through it becomes cumbersome.

How To Search A PHP Encrypted Database

There are a few options you could do to search a PHP encrypted database. You could decrypt and export to a json or xml file then bring it into your application. However I am going to show you something simple and more secure.

How To Search PHP Encrypted Database was last modified: October 30th, 2021 by Maximus Mccullough

Summary
How To Search PHP Encrypted Database
Article Name
How To Search PHP Encrypted Database
Description
This post will show you how to search a PHP encrypted database. This is a follow up on the PHP encryption and decryption tutorials.
Author
Publisher
A1WEBSITEPRO LLC
Logo
How To Search PHP Encrypted Database

Pages: 1 2 3 4 Next

5 Comments

  • H says:

    Thanks Maximus! Great tutorial again.
    I encrypted my data following your first tutorial.
    I am now trying to retrieve my encrypted data using the post value $email .
    Heres my code:

    include ‘./functions.php’;
    include ‘./enc_config.php’;

    if ($_SERVER[“REQUEST_METHOD”] == “POST”) {

    // Find the required post values
    $table = $_POST[‘appurlkey’];
    $email = $_POST[’email’];

    $encaddedby_email=decryptthis($addedby_email, $key);
    $encaddedby_name=decryptthis($addedby_name, $key);
    $encfirstname=decryptthis($firstname, $key);
    $enclastname=decryptthis($lastname, $key);
    $encbirthdate=decryptthis($birthdate, $key);
    $encphone=decryptthis($phone, $key);
    $encemail=decryptthis($email, $key);
    $encaddress=decryptthis($address, $key);
    $encpostcode=decryptthis($postcode, $key);

    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 patient_$table WHERE addedby_email=:addedby_email AND active=:active ORDER BY id DESC”;

    $sql2= “SELECT *, $encaddedby_email AS addedby_email, $encaddedby_name AS addedby_name, $encfirstname AS firstname, $enclastname AS lastname, $encbirthdate AS birthdate, $encphone AS phone, $encemail AS email, $encaddress AS address, $encpostcode AS postcode FROM patient_$table WHERE $encaddedby_email=:addedby_email AND active=:active ORDER BY id DESC”;

    // Prepare query
    $stmt = $conn->prepare($sql);

    // Bind
    $stmt->bindValue(‘:addedby_email’, $email);
    $stmt->bindValue(‘:active’, ‘1’);

    // Execute
    $stmt->execute();

    if ($stmt->rowCount() > 0) {

    $msg = “Successfully fetched the patients 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[“addedby_email”],
    “addedby_name” => $row[“addedby_name”],
    “firstname” => $row[“firstname”],
    “lastname” => $row[“lastname”],
    “birthdate” => $row[“birthdate”],
    “phone” => $row[“phone”],
    “email” => $row[“email”],
    “address” => $row[“address”],
    “town” => $row[“town”],
    “county” => $row[“county”],
    “postcode” => $row[“postcode”],
    “patient_type” => $row[“patient_type”],
    “doctor” => $row[“doctor”],
    “active” => $row[“active”],
    “created_at” => $created_at
    ];
    $patients[] = [
    “pt_id” => $row[“id”],
    “name” => $row[“firstname”] . ” ” . $row[“lastname”],
    “birthdate” => $row[“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.”;
    $response = [
    “success” => false,
    “message” => $msg,
    “patients” => null,
    ];
    }

    // Close connection
    $conn = null;

    // Json response
    echo json_encode($response);

    The $sql2 query doesn’t decrypt my data and it results in the json response
    {“success”:false,”message”:”Error while fetching the patients list.”,”patients”:null}
    Can you suggest how i would decrypt the data based on $post value of email entered by the user.
    If you think theres a bit of work involved here I’m happy to pay for your services – i did do a sign up but i cant login after sign-up! Thanks for your help.

    • HI Harry, thanks again for the comment. So the issue with this method is that everything is encrypted in the database. SO, you cannot really search for the encryption in the actual MySQL database. You have to decrypt it first then search for a match. Does that make sense? The other factor is that the encryption keeps changing. For example my name “Maximus” could be “lkabfeiwe” one time then “iweewnnd” the next time. This is so that it can never be decrypted without the key. So using MySQL to search for this encryption is not possible without decrypting it first. I have an application called https://just4u.ezbloo.com where people create their own accounts, login and save links. All of that is encrypted for them except for their ID number & username in the database. If I ever needed to search for info I decrypt and then search. I hope this helps. I look forward to hearing from you. 🙂

  • H says:

    Thanks that makes sense.
    So i have made a query on all the encrypted data cols and then decrypted them in a loop before selecting again based on the original query in $post email
    Whilst the final select query looks good in my var_dump I’m still getting no results back: catch(PDOException $e)
    ====================================
    //get all encrypted records
    $query = “SELECT addedby_email,addedby_name,firstname,lastname,birthdate,phone,email,address,postcode FROM patient_$table”;
    //loop through and decrypt
    $sth = $conn->query($query);
    while( $row = $sth->fetch(PDO::FETCH_ASSOC) ) {
    $encaddedby_email=decryptthis($row[‘addedby_email’], $key);
    $encaddedby_name=decryptthis($row[‘addedby_name’], $key);
    $encfirstname=decryptthis($row[‘firstname’], $key);
    $enclastname=decryptthis($row[‘lastname’], $key);
    $encbirthdate=decryptthis($row[‘birthdate’], $key);
    $encphone=decryptthis($row[‘phone’], $key);
    $encemail=decryptthis($row[’email’], $key);
    $encaddress=decryptthis($row[‘address’], $key);
    $encpostcode=decryptthis($row[‘postcode’], $key);
    }

    //var_dump($encaddedby_email);
    //exit;

    $sql = “SELECT * FROM patient_$table WHERE $encaddedby_email=:addedby_email AND active=:active ORDER BY id DESC”;

  • H says:

    Hi,
    I’ve got it working however as i have to do a select * to decrypt all the table values before running my query would the performance not get very slow especially if there were thousands of records?
    ===============================
    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);

    $result = $conn->query(“SELECT * FROM patient_$table”) ;

    while( $row = $result->fetch(PDO::FETCH_ASSOC))
    {

    $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);
    $address= decryptthis($row[‘address’], $key);
    $postcode = decryptthis($row[‘postcode’], $key);

    }

    // Query
    $sql = “SELECT * FROM patient_$table WHERE addedby_email=:addedby_email AND active=:active ORDER BY id DESC”;

    Execute and json response come here and all the data is decrypted!

    • Yes very good! Yes, I do understand that the query process would be slower however, if someone stole your database they could do nothing with it. Look at all the places being hacked that rely on MySQL security. The other thing to think about is that you do not have to encrypt everything. You could only encrypt sensitive information and other info leave searchable with MySQL. For example a postal code just in case you were trying to geo match a patient with a doctor or something. Thanks for the comment and I hope you had fun doing this.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.