I want to decrypt few table data of my connected MySQL database but I can’t do this operation. I just want to know is it possible or if Metabase provides this kind of feature to decrypt encrypted data of table row and display it in a report?
Ex - I have a user table with the column first_name_encypt and inserting an encrypted string of the value of name field from the laravel application. But when generating a report on Metabase I want to show the encrypted string into the decrypt name value. When using the AES_ENCRYPT function on MySQL workbench its works but Metabase doesn’t seem to support it.
Any help would be appreciated.
Metabase does currently not support on-the-fly decryption of data in the GUI, but you can make such questions with Native/SQL query.
Thanks for your help.
But I want to tell you that I have used the AES_ENCRYPT and AES_DECRYPT function of SQL as a raw query on MYSQL Workbench and it clearly displays the encrypted data and then decrypt the value of that.
But when the same query run on Metabase it didn’t work, displays a random string instead of the decrypt string value.
@sushovand Are you sure it’s not returning a binary value, which Workbench perhaps is auto-converting (because of some setting) to a human readable string (
@flamber Yes its not returning the human readable string
@sushovand Correct, you need to manually convert the results with
CAST( ... AS CHAR)
CAST to char is working.
is there any other simple process except AES_ENCRYPT algorithm to encrypt string value?
Because for AES function the table field should have type BLOB and takes minimum 16 bytes space for each string value.
@sushovand I would recommend that you try looking on stackoverflow.com, since it’s really not related to Metabase.