Decrypt Laravel encrypted values

user_9898348 0
Viewed 1377

I have a Laravel application that uses encrypt and decrypt with the default Laravel Encryptable trait.

Now I need to migrate the database to another one.

I've created an SQL script that handle data migration, but during the migration I need to make some checks on some fields. For example:

insert into test.table
            (valueA, valueB, valueC) 
SELECT      tb.a, tb.b, tc.c
from        db2.tableB tb
join        test.tableC tc on tb.id = tc.tb_id;

UPDATE      test.table
SET         valueD =  CASE  
            WHEN valueA = 'example' THEN 1 
            ELSE 0
END;

The problem is that some of these fields are encrypted. For example valueA will be something like:

eyJpdiI6ImtTTUthN0dFT2N3Qkt6REdaamIyWGc9PSIsInZhbHVlIjoiakFIa0RFTVdCSU1LM1hWY3NUM0tVUT09IiwibWFjIjoiOGI1ZjY5ODVkYzMwYTIxMTExMzgyNjZmMTViNDllZTUzYWUxNzEyMWEwNmM5M2RmZDQzNzcwMDY4MjlkYzVlOCJ9

I tried to use AES_DECRYPT() using the APP_KEY I have in Laravel, but the scripts always returns null. For example:

SELECT AES_DECRYPT(from_base64(valueA),'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from 
test.table;

Always returns NULL.

Obviously the key is hidden for the purpose of this post.

I think that this is due to the fact that Laravel uses open_ssl when encrypting while AES_DECRYPT doesn't. Am I right?

Is there a way to decrypt the Database before running my migration script or to decrypt the value in the SELECT statement during the update?

UPDATE:

I've set up MySql in order to use "AES-256-CBC". Keep in mind that in Laravel the APP_KEY is something like: "base64:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

So this is what I'm doing now:

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = to_base64('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
SET @init_vector = (SELECT LEFT(CONVERT(encryptedVal USING binary), 16)  from tableA where id =1);
SELECT encryptedVal, AES_DECRYPT(encryptedVal,@key_str,@init_vector) from tableA where id = 1;

BTW I'm still having null values. Any help will be appreciated

laravel encryption mysql php
1 Answers

If anyone needs I ended in creating a script called from Laravel that decrypts the tables and the fields I need:

class DecryptDatabase extends Command
{
    protected $tables = (array(
        "tableA" => array(
           "pk" => 'id',
           "encrypted" =>  array('valueA', 'valueB', 'valueC'),
           "name" => "tableA"
           ),
        "users" => array(
            "pk" => 'id',
            "encrypted" =>  array('name', 'surname', 'profession'),
            "name" => "users"
            ),
        ));

    public function handle()
    {
        foreach($this->tables as $t) {
            $columns = implode(',', $t['encrypted']);
            $q = DB::connection('mysql2')->select('select ' . $t['pk']. ','. $columns . ' from '. $t['name']);
            foreach($q as $result)
            {
                $pk = $t['pk'];
                try {
                    foreach($t['encrypted'] as $enc) {
                        if($result->$enc != null){
                            $decrypted = Crypt::decrypt($result->$enc);
                            $query = DB::connection('mysql3')
                            ->table($t['name'])
                            ->where($t['pk'], $result->$pk)
                            ->update([$enc => $decrypted]);
                        }
                    }
                } catch (DecryptException $e) {
                    echo ("Error in decryption: ". $e);
                }
            }
        }  
        echo "Decryption terminated";
        return 0;
    }
}

This scripts decrypts all the fields defined in the $tables variable. It uses a database defined in the "mysql2" connection as the source of the encrypted values and then stores the decrypted values in a database defined in the "mysql3" connection.

The first DB is the original one while the second is an exact copy of the first. I did this in order to have the original DB untouched while decrypting, to prevent data corruption in case of some error during decryption.

After decrypting the DB I can run my migration script properly and then encrypt the DB again doing the inverse of the decrypt function.

user_98983480
Related