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.
. You can see a similar issue with the examples