GDPR : How To Encrypt Personal Data In MySQL using KMS ?

Before going towards how to secure Personal data for GDPR and get knowledge about how to encrypt and decrypt MySQL data using Amazon Key Management Service ( KMS ). Let me explain why it is required to encrypt user data for GDPR compliance.

Full form of GDPR is “General Data Protection Regulation” which states so many set of rules for user data protection. Any website should follow these rules if it uses or stores end user data in any form. This is the reason why most of the websites need to display a cookie notice/policy. In accordance with using website functionality user will have to accept these policies. They also present their cookie and privacy policies on how they using the end user data.

Prerequisites :

• AWS Account.
• Install Composer.
• PHP 7+ / Mysql / Mariadb
• A Customer Managed Key from AWS KMS.

Now going to talk about encryption of user’s personal data. As per my knowledge, it may be recommendation to encrypt user data for security and integrity reasons. As per GDPR policies, I found that “According to GDPR it is recommended to encrypt user’s personal data using a key“.

Now i am going to discuss the main process of encrypting user’s personal data. We are going to discuss with an example in reference with the following tables :

1. credit_cards ( To save credit-card information ).
2. key_string ( To save secret key ).

CREATE TABLE credit_cards (
   id int(11) NOT NULL,
   card_info blob DEFAULT NULL,
   ut timestamp NOT NULL DEFAULT current_timestamp()
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE key_string (
   id int(11) NOT NULL,
   key blob DEFAULT NULL,
   ut timestamp NOT NULL DEFAULT current_timestamp()
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO key_string (id, key) VALUES (1, null);

Select your database and execute above query in your mysql terminal.

composer require aws/aws-sdk-php

Install the AWS SDK for PHP Using above Command.

Now Create a new php file called kms_class.php and write following code :

<?php
require './vendor/autoload.php';
 
use Aws\Kms\KmsClient;
use Aws\Exception\AwsException;

class KMS {

    private $keyId = 'aws_customer_managed_key_id';
    private $keystring = null;
 
    public function __construct() {
        //$this->update_key("secret_key");
        $this->keystring = $this->get_key();
    }

    private function initClient()
    {
         // Hard-coded credentials.
         $KmsClient = new Aws\Kms\KmsClient([
             'version' => 'latest',
             'region' => 'ap-south-1',
             'credentials' => [
                     'key'    => 'aws_user_key',
                     'secret' => 'aws_user_secret',
                 ],
         ]);

         return $KmsClient; 
    }

    private function mysqlConnection()
    {
       // Create connection
       $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

       // Check connection
       if ($conn->connect_error) {         
          die("Connection failed: ".$conn->connect_error);
       }

       return $conn; 
    }

    private function encrypt($plaintext)
    {
         try {

             $KmsClient = $this->initClient();
             $result = $KmsClient->encrypt([
                         'KeyId' => $this->keyId,
                         'Plaintext' => $plaintext,
                       ]);         
             return $result['CiphertextBlob'];
         }
         catch (AwsException $e) {
             // output error message if fails
             echo $e->getMessage();
             echo "\n";
         }
    } 

    private function decrypt($CiphertextBlob) 
    {
         try {

            $KmsClient = $this->initClient();
            $result = $KmsClient->decrypt([
               'CiphertextBlob' => $CiphertextBlob,
            ]);
            return $result['Plaintext'];
         }      
         catch (AwsException $e) {
            // Output error message if fails         
            echo $e->getMessage();
            echo "\n";
         }
    }

    private function get_key()
    {
       try {

         $mysqli = $this->mysqlConnection();
         // Perform query 
        $result = $mysqli->query("SELECT `key` FROM `key_string` WHERE `id`=1 LIMIT 1"); 

         if ($result->num_rows > 0) {
             // output data of each row 
             $row = $result->fetch_assoc();
             return $this->decrypt($row['key']);
         }
         else {
             throw "0 results";
         } 
         
         $mysqli -> close();
      }
      catch (AwsException $e) {
         // Output error message if fails
         echo $e->getMessage(); 
         echo "\n";
     } 
} 

  private function update_key($secret_key) {
     try { 
        $mysqli = $this->mysqlConnection();
         // Perform query
         if ($mysqli->query("UPDATE `key_string` SET `key`='".($this->encrypt($secret_key))."' WHERE `id`=1")) { 
            echo "Update key Success.";
         }
         $mysqli->close();
     }
      catch (AwsException $e) {
         // output error message if fails
         echo $e->getMessage();
         echo "\n";
     } 
  }



    public function add()
    {
         $mysqli = $this->mysqlConnection();

         $card_info = json_encode([
             "full_name" => "Customer Name",
              "card_no" => "1111 2222 3333 4444",
              "cvv" => '1234',
              'expiry' => '12/1990' 
         ]);

         // Perform query
         if ($mysqli->query("INSERT INTO credit_cards SET card_info=AES_ENCRYPT('".$card_info."', '".$this->keystring."')")) {
             echo "Add Success"; 
        }
    } 

    public function fetch() 
    { 
       $mysqli = $this->mysqlConnection();
       // Perform query 
       $result = $mysqli->query("SELECT AES_DECRYPT(`card_info`, '".$this->keystring."') AS `card_info` FROM `credit_cards` WHERE `card_info` IS NOT NULL");

       if ($result->num_rows > 0) {
         // output data of each row 
         while ($row = $result->fetch_assoc()) { 
            pre(json_decode($row['card_info']));
         }
         // Free result set
         $result->free_result();
       }
       else {
         echo "0 results";
       }
       $mysqli -> close();
     }

}

Create a new file called index.php and write following code :

<?php
    require './kms_class.php';

    $obj = new KMS();

    // update your existing secret key.
    $obj->update_key('secret_key'); 

    // add data into database.
    $obj->add(); 

    // fetch data from database.
    $obj->fetch(); 
   

Now you are ready to use your index.php