Featured Image with Sidebar

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

By Thorben Janssen

Mapping

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.

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.

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.

About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Reply

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

  1. 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

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

  3. 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.

  4. 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. 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

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}