MySQL | Reading encrypted data (Data at Rest ) using AS_DECRYPT() function

I am implementing a solution using MySQL as the backend database. A table in the database has a column where data is encrypted (data at rest). I have tested inserting a new row with encrypted data and reading it back using phpMyAdmin. All is good. Now I want to generate an app using this database.

List option of the app should decrypt the column before displaying the data
Add new data option of the app should encrypt the data

secret_word = encrypted column
shopping = table name

There will be another one or two columns with encrypted data. For the sake of this discussion we will focus on the ‘secret_word’ column.

Reading data is accomplished using this SQL statement. The encrypted column is shown as ascii characters and the decrypted data is shown in a new column.

SELECT *, AES_DECRYPT(secret_word, 'my secret key') AS Secret FROM 'shopping';
NOTE. The decrypted data is listed under a new column with the name ‘Secret’.

Inserting a new row is done using this SQL statement.

INSERT INTO 'shopping' ……..
(AES_ENCRYPT('bronze', 'my secret key'))

The above two SQL statements are executed using myPhpAdmin.

Now, I am testing a new retool app generated from the ‘shopping’ database.

My querry1 in the retool is this.
SELECT *, AES_DECRYPT(secret_word, 'my secret key') AS Secret FROM 'shopping';

The retool table has secret_word and Secret among other columns.

Data in column secret_word as expected:

{"type": "Buffer", "data": [ 202, 167, 114, 9, 186, 122, 125, 164, 142, 67, 244, 28, 246, 227, 240, 111 ] }

Data in column Secret:
{ "type": "Buffer", "data": [ 76, 79, 76 ] }

My expectation was to see the decrypted data values under the column Secret.

Is my approach correct and if not what are the correct steps I should follow ?

Thank you.

Hi @ceylon Apologies for the delay! I believe this not currently supported and therefore the expected response :disappointed:. You can see a similar issue with the examples here.

I've linked this post to a ticket where we're tracking requests for support here & I'll let you know if I hear anything