SQLite 3 Database Encryption

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.
  1. <?php
  2. //Create a new SQLite3 Database
  3. $db = new SQLite3('members.db');
  4.  
  5. //Create a new table to our database
  6. $query = "CREATE TABLE IF NOT EXISTS members (firstname STRING, lastname STRING, address STRING, key STRING)";
  7. $db->exec($query);
  8.  
  9. ?>

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.
  1. <?php
  2.  
  3. Class Encrypt {
  4.  
  5. protected $key;
  6.  
  7. public function __construct(){
  8. //you can assign any number on the key
  9. $this->key = openssl_random_pseudo_bytes(28);
  10. }
  11.  
  12. public function setKey($key){
  13. $this->key = $key;
  14. }
  15.  
  16. public function getKey(){
  17. return $this->key;
  18. }
  19.  
  20. public function encrypt($text){
  21. $ivlen = openssl_cipher_iv_length($cipher="AES-128-CBC");
  22. $iv = openssl_random_pseudo_bytes($ivlen);
  23. $ciphertext_raw = openssl_encrypt($text, $cipher, $this->key, $options=OPENSSL_RAW_DATA, $iv);
  24. $hmac = hash_hmac('sha256', $ciphertext_raw, $this->key, $as_binary=true);
  25. $ciphertext = base64_encode( $iv.$hmac.$ciphertext_raw );
  26.  
  27. return $ciphertext;
  28. }
  29.  
  30. public function decrypt($text, $key){
  31. $c = base64_decode($text);
  32. $ivlen = openssl_cipher_iv_length($cipher="AES-128-CBC");
  33. $iv = substr($c, 0, $ivlen);
  34. $hmac = substr($c, $ivlen, $sha2len=32);
  35. $ciphertext_raw = substr($c, $ivlen+$sha2len);
  36. $original_plaintext = openssl_decrypt($ciphertext_raw, $cipher, $key, $options=OPENSSL_RAW_DATA, $iv);
  37. $calcmac = hash_hmac('sha256', $ciphertext_raw, $key, $as_binary=true);
  38. if (hash_equals($hmac, $calcmac)) {
  39. return $original_plaintext;
  40. }
  41. }
  42.  
  43. }
  44.  
  45. ?>

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.
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>CRUD Operation on SQLite3 Database using PHP</title>
  6. </head>
  7. <body>
  8. <a href="add.php">Add</a>
  9. <table border="1">
  10. <thead>
  11. <th>ID</th>
  12. <th>Firstname</th>
  13. <th>Lastname</th>
  14. <th>Address</th>
  15. <th>Action</th>
  16. </thead>
  17. <tbody>
  18. <?php
  19. //include our connection
  20. include 'dbconfig.php';
  21.  
  22. //require our Encrypt Class
  23. require 'Encrypt.php';
  24.  
  25. //declare a new object for Encrypt class
  26. $e = new Encrypt();
  27.  
  28. //query from the table that we create
  29. $sql = "SELECT rowid, * FROM members";
  30. $query = $db->query($sql);
  31.  
  32. while($row = $query->fetchArray()){
  33. echo "
  34. <tr>
  35. <td>".$row['rowid']."</td>
  36. <td>".$e->decrypt($row['firstname'], $row['key'])."</td>
  37. <td>".$e->decrypt($row['lastname'], $row['key'])."</td>
  38. <td>".$e->decrypt($row['address'], $row['key'])."</td>
  39. <td>
  40. <a href='edit.php?id=".$row['rowid']."'>Edit</a>
  41. <a href='delete.php?id=".$row['rowid']."'>Delete</a>
  42. </td>
  43. </tr>
  44. ";
  45. }
  46.  
  47. ?>
  48. </tbody>
  49. </table>
  50. </body>
  51. </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.
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>CRUD Operation on SQLite3 Database using PHP</title>
  6. </head>
  7. <body>
  8. <form method="POST">
  9. <a href="index.php">Back</a>
  10. <p>
  11. <label for="firstname">Firstname:</label>
  12. <input type="text" id="firstname" name="firstname">
  13. </p>
  14. <p>
  15. <label for="lastname">Lastname:</label>
  16. <input type="text" id="lastname" name="lastname">
  17. </p>
  18. <p>
  19. <label for="address">Address:</label>
  20. <input type="text" id="address" name="address">
  21. </p>
  22. <input type="submit" name="save" value="Save">
  23. </form>
  24. <?php
  25. if(isset($_POST['save'])){
  26. //include our connection
  27. include 'dbconfig.php';
  28.  
  29. //require our Encrypt Class
  30. require 'Encrypt.php';
  31.  
  32. //declare a new object for Encrypt class
  33. $e = new Encrypt();
  34.  
  35. //decrypt post values
  36. $firstname = $e->encrypt($_POST['firstname']);
  37. $lastname = $e->encrypt($_POST['lastname']);
  38. $address = $e->encrypt($_POST['address']);
  39. //add the key for this encryption
  40. $key = $e->getKey();
  41.  
  42. //insert query
  43. $sql = "INSERT INTO members (firstname, lastname, address, key) VALUES ('$firstname', '$lastname', '$address', '$key')";
  44. $db->exec($sql);
  45.  
  46. header('location: index.php');
  47.  
  48. }
  49. ?>
  50. </body>
  51. </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.
  1. <?php
  2. //include our connection
  3. include 'dbconfig.php';
  4.  
  5. //require our Encrypt Class
  6. require 'Encrypt.php';
  7.  
  8. //declare a new object for Encrypt class
  9. $e = new Encrypt();
  10.  
  11. //get the row of selected id
  12. $sql = "SELECT rowid, * FROM members WHERE rowid = '".$_GET['id']."'";
  13. $query = $db->query($sql);
  14. $row = $query->fetchArray();
  15.  
  16. //set our encrypt from the row key
  17. $e->setKey($row['key']);
  18.  
  19. ?>
  20. <!DOCTYPE html>
  21. <html>
  22. <head>
  23. <meta charset="utf-8">
  24. <title>CRUD Operation on SQLite3 Database using PHP</title>
  25. </head>
  26. <body>
  27. <form method="POST">
  28. <a href="index.php">Back</a>
  29. <p>
  30. <label for="firstname">Firstname:</label>
  31. <input type="text" id="firstname" name="firstname" value="<?php echo $e->decrypt($row['firstname'], $row['key']); ?>">
  32. </p>
  33. <p>
  34. <label for="lastname">Lastname:</label>
  35. <input type="text" id="lastname" name="lastname" value="<?php echo $e->decrypt($row['lastname'], $row['key']); ?>">
  36. </p>
  37. <p>
  38. <label for="address">Address:</label>
  39. <input type="text" id="address" name="address" value="<?php echo $e->decrypt($row['address'], $row['key']); ?>">
  40. </p>
  41. <input type="submit" name="save" value="Save">
  42. </form>
  43. <?php
  44. if(isset($_POST['save'])){
  45. //encrypt post value
  46. $firstname = $e->encrypt($_POST['firstname']);
  47. $lastname = $e->encrypt($_POST['lastname']);
  48. $address = $e->encrypt($_POST['address']);
  49.  
  50. //update our table
  51. $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE rowid = '".$_GET['id']."'";
  52. $db->exec($sql);
  53.  
  54. header('location: index.php');
  55. }
  56. ?>
  57. </body>
  58. </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.
  1. <?php
  2. //include our connection
  3. include 'dbconfig.php';
  4.  
  5. //delete the row of selected id
  6. $sql = "DELETE FROM members WHERE rowid = '".$_GET['id']."'";
  7. $db->query($sql);
  8.  
  9. header('location: index.php');
  10. ?>
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 :)

Comments

Submitted byoleteacheron Thu, 04/12/2018 - 00:20

Another excellent and easy to follow tutorial Neovic, thank you. I have struggled to follow anything on stackoverflow and the answers there have been encrypted, LOL. You make it easy. If I may bother you with a question: Not sure how to create a "view" script with the encrypted version, keep getting errors. How can I view a single row using something like view.php?id=1 Thank you again, Susan

Hello, I believe I already have the codes for that in my edit.php. This lines of codes. //get the row of selected id $sql = "SELECT rowid, * FROM members WHERE rowid = '".$_GET['id']."'"; $query = $db->query($sql); $row = $query->fetchArray(); Make sure to include our database connection. You can then access the data from that row using something like: echo $row['firstname'].'
'; echo $row['lastname'].'
'; echo $row['address'].'
'; Comment back if your still confuse on something.

Yes Neovic, you are correct. When we attempted to modify and make a view.php, omitted the Encrypt.php by mistake. Sorry for the stupid question:)
Submitted byoleteacheron Tue, 07/10/2018 - 20:36

Hi! Hoping someone can provide some help. When setting up table: CREATE TABLE IF NOT EXISTS members (firstname STRING, lastname STRING, address STRING, key STRING) How do I populate the table with default data in dbconfig.php first time ran? Of course would not want it over writing existing data in future, only first time table is setup. Any help would be greatly appreciated. Thank you! Susan

Add new comment