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.
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.
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:
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.
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
The records inside the database are encrypted
You can find the files @https://github.com/bflaven/BlogArticlesExamples/tree/master/manage_potus
- Very nice article. Encryption and Decryption Form Data in PHP
- Encrypt Decrypt Hashing – PHP & MYSQL – Protect your data in your database
- The tag GDPR in Janrain resources
- MYSQL Database Encryption
- Few principles on Mysql – Encryption
- Very nice article. ENCRYPT MYSQL DATA USING AES TECHNIQUES
- Replicating MySQL AES Encryption Methods With PHP
- PHP MySQL AES encrypt/decrypt on gitbub
- How to Use MySQL’s AES_ENCRYPT and AES_DECRYPT to Store Information in a Database
- How to Encrypt & Decrypt Form Data using PHP Ajax – 1 from webslesson.info
- How to Encrypt & Decrypt Form Data using PHP Ajax – 2 from webslesson.info