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

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

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. A quick chat with the Hibernate team confirmed that they 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.

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.

insert into Customer (creditCardNumber, firstName, lastName, version, id) values (pgp_sym_encrypt(‘1234 5678 9012 3456’, ‘mySecretKey’), ‘Jane’, ‘Doe’, 0, 2);
SELECT pgp_sym_decrypt(creditCardNumber, ‘mySecretKey’) FROM customer;

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.

@Entity
public class Customer {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = “id”, updatable = false, nullable = false)
	private Long id;

	@Column
	private String firstName;

	@Column
	private String lastName;

	@Column
	@ColumnTransformer(read = “pgp_sym_decrypt(creditCardNumber, ‘mySecretKey’)”, write = “pgp_sym_encrypt(?, ‘mySecretKey’)”)
	private String creditCardNumber;

	@Version
	@Column(name = “version”)
	private int version;

	…
}

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.

Customer c = new Customer();
c.setFirstName(“John”);
c.setLastName(“Doe”);
c.setCreditCardNumber(“1234 5678 9012 3456”);
em.persist(c);

13:13:03,840 DEBUG SQL:92 – insert into Customer (creditCardNumber, firstName, lastName, version, id) values (pgp_sym_encrypt(?, ‘mySecretKey’), ?, ?, ?, ?)

Customer c2 = em.find(Customer.class, c.getId());

13:13:03,992 DEBUG SQL:92 – select customer0_.id as id1_0_0_, pgp_sym_decrypt(customer0_.creditCardNumber, ‘mySecretKey’) as creditCa2_0_0_, customer0_.firstName as firstNam3_0_0_, customer0_.lastName as lastName4_0_0_, customer0_.version as version5_0_0_ from Customer customer0_ where customer0_.id=?

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.