Getting Started
Note: You must have a basic knowledge or concepts on OOP (Object-Oriented Programming).
In the previous tutorial, we've created a
CRUD Operation on SQLite3 Database using PHP. This time we're are going to discuss on how to encrypt data in this sqlite3 database.
Creating our SQLite Database and Connection
First, we are going to create a new sqlite database as well as a table.
Create a new file, name it as
dbconfig.php and paste the codes below.
<?php
//Create a new SQLite3 Database
$db = new SQLite3('members.db');
//Create a new table to our database
$query = "CREATE TABLE IF NOT EXISTS members (firstname STRING, lastname STRING, address STRING, key STRING)";
?>
Creating our Encrypt Class
Next, we need to create a class which contains our generated key, encrypt, and decrypt function.
Create a new file, name it as
Encrypt.php and paste the codes below.
<?php
Class Encrypt {
protected $key;
public function __construct(){
//you can assign any number on the key
$this->key = openssl_random_pseudo_bytes
(28); }
public function setKey($key){
}
public function getKey(){
}
public function encrypt($text){
$ivlen = openssl_cipher_iv_length($cipher="AES-128-CBC");
$iv = openssl_random_pseudo_bytes($ivlen);
$ciphertext_raw = openssl_encrypt
($text, $cipher, $this->key, $options=OPENSSL_RAW_DATA
, $iv); $hmac = hash_hmac('sha256', $ciphertext_raw, $this->key, $as_binary=true);
return $ciphertext;
}
public function decrypt($text, $key){
$ivlen = openssl_cipher_iv_length($cipher="AES-128-CBC");
$hmac = substr($c, $ivlen, $sha2len=32); $ciphertext_raw = substr($c, $ivlen+$sha2len); $original_plaintext = openssl_decrypt($ciphertext_raw, $cipher, $key, $options=OPENSSL_RAW_DATA, $iv);
$calcmac = hash_hmac('sha256', $ciphertext_raw, $key, $as_binary=true); if (hash_equals($hmac, $calcmac)) {
return $original_plaintext;
}
}
}
?>
Displaying our Table Data
Next step is to display the data from our created database and table. The data from our database is encrypted, so we're going to decrypt it.
Create a new file, name it as
index.php and paste the codes below.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CRUD Operation on SQLite3 Database using PHP</title>
</head>
<body>
<a href="add.php">Add</a>
<table border="1">
<thead>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
<th>Action</th>
</thead>
<tbody>
<?php
//include our connection
include 'dbconfig.php';
//require our Encrypt Class
require 'Encrypt.php';
//declare a new object for Encrypt class
$e = new Encrypt();
//query from the table that we create
$sql = "SELECT rowid, * FROM members";
$query = $db->query($sql);
while($row = $query->fetchArray()){
echo "
<tr>
<td>".$row['rowid']."</td>
<td>".$e->decrypt($row['firstname'], $row['key'])."</td>
<td>".$e->decrypt($row['lastname'], $row['key'])."</td>
<td>".$e->decrypt($row['address'], $row['key'])."</td>
<td>
<a href='edit.php?id=".$row['rowid']."'>Edit</a>
<a href='delete.php?id=".$row['rowid']."'>Delete</a>
</td>
</tr>
";
}
?>
</tbody>
</table>
</body>
</html>
Creating our Add Form and Script
Next, we create our add form as well our add script. In this script, we are going to encrypt our data using our generated key from our class then we insert our encrypted data together with the key which we need to decrypt the data.
Create new file, name it as
add.php and paste the codes below.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CRUD Operation on SQLite3 Database using PHP</title>
</head>
<body>
<form method="POST">
<a href="index.php">Back</a>
<p>
<label for="firstname">Firstname:</label>
<input type="text" id="firstname" name="firstname">
</p>
<p>
<label for="lastname">Lastname:</label>
<input type="text" id="lastname" name="lastname">
</p>
<p>
<label for="address">Address:</label>
<input type="text" id="address" name="address">
</p>
<input type="submit" name="save" value="Save">
</form>
<?php
if(isset($_POST['save'])){ //include our connection
include 'dbconfig.php';
//require our Encrypt Class
require 'Encrypt.php';
//declare a new object for Encrypt class
$e = new Encrypt();
//decrypt post values
$firstname = $e->encrypt($_POST['firstname']);
$lastname = $e->encrypt($_POST['lastname']);
$address = $e->encrypt($_POST['address']);
//add the key for this encryption
$key = $e->getKey();
//insert query
$sql = "INSERT INTO members (firstname, lastname, address, key) VALUES ('$firstname', '$lastname', '$address', '$key')";
header('location: index.php');
}
?>
</body>
</html>
Creating our Edit Form and Edit Script
Next, we create our edit form and our edit script. Create a new file, name it as
edit.php and paste the codes below.
<?php
//include our connection
include 'dbconfig.php';
//require our Encrypt Class
require 'Encrypt.php';
//declare a new object for Encrypt class
$e = new Encrypt();
//get the row of selected id
$sql = "SELECT rowid, * FROM members WHERE rowid = '".$_GET['id']."'";
$query = $db->query($sql);
$row = $query->fetchArray();
//set our encrypt from the row key
$e->setKey($row['key']);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CRUD Operation on SQLite3 Database using PHP</title>
</head>
<body>
<form method="POST">
<a href="index.php">Back</a>
<p>
<label for="firstname">Firstname:</label>
<input type="text" id="firstname" name="firstname" value="<?php echo $e->decrypt($row['firstname'], $row['key']); ?>">
</p>
<p>
<label for="lastname">Lastname:</label>
<input type="text" id="lastname" name="lastname" value="<?php echo $e->decrypt($row['lastname'], $row['key']); ?>">
</p>
<p>
<label for="address">Address:</label>
<input type="text" id="address" name="address" value="<?php echo $e->decrypt($row['address'], $row['key']); ?>">
</p>
<input type="submit" name="save" value="Save">
</form>
<?php
if(isset($_POST['save'])){ //encrypt post value
$firstname = $e->encrypt($_POST['firstname']);
$lastname = $e->encrypt($_POST['lastname']);
$address = $e->encrypt($_POST['address']);
//update our table
$sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE rowid = '".$_GET['id']."'";
header('location: index.php'); }
?>
</body>
</html>
Creating our Delete Script
Lastly, we create our delete script. Create a new file, name it as
delete.php and paste the codes below.
<?php
//include our connection
include 'dbconfig.php';
//delete the row of selected id
$sql = "DELETE FROM members WHERE rowid = '".$_GET['id']."'";
$db->query($sql);
header('location: index.php'); ?>
P.S. I've used
php.net-openssl_encrypt as a guide to create the encryption and decryption in this tutorial.
That ends this tutorial. Happy Coding :)
