{"id":11064,"date":"2018-02-10T11:12:45","date_gmt":"2018-02-10T10:12:45","guid":{"rendered":"http:\/\/flaven.fr\/?p=11064"},"modified":"2021-03-23T08:07:06","modified_gmt":"2021-03-23T07:07:06","slug":"mysql-encrypt-decrypt-encryption-decryption-data-in-php-mysql-and-some-elements-on-the-gdpr-compliance","status":"publish","type":"post","link":"https:\/\/flaven.fr\/2018\/02\/mysql-encrypt-decrypt-encryption-decryption-data-in-php-mysql-and-some-elements-on-the-gdpr-compliance\/","title":{"rendered":"MySQL, Encrypt, Decrypt – Encryption, decryption data in PHP, MySQL and some elements on the GDPR Compliance"},"content":{"rendered":"

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.<\/p>\n

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.*<\/i><\/p>\n

The GDPR has at list one first benefit, it brings personal data and privacy to the fore in many companies.<\/b><\/p>\n

*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.<\/i><\/p>\n

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.<\/b><\/p>\n

    \n
  1. Record keeping: each controller and processor must maintain a record of all categories of processing activities carried out.<\/li>\n
  2. Pseudonymisation and encryption: all personal data must be pseudonomised and\/or encrypted.<\/b><\/li>\n
  3. Security and resilience: ensure the ongoing confidentiality, integrity, availability and resilience of processing systems and services.<\/li>\n
  4. 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.<\/li>\n
  5. 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.<\/li>\n
  6. Breach notification: a personal data breach must be notified without undue delay and, where feasible, not later than 72 hours.<\/li>\n<\/ol>\n

    Source: https:\/\/www.itproportal.com\/features\/how-enterprise-file-services-can-help-ensure-gdpr-compliance\/<\/a><\/p>\n

    Encrypted data<\/h2>\n

    Let’s say we create 2 tables in a database named encrypt_db<\/code>, just to point out the differences.<\/p>\n

    The table with with fields in clear<\/b><\/p>\n

    \r\n-- NON-ENCRYPTED TABLE\r\nCREATE  TABLE user_non_encrypted_ex (\r\nid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\r\nusername VARCHAR(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',\r\npassword VARCHAR(100) NOT NULL DEFAULT '',\r\naddress VARCHAR(200) NOT NULL DEFAULT '',\r\nsalt VARCHAR(20) NOT NULL DEFAULT '',\r\nPRIMARY KEY (id)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\r\n<\/pre>\n
    The same table with “crypted” fields<\/b><\/p>\n
    \r\n-- ENCRYPTED TABLE\r\nCREATE  TABLE user_encrypted_ex (\r\nid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\r\nusername VARBINARY(100) NOT NULL DEFAULT '',\r\npassword VARBINARY(100) NOT NULL DEFAULT '',\r\naddress VARBINARY(200) NOT NULL DEFAULT '',\r\nsalt VARBINARY(20) DEFAULT NULL,\r\nPRIMARY KEY (id)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\r\n<\/pre>\n
    Now, I add to insert some dummy data. Here they are. As usual, I like using US presidents as dummy data.<\/p>\n
    \r\n-- user 1\r\nusername : Donald Trump\r\npassword : Russian_Roulette\r\naddress : White-House\r\nsalt : Melania\r\nkey : Mar-a-Lago\r\n\r\n-- user 2\r\nusername : Barack Obama\r\npassword : Michelle_Mabelle\r\naddress : Illinois\r\nsalt : Hillary\r\nkey : Kenya\r\n\r\n-- user 3\r\nusername : George W. Bush\r\npassword : Howdy_My_Password\r\naddress : Texas\r\nsalt : Bretzel\r\nkey : Saddam\t\r\n<\/pre>\n
    Give honour where honour is due, I start with “Joli Toupet”. Here is the main queries for Donald Trump.<\/p>\n
    \r\n-- insert user non encrypted\r\nINSERT INTO user_non_encrypted_ex (id, username, password, address, salt) VALUES (NULL, 'Donald Trump', 'Russian_Roulette', 'White-House', 'Melania');\r\n\r\n-- insert same user encrypted\r\nINSERT 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'));\r\n\r\n-- DETAIL FOR EACH FIELD\r\n-- username => AES_ENCRYPT('Donald Trump', 'Mar-a-Lago')\r\n-- password => AES_ENCRYPT(CONCAT('Russian_Roulette','Melania'),'Mar-a-Lago')\r\n-- username => AES_ENCRYPT('White-House', 'Mar-a-Lago')\r\n-- username => AES_ENCRYPT('Melania', 'Mar-a-Lago')\r\n\r\n-- query_1 : select encrypt data \r\nSELECT AES_DECRYPT(username, 'Mar-a-Lago'), AES_DECRYPT(address, 'Mar-a-Lago') FROM user_encrypted_ex;\r\n-- Output : Donald Trump, White-House\r\n\r\n-- query_4: select encrypt password\r\nSELECT AES_DECRYPT(password, 'Mar-a-Lago') FROM user_encrypted_ex;\r\n-- Output : Russian_RouletteMelania\r\n\r\n-- query_3: select encrypt password retrieve the salt\r\nSELECT REPLACE(CAST(AES_DECRYPT(password,'Mar-a-Lago') AS CHAR(100)), AES_DECRYPT(salt, 'Mar-a-Lago'), '') FROM user_encrypted_ex;\r\n-- Output : Russian_Roulette\r\n\r\n-- DETAIL FOR PASSWORD\r\n-- password => REPLACE(CAST(AES_DECRYPT(password,'Mar-a-Lago') AS CHAR(100)), AES_DECRYPT(salt, 'Mar-a-Lago'), '')\r\n\r\n-- query_4: select all\r\nSELECT 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;\r\n<\/pre>\n
    The first query (query_1), a select query of  encrypted data : username, address<\/b>
    \n\"MySQL,<\/p>\n
    The second query (query_2), a select query of  encrypted data : password<\/b>
    \n\"MySQL,<\/p>\n
    The third query (query_3), a select query of  encrypted data : password without the salt string<\/b>
    \n\"MySQL,<\/p>\n
    The fourth query (query_4), a select query of  encrypted data : username, address, password without the salt string<\/b>
    \n\"MySQL,<\/p>\n
    BAD: the table user_non_encrypted_ex from encrypt_db<\/b>
    \n\"MySQL,<\/p>\n
    GOOD: the table user_encrypted_ex from encrypt_db with same information encrypted.<\/b>
    \n\"MySQL,<\/p>\n
    A note on decrypting password<\/h2>\n
    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.<\/b> 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.<\/p>\n
    POC : a mini CRUD with encrypted data<\/h2>\n
    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.<\/b><\/p>\n
    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.<\/i><\/p>\n
    Source : http:\/\/www.webslesson.info\/2017\/12\/encryption-and-decryption-form-data-in-php.html<\/a><\/p>\n
    In function.php, the scripting method, the most important part, that is the key to encrypt and decrypt the data.<\/b><\/p>\n
    \r\n $encrypt_method = \"AES-256-CBC\";\r\n    $secret_key = 'i7fh3x68dch#jh1ey0s+j9$(h128+(i9g)725*k0grt!'; \/\/ 44 characters\r\n    $secret_iv = '8mgo+i40!l-jtr!fb@vb='; \/\/ 21 characters\r\n<\/pre>\n
    For those, you want to read more on Encryption Standard especially on AES-256-CBC:
    \n    https:\/\/en.wikipedia.org\/wiki\/Advanced_Encryption_Standard<\/a><\/p>\n
    Things to remember about security<\/h2>\n
    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”.<\/p>\n
      \n
    1. 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.<\/li>\n
    2. No need to solicit the decryption and encryption abilities of MySQL. Using PHP may optimise the speed and efficiency of your application.<\/li>\n
    3. MySQL often logs transactions, so if the database\u2019s server has been compromised, then the log file would produce both the encryption key and the original value.<\/li>\n<\/ol>\n
      Source: https:\/\/www.smashingmagazine.com\/2012\/05\/replicating-mysql-aes-encryption-methods-with-php\/<\/a><\/p>\n
      What is an initialization vector?<\/b>
      \nI 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.<\/p>\n
      Enter some records inside the Dashboard<\/b>
      \n\"MySQL,<\/p>\n
      The records inside the database are encrypted<\/b>
      \n\"MySQL,<\/p>\n
      You can find the files @https:\/\/github.com\/bflaven\/BlogArticlesExamples\/tree\/master\/manage_potus<\/a><\/p>\n
      Read more<\/h2>\n