MySQL, Encrypt, Decrypt – Encryption, decryption data in PHP, MySQL and some elements on the GDPR Compliance

Due to the coming GDPR (General Data Protection Regulation), that should come into force on 25th May 2018. Many organisations have to rethink their privacy policy. Indeed, GDPR establishes in terms of storage and exploitation of data a lot of new restrictions and duties.

There is a lot of literature and business around this new directive. After meeting some so-called experts, you still remain “dazed and confused” on what steps to make to be compliant with the GDPR.*

The GDPR has at list one first benefit, it brings personal data and privacy to the fore in many companies.

*In my case, I have met incompetent and arrogant consultants unable to make any recommendations that have added more mess to a situation already complex.

What has to be remembered from the GDPR? I have found this bullet points list below. Some of the points are not meaningful in my case as it has a lot to deal with hosting and IT. Matters that are not directly in the scope of my function. The point that I wanted get to grips with is the “pseudonymisation and encryption”. This one clearly belongs to my tasks.

Record keeping: each controller and processor must maintain a record of all categories of processing activities carried out.
Pseudonymisation and encryption: all personal data must be pseudonomised and/or encrypted.
Security and resilience: ensure the ongoing confidentiality, integrity, availability and resilience of processing systems and services.
Disaster recovery: the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident.
Testing and monitoring: a process for regularly testing, assessing and evaluating the effectiveness of technical and organisational measures for ensuring the security of the processing.
Breach notification: a personal data breach must be notified without undue delay and, where feasible, not later than 72 hours.

Source: https://www.itproportal.com/features/how-enterprise-file-services-can-help-ensure-gdpr-compliance/

Encrypted data

Let’s say we create 2 tables in a database named encrypt_db, just to point out the differences.

The table with with fields in clear

-- NON-ENCRYPTED TABLE
CREATE  TABLE user_non_encrypted_ex (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
password VARCHAR(100) NOT NULL DEFAULT '',
address VARCHAR(200) NOT NULL DEFAULT '',
salt VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The same table with “crypted” fields

-- ENCRYPTED TABLE
CREATE  TABLE user_encrypted_ex (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARBINARY(100) NOT NULL DEFAULT '',
password VARBINARY(100) NOT NULL DEFAULT '',
address VARBINARY(200) NOT NULL DEFAULT '',
salt VARBINARY(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, I add to insert some dummy data. Here they are. As usual, I like using US presidents as dummy data.

-- user 1
username : Donald Trump
password : Russian_Roulette
address : White-House
salt : Melania
key : Mar-a-Lago
 
-- user 2
username : Barack Obama
password : Michelle_Mabelle
address : Illinois
salt : Hillary
key : Kenya
 
-- user 3
username : George W. Bush
password : Howdy_My_Password
address : Texas
salt : Bretzel
key : Saddam

Give honour where honour is due, I start with “Joli Toupet”. Here is the main queries for Donald Trump.

-- insert user non encrypted
INSERT INTO user_non_encrypted_ex (id, username, password, address, salt) VALUES (NULL, 'Donald Trump', 'Russian_Roulette', 'White-House', 'Melania');
 
-- insert same user encrypted
INSERT INTO user_encrypted_ex (id, username, password, address, salt) VALUES (NULL, AES_ENCRYPT('Donald Trump', 'Mar-a-Lago'), AES_ENCRYPT(CONCAT('Russian_Roulette','Melania'),'Mar-a-Lago'), AES_ENCRYPT('White-House', 'Mar-a-Lago'), AES_ENCRYPT('Melania', 'Mar-a-Lago'));
 
-- DETAIL FOR EACH FIELD
-- username => AES_ENCRYPT('Donald Trump', 'Mar-a-Lago')
-- password => AES_ENCRYPT(CONCAT('Russian_Roulette','Melania'),'Mar-a-Lago')
-- username => AES_ENCRYPT('White-House', 'Mar-a-Lago')
-- username => AES_ENCRYPT('Melania', 'Mar-a-Lago')
 
-- query_1 : select encrypt data 
SELECT AES_DECRYPT(username, 'Mar-a-Lago'), AES_DECRYPT(address, 'Mar-a-Lago') FROM user_encrypted_ex;
-- Output : Donald Trump, White-House
 
-- query_4: select encrypt password
SELECT AES_DECRYPT(password, 'Mar-a-Lago') FROM user_encrypted_ex;
-- Output : Russian_RouletteMelania
 
-- query_3: select encrypt password retrieve the salt
SELECT REPLACE(CAST(AES_DECRYPT(password,'Mar-a-Lago') AS CHAR(100)), AES_DECRYPT(salt, 'Mar-a-Lago'), '') FROM user_encrypted_ex;
-- Output : Russian_Roulette
 
-- DETAIL FOR PASSWORD
-- password => REPLACE(CAST(AES_DECRYPT(password,'Mar-a-Lago') AS CHAR(100)), AES_DECRYPT(salt, 'Mar-a-Lago'), '')
 
-- query_4: select all
SELECT AES_DECRYPT(username, 'Mar-a-Lago'), REPLACE(CAST(AES_DECRYPT(password,'Mar-a-Lago') AS CHAR(100)), AES_DECRYPT(salt, 'Mar-a-Lago'), ''), AES_DECRYPT(address, 'Mar-a-Lago') FROM user_encrypted_ex;

The first query (query_1), a select query of encrypted data : username, address

The second query (query_2), a select query of encrypted data : password

The third query (query_3), a select query of encrypted data : password without the salt string

The fourth query (query_4), a select query of encrypted data : username, address, password without the salt string

BAD: the table user_non_encrypted_ex from encrypt_db

GOOD: the table user_encrypted_ex from encrypt_db with same information encrypted.

A note on decrypting password

Don’t do it! The ability to decrypt user’s passwords is made only for fun and the purpose of this post to understand encrypt, decrypt methods with a salt system in particular. If you’re storing user passwords, you should hash them with sha1 or md5, not encrypt them. There’s really no valid use case for decrypting customer passwords. The best thing to do, it is just to think of a recovery password procedure. So the users can completely reset their password rather than your application emails them their current.

POC : a mini CRUD with encrypted data

I found a great article on webslesson.info. It is the next step after understanding how to encrypt data. This step is how to manipulate data with the help of PHP, in a secure way with encryption and decryption.

The funny thing is that the videos available on youtube, see below for the links, has been recorded and translated with a non-human voice from Google.

Source : http://www.webslesson.info/2017/12/encryption-and-decryption-form-data-in-php.html

In function.php, the scripting method, the most important part, that is the key to encrypt and decrypt the data.

 $encrypt_method = "AES-256-CBC";
    $secret_key = 'i7fh3x68dch#jh1ey0s+j9$(h128+(i9g)725*k0grt!'; // 44 characters
    $secret_iv = '8mgo+i40!l-jtr!fb@vb='; // 21 characters

For those, you want to read more on Encryption Standard especially on AES-256-CBC:
https://en.wikipedia.org/wiki/Advanced_Encryption_Standard

Things to remember about security

Apparently, if your database is hacked, it will be impossible to decrypt the data. The idea is to protect the key as much you can, be not a mule smuggling drugs for cartels. The idea is to keep this api key in the safer place possible. So apparently, the best idea is to “use an ini file that’s read at runtime and that is not publicly accessible within the scope of the Web server”.

Fully rely on the MySQL decryption and encryption abilities may be very problematic if the database has internal failures. It will render your application unusable.
No need to solicit the decryption and encryption abilities of MySQL. Using PHP may optimise the speed and efficiency of your application.
MySQL often logs transactions, so if the database’s server has been compromised, then the log file would produce both the encryption key and the original value.

Source: https://www.smashingmagazine.com/2012/05/replicating-mysql-aes-encryption-methods-with-php/

What is an initialization vector?
I was intrigued by the value secret_iv. Just for my personal information. iv stands for initialization vector (IV). An initialization vector (IV) is an arbitrary number that can be used along with a secret key for data encryption. This number, also called a nonce, is employed only one time in any session. The use of an IV prevents repetition in data encryption, making it more difficult for a hacker using a dictionary attack to find patterns and break a cipher.

Enter some records inside the Dashboard