Decrypt Laravel encrypted values

Question

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

I found an indication that Laravel uses AES + HMAC for integrity / authentication, but no specification yet, as if they don't want you to find it on purpose. Probably it uses this. — Maarten Bodewes
– Maarten Bodewes, Commented Nov 15, 2020 at 2:17
Hi @MaartenBodewes. Yes it uses the Trait you linked. Based on what you read there do you know how (if) I can decrypt directly in MySql Workbench? — Federico Arona
– Federico Arona, Commented Nov 15, 2020 at 14:58
I got a bit confused, either it's IV + CBC(plaintext) or it is IV + CBC(plaintext) + MAC. Looking at AES_DECRYPT, it defaults to ECB mode using a system variable, and it includes the IV in the parameters, not in the ciphertext. As most DB encryption functions, it's kind of stupid beyond belief; relatively little database encryption seems to be designed by experts. But yeah, change the mode to CBC, extract the IV from the ciphertext (first 16 bytes) and then decrypt. If it doesn't work it's probably because of the MAC still being present. — Maarten Bodewes
– Maarten Bodewes, Commented Nov 15, 2020 at 15:49
Thank you again for your answer @MaartenBodewes. I was trying to change the mode to CBC in MySql with SET block_encryption_mode = 'aes-256-cbc. BTW I get the following error: Error Code: 1193. Unknown system variable 'block_encryption_mode' . That's sounds really strange to me becasue if I run SHOW VARIABLES LIKE "%version%"; i get 'version', '10.4.11-MariaDB' so the block_encryption_mode should be present in the version I have. Any idea why that's happening? — Federico Arona
– Federico Arona, Commented Nov 15, 2020 at 16:03
Nope, maybe ask a separate question about it after searching. SET block_encryption_mode = 'aes-256-cbc'; literally seems to be present in the documentation. I've tried staying away a bit from DB programming, I'm more a security expert. — Maarten Bodewes
– Maarten Bodewes, Commented Nov 15, 2020 at 16:16

Federico Arona · Accepted Answer · 2020-11-24 15:54:37Z

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.

user9897505 · Accepted Answer · 2023-04-08 16:30:31Z

0

Maybe someone else is figuring this out. I had only one column (date of birth) that was encrypted, I figured I'll do it like this:

//find all encrypted date of birth. The ones that are encrypted have a longer length
    $users = User::whereRaw('LENGTH(dob) > ?', [12])->get();
    
    $string = "";
    foreach ($users as $user){
        $string .= "UPDATE `users` SET `dob` = '".Crypt::decryptString($user->dob)."' WHERE `users`.`id` = ".$user->id.";";
    }

    return $string;

Then simply use the string

answered Apr 8, 2023 at 16:30

user9897505

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Decrypt Laravel encrypted values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related