How to map encrypted database columns with Hibernate’s @ColumnTransformer annotation

A few days ago, someone asked me how to map an encrypted database column to an entity attribute. I remembered that I used a @ColumnTransformer for a similar use case, a few years ago. Vlad Mihalcea, Hibernate’s developer advocate, confirmed in a short chat that the team hadn’t done any major changes in that area and that the @ColumnTransformer is still the best solution for it. While answering the question in an email, I thought that would also be an interesting topic for a blog post and not just a short Hibernate Tip which I normally write about these kinds of questions.  So here it is.

You don’t want to store
sensitive information
as plain Strings
in your database.

Before we dive into the entity mappings, let me give you a quick introduction to the topic. The general idea is pretty obvious. You don’t want to store sensitive information, like passwords or credit card numbers, as plain Strings in your database. Most databases, therefore, support the encryption of certain columns.

PostgreSQL, for example, provides the pgcrypto module which adds support for several encryption algorithms and database functions for encryption and decryption. In this post, I use the module and its pgp_sym_encrypt and pgp_sym_decrypt functions to symmetrically en- and decrypt the credit card number in a customer table. These functions are easy to use as you can see in the following code snippet. You just have to provide the value you want to en- or decrypt and your password.

The following screenshot shows an example record of that table. As you can see, PostgreSQL stores the encrypted credit card number as a byte array.

JPA doesn’t provide a good solution

Unfortunately, the JPA standard doesn’t offer any real options to map the encrypted columns. The only thing you can do is not using the features of your database and do the en- and decryption within your persistence code. This provides a fancy example for an AttributeConverter, but it’s only a poor solution compared to the advanced encryption features of most databases.

Hibernate’s @ColumnTransformer for the rescue

It gets a lot better when you can use proprietary Hibernate features like the @ColumnTransformer. This annotation allows you to provide an SQL expression which Hibernate will use when reading from or writing to the database column. It is similar to the @Formula annotation I explained in a recent Hibernate Tip. The main difference is that it allows read and write access to the database column. If you’re working with encrypted database columns, you can use this annotation to define the calls of the pgp_sym_encrypt and pgp_sym_decrypt functions.

As you can see, I modeled the creditCardNumber attribute as a String and annotated it with a @ColumnTransformer annotation which defines the two function calls. The annotation supports 3 optional attributes:

• The forColumn attribute, which I didn’t use in the example, defines for which column the expressions shall be used. You need this when your entity attribute gets mapped to multiple database columns. In that case, you might need to provide multiple @ColumnTransformer annotations with a @ColumnsTransformers annotation or, if you’re using Hibernate 5.2, as a repeatable annotation.
• The read attribute defines the SQL expression that Hibernate applies when reading the database column. As you can see in the example, I use this attribute to specify the call of the pgp_sym_decrypt method and provide the column name and the password as parameters.
• The write attribute defines the SQL expression that Hibernate uses when writing to the database column. Hibernate expects exactly one ‘?’ placeholder within this expression which gets replaced by the value of the entity attribute. This is the same kind of placeholder as you know from your native queries. I use it in this example to define the call of the pgp_sym_encrypt method.

With this annotation in place, you can use the Customer entity and its creditCardNumber attribute as any other entity or attribute. Hibernate will apply the SQL expressions for every read and write operation in a transparent way. You can see examples for it in the following code snippets and log messages.

As you’ve seen, the definition of the function calls is pretty simple and it doesn’t have any effect on your code.

But in this example, it also has a small drawback. PostgreSQL wants to use a column of type bytea to store the encrypted data. I modeled the entity attribute as a String. That’s a good fit for the domain model but it doesn’t fit the database column data type.

Because of the provided SQL expressions in the @ColumnTransformer annotation, this becomes only an issue, if you try to generate the database tables based on the entity metadata. You, therefore, need to use an SQL script to generate the database tables which is the better approach, anyways.

Summary

Encrypted columns are a common approach for storing sensitive information in a database. Most databases, therefore, provide easy to use functions to apply strong encryption algorithms. Unfortunately, there is no good way to use them with plain JPA, and you have to rely on vendor specific features, like Hibernate’s @ColumnTransformer annotation.

As you’ve seen in this post, this annotation allows you to provide custom SQL expressions which Hibernate will transparently apply when reading from or writing to the database column. This allows you to define the encryption and decryption of the sensitive information in your entity mapping without adapting your business code.