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


Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.


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.

DatabaseTable

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.

Related Articles

Responses

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. Is there a simple way to query the table with the encrypted column in the where clause?

    1. Hi Juzer,

      You need to use a native SQL query for that.
      If you’re referencing the encrypted column in a JPQL or Criteria Query, Hibernate will apply the defined mapping and by doing that, call the function to encrypt the value.

      Regards,
      Thorben

  2. Hi Sir

    In my case , i stored password in md5 encrypt format,so i want to validate user password to it,how do i do it , i am using hql query (ex..select u from User u where u.emailid=:em and u.password=:pass).
    please can u help me.How to validate user password to md5 encrypt string .

    1. Hi Angad,

      You can’t use this approach for your use case because there is no reverse operation that gets the original String from the md5 hash.
      You can store the md5 hash as a simple String attribute. When you validate a user login, you need to calculate the md5 hash of the entered password and compare it with the value stored in the database. But please be aware, that the md5 algorithm has several vulnerabilities.

      Regards,
      Thorben

  3. Thank You for this Blog

  4. Great article Thorben, learned something new today. Thanks

  5. Pretty good article!

    I didn’t know this annotation, that’s very useful. Once I needed something like that I used internal callbacks (@PrePersist and @PostLoad) to handle (en)decryption. That worked like a charm!

  6. In JPA it is possible to implement it with entitylisteners, we’ve taken this approach and it worked well. However column transformation is more elegant. Also we avoided having the logic in db, to protect data from DBAs.

    1. Good point. It’s not a nice solution but it at least works.

  7. Nice Tip!

    I think it would be better to refer to stored functions in the @ColumnTransformer annotation that encapsulates the secret key and calls to pqcrypto functions in the database instead of having it in the code.

    Cheers,
    Thomas

    1. Agreed to your comment Thomas. That safer 100% than raw mapping in JPA

    2. Hi Thomas,

      thanks for your comment.

      Using a function to encapsulate the key and encryption is also an option.
      But I’m not sure that it’s a safer approach. You have to store the key somewhere. If you do that in a database function then everyone who has access to the encrypted data also has access to the secret key. I prefer to store key and data separately. That, of course, doesn’t really solve the issue but it’s at least not at the same place as the encrypted data.

      Regards,
      Thorben