Results File
This file takes whatever results that you get on the search page and gives you more details about the entry you selected.
<?php include_once('header.php'); if(isset($_POST['submit'])){ $id=$_POST['theid']; } echo '<div class="well">'; $result = $con->query("SELECT * FROM people WHERE id='$id' LIMIT 1") ; while ($row = $result->fetch_assoc()) { echo '<p>Encrypted name from database: '.$row['name'].'</p>'; echo '<p>Decrypted Name: '.decryptthis($row['name'], $key).'</p>'; echo '<p>Encrypted email from database: '.$row['email'].'</p>'; echo '<p>Decrypted Email: '.decryptthis($row['email'], $key).'</p>'; echo '<p>ID#: '.$row['id'].'</p>'; } echo '</div> </div> <div class="col-sm-3"></div> </div></div>'; include_once('footer.php'); ?>
All The Files
If you would like to download all the files in this lesson here they are.
How To Search PHP Encrypted Database was last modified: March 17th, 2023 by
Summary
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
Maximus McCullough
Publisher
A1WEBSITEPRO LLC
Logo
5 Comments
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. 🙂
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”;
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.