-3

I have a php program that stores encrpyted data in the database using openssl_encrypt.

I need to be able to decrypt this data in mysql to perform queries on it.

The problem is that im not sure what the value of my key should be in the AES_DECRYPT function of mysql.

My php code uses a base64 key that decrypts to a non ascii value.

The code base64 encodes the encrypted value and adds the IV at the end, so in mysql i decode and split this. All of that is working fine, but when i go to decrypt it returns null. presumably because i dont know how to put the key in correctly.

Ive tried UNHEX("key") FROM_BASE64("key") etc

function encrypt($data)
{
    if (!$data) {
        return null;
    }
    $key = "lvvs9pxoekc54zsBpsp+/H1235BdjsAscdRRAxr47uw="; //this is not my real key
    $encryption_key = base64_decode($key);
    $hex = bin2hex($encryption_key);
    $iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length('aes-256-cbc'));
    $encrypted = openssl_encrypt($data, 'aes-256-cbc', $encryption_key, 0, $iv);
    return base64_encode($encrypted . '::' . $iv);
}

In mysql i am doing this

    AES_DECRYPT(
FROM_BASE64(SUBSTRING_INDEX( 
        UPPER(
            CAST(
                FROM_BASE64(member_ssn)
                AS CHAR(1000)
            )
        ), '::', 1))
        , '*KEY* what do i need to put here?') as decrpyted_string
jarlh
  • 42,561
  • 8
  • 45
  • 63
Bigbob556677
  • 1,805
  • 1
  • 13
  • 38
  • If you, or anybody else knows the key in order to feed it into a manual query, then quite frankly **there is no point in encrypting the data in the first place** The key is after all ___The family Jewels___ – RiggsFolly Jan 17 '23 at 18:00
  • I know the key because im the one writing the code, it works in PHP but im not sure how to translate that same key into mysql. @RiggsFolly – Bigbob556677 Jan 17 '23 at 18:01
  • Yes, but as soon as you start passing that around the possibility for loosing it to the wild increases exponentially – RiggsFolly Jan 17 '23 at 18:02
  • Hard coding the key in a php script is a long long way from secure as well – RiggsFolly Jan 17 '23 at 18:03
  • @RiggsFolly this is a minimum example not using env files etc. – Bigbob556677 Jan 17 '23 at 18:03
  • @RiggsFolly MySql has a AES_ENCRYPT and AES_DECRYPT function that both require a key to be passed to it. I just need to know how to pass this key. or rather, what format this key should be in. – Bigbob556677 Jan 17 '23 at 18:04
  • the alghorithms don't match even f they have the same name, so you can only decrypt something with the same tool that you encrypted it – nbk Jan 17 '23 at 18:05
  • @nbk they are both using `aes-256-cbc` – Bigbob556677 Jan 17 '23 at 18:06
  • @Bigbob556677 that doesn't matter, the implementation differ, so they will never ever match – nbk Jan 17 '23 at 18:06
  • @nbk I dont believe that is correct as there are numerous examples of doing so. e.g https://stackoverflow.com/questions/69099998/cant-aes-decrypt-mysql-a-string-encrypted-with-openssl-encrypt-php – Bigbob556677 Jan 17 '23 at 18:12
  • no both binary differ, tested numerous times, some times even versionsnumber differ in implmentation – nbk Jan 17 '23 at 18:16
  • I don't have an answer to your question, but if you have a (relatively) minimal amount of columns that need to be encrypted and searched, and they aren't just free-form text blobs, you might benefit from blind indexes. I know you've probably got an implementation already down this path, but I'm just throwing it out just in case. – Chris Haas Jan 17 '23 at 18:42
  • @ChrisHaas the best thing we have right now is querying ALL the rows and filtering in php. (i inherited this mess haha) – Bigbob556677 Jan 17 '23 at 18:53
  • @Bigbob556677, yeah, I get it, we've all been there. If this is a "nice-to-have" "upgrade feature", it might be worth pushing back to make sure people understand why this approach isn't recommended. But if you gotta build it, you gotta build it! – Chris Haas Jan 17 '23 at 19:14
  • @nbk i was able to get it working. – Bigbob556677 Jan 19 '23 at 04:26

1 Answers1

1

I got this working so i will post my solution for those who care.

The code performs the following opperations.

  • takes a base64 value and decodes it
  • this results in a value of base64cryptstring::IV
  • then splits the string at the :: to get the IV and crypt string separated
  • then it uses these values and a FROM_BASE64 on the key itself to get the decrpyted value
SELECT dec_data.member_id, CONVERT(dec_data.dec_ssn USING LATIN1) as dec_ssn FROM (
                    select 
                        *,                        
                        CAST(
AES_DECRYPT(
    FROM_BASE64(SUBSTRING_INDEX(CAST(FROM_BASE64(member_ssn)AS CHAR(1000)), '::', 1)), 
    FROM_BASE64('yourbase64keyhere=='),                         
    SUBSTRING_INDEX(SUBSTRING_INDEX(FROM_BASE64(member_ssn), '::', 2), '::', -1)
) AS CHAR) as dec_ssn 
                        from lb_1_members where member_ssn is not null AND member_ssn != '') dec_data
Bigbob556677
  • 1,805
  • 1
  • 13
  • 38