Decrypt Data in Laravel that was Encrypted Using AES_ENCRYPT in MySQL

Question

I have a legacy database that is being integrated with Laravel.

Some of the varbinary fields in the database were being stored with AES_ENCRYPT in a raw query on the legacy app. Within an Eloquent model on Laravel I want to write an accessor that decrypts this data when I need it without having to make an extra query to the database. How would I go about this?

The legacy app was encrypting data as follows: AES_DECRYPT(first_name, ?) where ? refers to the key which was being passed in as sha1('mykeyhere').

Any ideas?

https://stackoverflow.com/questions/3422759/php-aes-encrypt-decrypt — Jonas Staudenmeir, Mar 14 '18 at 21:53
Is decrypting the database and re-encrypting it with Laravel an option? — Jonas Staudenmeir, Mar 14 '18 at 22:34

score 4 · Answer 1 · answered Mar 14 '18 at 22:45

No one should ever have done any of the things that resulted in this situation, but here's how to decode it with a non-deprecated API.

/**
* @param string $crypted    Un-encoded input
* @param string $key        Key string provided to mysql AES_ENCRYPT();
* @return string
**/
function mysql_aes_decrypt($crypted, $key) {
    // mysql will happily fudge your key for you, so we do too
    $keylen = strlen($key);
    if( $keylen < 16 ) {
        $key = str_pad($key, 16, "\0");
    } else if( $keylen > 16 ) {
        $key = substr($key, 0, 16);
    }
    return openssl_decrypt($crypted, 'AES-128-ECB', $key, OPENSSL_RAW_DATA);
}

$plain = 'foo';
$key = 'bar';
// SELECT AES_ENCRYPT('foo', 'bar')
$crypted = hex2bin('93F254924801B8B0F000571DFD8C4A5E');

var_dump( mysql_aes_decrypt($crypted, $key) ); // string(3) "foo"

I am in agreement with you on your opinion lol the legacy code is pretty bad. What I originally tried was very similar to this but I didn't realize MySQL messes with the key size. For some reason this does not work though. — lorenzo, Mar 15 '18 at 14:18
@Sammitch can you explain what's wrong with the per-field encryption? — ctietze, Nov 14 '20 at 07:20

Decrypt Data in Laravel that was Encrypted Using AES_ENCRYPT in MySQL

1 Answers1