Encrypting specific columns in Hibernate: What to do with existing data and how to correctly implement @ColumnTransformer?

Question

I have built a web application with a MySQL database that holds patients data. According to GDPR patients names must be encrypted inside the database. For connecting and performing operations to db, I use Hibernate 5.

Searching the web, I have found a lot of info regarding how to encrypt a specific column or columns inside a db table. Mainly the following three approaches:

Using @ColumnTransformer annotation of Hibernate which is the least destructive to existing code and requires the least code to be written
Using Jasypt and its Hibernate integration which is more destructive to existing code and requires a few lines of code.
Implementing a JPA Attribute Converter which required quite a few lines to be written

I decided to use @ColumnTransformer which seems to be the easiest implementation. If you think that one of the other approaches is better please say it and also explain the reason.

My question, however, has to do with existing data. My db already has data that is unencrypted which must be encrypted to work with @ColumnTransformer implementation. I intend to use the following annotation:

@ColumnTransformer(
    read = "pgp_sym_decrypt(lastName, 'mySecretKey')", 
    write = "pgp_sym_encrypt(?, 'mySecretKey')"
)

and

@ColumnTransformer(
    read = "pgp_sym_decrypt(firstName, 'mySecretKey')", 
    write = "pgp_sym_encrypt(?, 'mySecretKey')"
)

to the corresponding columns.

How should I encrypt existing data to comply with the above annotations? What SQL code should I use?

It is a bit out of the scope but hard-coding the "secret key" in a source code is a very bad idea - it is not a secret any more. — Michal Wilkowski
– Michal Wilkowski, Commented Dec 14, 2020 at 22:02
Well, yes, so we do not see that (I mean it is not published on stackoverflow). But anyone who has access to your source code, has access to your data. And you cannot change your key and re-encrypt your data whenever necessary without re-building the software. Basically, these are rather bad security practices and I doubt any respectful IT security team would approve it. But again, I respect that this is potentially out of scope of the discussion, I just wanted to make sure that others reading it are aware of it, too. — Michal Wilkowski
– Michal Wilkowski, Commented Dec 16, 2020 at 19:33

mixtou · Accepted Answer · 2024-03-29 10:56:04Z

MySQL supports the following functions:

AES_ENCRYPT(str, key_str);
AES_DECRYPT(crypt_str,key_str);

However, I can't update all MySQL entries using the following (because aes_encrypt returns binary):

UPDATE Patient SET firstName=AES_ENCRYPT(firstName, "mySecretKey"), lastName=AES_ENCRYPT(lastName, "mySecretKey") //NOT WORKING

The solution is:

Rename existing columns using MySQLcommand:

ALTER TABLE Patient CHANGE firstName firstName-old;

ALTER TABLE Patient CHANGE lastName lastName-old;

Create two new MySQL columns of type varbinary(512) with command:

ALTER TABLE Patient ADD COLUMN lastName VARBINARY(512) NOT NULL;

ALTER TABLE Patient ADD COLUMN firstName VARBINARY(512) NOT NULL;

Update the new columns from the old ones with the following command:

UPDATE `gourvas_platform`.`Patient` SET firstName=aes_encrypt(`firstName-old`, "my secret"), lastName=aes_encrypt(`lastName-old`, "mysecret");

Now we can safely delete the old columns

Finally use the following Hibernate @ColumnTransformer annotations:

@ColumnTransformer(
    read = "AES_DECRYPT(lastName, 'mySecretKey')", 
    write = "AES_ENCRYPT(?, 'mySecretKey')"
)

and

@ColumnTransformer(
    read = "AES_DECRYPT(firstName, 'mySecretKey')", 
    write = "AES_ENCRYPT(?, 'mySecretKey')"
)

Note: If you are using MySql 8 and beyond skip this step. Because I'm using MySQL 5.7 and AES_DECRYPT function returns binary[] instead of String, I need to cast to text. So the above @ColumnTransformer needs to be changed to the following:

@ColumnTransformer(
read = "cast(aes_decrypt(lastName, 'my secret') as char(255))", 
write = "aes_encrypt(?, 'mysecret')"
)

and

@ColumnTransformer(
read = "cast(aes_decrypt(firstName, 'myscret') as char(255))", 
write = "aes_encrypt(?, 'mysecret')"
)

To make collation work you need to cast to char even on never versions of MySQL / MariaDB

Collectives™ on Stack Overflow

Encrypting specific columns in Hibernate: What to do with existing data and how to correctly implement @ColumnTransformer?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related