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
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.Setting Up the Search Page
In addition to the last lesson where I gave you all the files, I made a couple changes and set up some more pages. This is the search page. I am taking advantage of the Bootstrap features for quick searching. Notice that I have some functions as well in there, so I also updated the functions page. Notice I created a function called "names" where I select all from the people's table. I then echo out their name, email and id number.Search Page
<?php
include_once('header.php');
?>
<div class="well">
<h2>Search By Name or Email</h2>
<form method="post" action="results.php">
<div class="form-group">
<select name="theid" class="selectpicker show-tick" data-live-search="true" data-size="1" data-header="Search" data-width="auto" >
<option>Search</option>
<?php names(); ?>
</select>
</div>
<button type="submit" id="submit" name="submit" class="btn btn-success">Submit</button>
</form>
</div>
</div>
</div>
<?php include_once('footer.php'); ?>
The Functions Page
<?php
include_once('config.php');
$key = 'PHPencryptionIStheGREATESTthingINtheWORLD^&%$#@!';
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);
}
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);
}
function names(){
global $con;
global $key;
$result = $con->query("SELECT * FROM people") ;
while ($row = $result->fetch_assoc()) {
$id= $row['id'];
$thename= decryptthis($row['name'], $key);
$email= decryptthis($row['email'], $key);
echo '<option value="'.$id.'" data-tokens="'.$thename.'" data-subtext="'.$email.'"">'.$thename.'</option>';
}
}
?>
I also added more changes please go to the next page.
The Header & Footer Files
I updated the header and footer files as well. I put more CSS in the header and more javascript in the footer.Header File
<?php
date_default_timezone_set('America/New_York');
include_once('functions.php');
?>
<html>
<head>
<title>PHP ENCRYPTION DECRYPTION MYSQL</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap-select.min.css">
</head>
<body>
<?php include_once('menu.php'); ?>
<div class="jumbotron"><h1 class="text-center">The Best PHP Encryption Tutorial</h1>
</div>
<div class="container">
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6">
Footer File
</div> <div class="well"> <p class="lead text-center">Brought to you by <a href="a1websitepro.com">A1WEBSITEPRO.COM</a></p> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <!-- Latest compiled and minified JavaScript --> <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap-select.min.js"></script> </div> </body> </html> <?php $con->close(); ?>I also added a results file, you can get that on the next page.
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');
?>