John Davidson

Message:

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

---

My Answer:
Laravel's encryption key for decryption. Here's how you can decrypt the values in your migration script:

sql
SET @key = 'your_laravel_app_key_here';

SELECT AES_DECRYPT(FROM_BASE64(valueA), UNHEX(SHA2(@key, 512))) FROM test.table;


Make sure to replace 'your_laravel_app_key_here' with your actual Laravel encryption key. This should allow you to decrypt the values in your migration script.