How can i solve Mysql AES encryption and decryption issues

[pandglobal]

Hello house,
I used Mysql aes encryption to encrypt a column, and i can also decrypt the column in a SELECT query to return the actual text value.
However my problem is i want to be able to decrypt the encrypted data using php since i have the keys and the cipher and iv values, but is not working

$encryptionKey = 'myenckey';
$newiv = bin2hex(random_bytes(8));  //gives a 16 digits
$con->prepare("UPDATE subscriptions SET marginbalance = TO_BASE64(CONCAT('$newiv', AES_ENCRYPT(COALESCE(AES_DECRYPT(SUBSTRING(FROM_BASE64(marginbalance),17), '$encryptionKey',SUBSTRING(FROM_BASE64(marginbalance),1,16)),0) + '$amount', '$encryptionKey','$newiv'))),

To decrypt and get the value in msql i did this

// SELECT - decrypt
$encryptionKey = 'myenckey';
$search = $olanzarmanagercon->prepare("SELECT AES_DECRYPT(SUBSTRING(FROM_BASE64(marginbalance),17), '$encryptionKey', SUBSTRING(FROM_BASE64(marginbalance),1,16)) AS marginbalancenew FROM subscriptions WHERE userid = '12'");
$search->execute();
$result = $search->fetch(PDO::FETCH_ASSOC);

This gives my the value but i am trying to create a php function that will decrypt the encrypted data without using msql to do that, i have this php code yet it returns empty

```
function show($data) {
    $key = 'myenckey';
    $cipher = 'aes-256-cbc';

    // Decode from Base64
    $decodedData = base64_decode($data);

    // Extract IV and encrypted data
    $iv = substr($decodedData, 0, 16);
    $encrypted = substr($decodedData, 16);

    // Decrypt the data
    $decrypted = openssl_decrypt($encrypted, $cipher, $key, 0, $iv);

    return $decrypted;
}

Now this returns empty

echo show('OTA4OGU4YjY5NGUyMzc4ZFZYPqyoBNCzIwokVddSP7k=');

Please what do you think i should do to have it decrypt in php

[benanamen]

If your going to do this in code, follow KISS.

<?php

class AES
{
    const KEY = '1234567890123456';
    const IV = '1234567890123456';

    public function decode($str)
    {
        $decrypted = openssl_decrypt(base64_decode($str), "AES-128-CBC", self::KEY, OPENSSL_RAW_DATA, self::IV);

        if ($decrypted === false) {
            throw new \Exception('Decryption failed');
        }

        return $decrypted;
    }

    public function encode($str)
    {
        $encrypted = openssl_encrypt($str, "AES-128-CBC", self::KEY, OPENSSL_RAW_DATA, self::IV);

        if ($encrypted === false) {
            throw new \Exception('Encryption failed');
        }

        return base64_encode($encrypted);
    }
}

try {
    $aes = new AES();
    $encryptedMessage = $aes->encode('My Secret Message');

    var_dump($encryptedMessage); // '4jZf0a8oV3Xa5e0TyI7EcLAI3FGstD9Hn6teGkzjFIQ=
    var_dump($aes->decode($encryptedMessage)); // My Secret Message
} catch (\Exception $e) {
    echo 'Error: ' . $e->getMessage();
}

[pandglobal]

Thanks a million times i have spent hours on this without success.
Will run the code and then add it to database to see if i will be able to decrypt what the ase php class encrypted in MySQL and then vice versa.

However i noticed the iv was not concatenated in the returned encrypted it, is it deliberately because the ebc and cbc do not need iv during decryption?

The reason I adds iv in the encryption process it to have fresh ciphers even on a same text not really bcs of added security.

Will test run these code and then give you feedback

[pandglobal]

Master please this code is works, but i observe it does use iv when encoding and thus resulting to static cipher. my cipher mode by msql default is aes-128-ecb and i have changed that in the AES class you posted

1. Now i used your code and generated an encrypted text of My Secret Message,
2. Then i copied the encrypted text and pasted it on my database column
3. Then i query the column using the below sql and the decryption failed as it it returns empty value

$encryptionKey = '1234567890123456';
$iv = '';
$search = $olanzarmanagercon->prepare("SELECT AES_DECRYPT(FROM_BASE64(marginbalance), '$encryptionKey','$iv') AS marginbalancenew FROM subscriptions WHERE userid = '12'");
$search->execute();
$result = $search->fetch(PDO::FETCH_ASSOC);

print_r($result);

it printed the decrypted text perfectly, but the problem is IV is not being used and thus static cipher text is being produced.
In mysql i used vi and it produces fresh cipher text on each run even when the text and key are the same.
For instance if you run hundred times it will output same encrypted text without changes, which is what using iv makes it to change on every run

try {
    $aes = new AES();
    $encryptedMessage = $aes->encode(900);

    var_dump($encryptedMessage); // '4jZf0a8oV3Xa5e0TyI7EcLAI3FGstD9Hn6teGkzjFIQ=
    var_dump($aes->decode($encryptedMessage)); // My Secret Message
} catch (\Exception $e) {
    echo 'Error: ' . $e->getMessage();
}

Please can you make it use vi during encryption Sir

[pandglobal]

I have modified your code to get it to use VI and everything working fine just that the VI is more like a fancy as the AES_128_EBC does not actually need it.
The cipher changes are mere base64_encode() that produces new text due to the presence of the random text that was added to it not necessarily because the encryption it self produced new text. It looks less secured compared to standard AES-128-CTR or AES_256_CBC.

Thanks so much for your guide and timely response am most grateful.