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?

Answer 1

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:

1. Rename existing columns using MySQLcommand:

   ALTER TABLE Patient CHANGE firstName firstName-old;
   
   ALTER TABLE Patient CHANGE lastName lastName-old;
   

2. 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;
   

3. 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");
   

4. Now we can safely delete the old columns

5. 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: 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')"
)