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?

Is decrypting the database and re-encrypting it with Laravel an option? — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Mar 14, 2018 at 22:34

Sammitch · Accepted Answer · 2018-03-14 22:45:17Z

4

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"

answered Mar 14, 2018 at 22:45

Sammitch

32.5k7 gold badges58 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lorenzo Over a year ago

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.

ctietze Over a year ago

@Sammitch can you explain what's wrong with the per-field encryption?

Collectives™ on Stack Overflow

Decrypt Data in Laravel that was Encrypted Using AES_ENCRYPT in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related