MySQL Forums
Forum List  »  General

Can a MySQL query decrypt Drupal encryption>
Posted by: Vicki L.
Date: June 19, 2019 11:54AM

I do not work directly with Drupal myself and so may be missing some obvious things.
If anyone could point me in the right direction it would be greatly appreciated.

I've been asked to investigate decryption by query from a MySQL database.
Drupal 7 is used for webpages that store and display webform data in a MySQL v. 5.5 database.
Mcrypt AES CBC Mode is used for encrypting selected table columns.
I have been given the private key in clear text.
A v.8 MySQL database (that will not be accessible by web browser) has been created to hold decrypted data.
A data import of various tables would be done from the v.5.5 database to the v8 one, followed by the decryption of a number of columns in table webform_submitted_data.
Between having the private key and MySQL v8 having the additional abilities of block_encryption_mode and an initialization vector for AES_DECRYPT, it was thought decryption could be done with a database query.
A typical encrypted column has this sort of content:
a:6:{s:4:"text";s:128:"AVcdpc+pHcU4/LjBkG+LllYxGEVVdBRD0+fThxkGD7q9zUFXx+H2lcYOWK05Y5483MOGaJuM6yMNIYjcCpyx6s0JyU7d2wJMWH+jLvOSoezaVPNmGKB9/cykAnCBTiC5";s:6:"method";s:14:"mcrypt_aes_cbc";s:12:"key_provider";s:15:"drupal_variable";s:7:"options";a:1:{s:6:"base64";b:1;}s:15:"method_settings";s:0:"";s:17:"provider_settings";a:1:{s:6:"method";s:17:"variable_contents";}}

Many variations of this sort of approach have been used but without success:
mysql> SET block_encryption_mode = 'aes-256-cbc';
mysql> SET @key_str = SHA2('My private key',512);
mysql> SET @init_vector = RANDOM_BYTES(16);
mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);

Given the circumstances is it possible to decrypt the data via MySQL query?
Is there some missing piece that would make it work?

Options: ReplyQuote


Subject
Written By
Posted
Can a MySQL query decrypt Drupal encryption>
June 19, 2019 11:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.