• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar

Thoughts on Java

  • Tutorials ▼
    • Hibernate & JPA Tutorials
    • Hibernate Tips
    • Tutorials on YouTube
  • Books & Training ▼
    • Hibernate Tips Book
    • Online Training
    • On-Site Training
  • Consulting ▼
    • Consulting Call
    • Project Coaching
  • About ▼
    • About Thoughts on Java
    • Support Thoughts on Java
    • Resources
  • Member Library
You are here: Home / Hibernate / Hibernate Advanced / How to map encrypted database columns with Hibernate’s @ColumnTransformer annotation
Special thanks to all Thoughts on Java Supporters for supporting this article!

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

By Thorben Janssen 10 Comments

  • tweet 
  • share 
  • share 
  • share 
  • share 
  • share 
  • e-mail 

Advanced Mappings_ How to map encrypted database columns with Hibernate

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  


Already a member? Login here.

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.


Already a member? Login here.

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.

  • tweet 
  • share 
  • share 
  • share 
  • share 
  • share 
  • e-mail 

Related posts:

  1. Mapping Definitions in JPA and Hibernate – Annotations, XML or both?
  2. Ordering vs Sorting with Hibernate – What should you use?
  3. Composition vs. Inheritance with JPA and Hibernate
  4. Ultimate Guide to Implementing equals() and hashCode() with Hibernate
Become a Thoughts on Java Supporter to claim your member perks and to help me write more articles like this.

Filed Under: Hibernate Advanced Tagged With: Mapping

Implement Your Persistence Layer with Ease

Learn More About Hibernate

Need Some Help with Your Project?

Reader Interactions

Comments

  1. Thomas Darimont says

    September 7, 2016 at 7:18 am

    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

    Reply
    • Alexey says

      September 7, 2016 at 7:45 am

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

      Reply
    • Thorben Janssen says

      September 7, 2016 at 9:15 am

      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

      Reply
  2. Christos Melas says

    September 7, 2016 at 8:51 am

    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.

    Reply
    • Thorben Janssen says

      September 7, 2016 at 9:11 am

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

      Reply
  3. Rafael Ponte says

    September 7, 2016 at 1:40 pm

    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!

    Reply
  4. Javin says

    September 7, 2016 at 2:26 pm

    Great article Thorben, learned something new today. Thanks

    Reply
  5. Tarek says

    September 9, 2016 at 1:02 pm

    Thank You for this Blog

    Reply
  6. Angad says

    December 27, 2017 at 8:49 am

    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 .

    Reply
    • Thorben Janssen says

      December 29, 2017 at 4:50 pm

      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

      Reply

Leave a Reply Cancel 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.

Primary Sidebar

Join over 10.000 developers
in the
Thoughts on Java Library

Ebooks Sidebar Get free access to ebooks, cheat sheets and training videos.
Join Now!
Don't like ads?
Become a Thoughts on Java Supporter.
Special Launch Price

Let’s Connect


Thorben Janssen
Independent consultant, trainer and author
  • Facebook
  • GitHub
  • Google+
  • LinkedIn
  • Twitter
  • xing
  • YouTube

Speaking at

7th February 2019
JUG Ostfalen (Germany):
Hibernate Tips 'n' Tricks: Typische Probleme schnell gelöst (Talk - German)

19th March 2019
JavaLand 2019 (Germany):
Hibernate Tips 'n' Tricks: Typische Probleme schnell gelöst (Talk - German)

21st March 2019
JavaLand 2019 (Germany):
Hibernate + jOOQ + Flyway = Die besten Frameworks in einem Stack (1-day Workshop - German)

6th-10th May 2019
JAX 2019 (Germany):
Hibernate Workshop: Komplexe Lösungen jenseits von CRUD (2-day Workshop - German)

6th-10th May 2019
JAX 2019 (Germany):
Spring Data JDBC vs. Spring Data JPA – Wer macht das Rennen? (Talk - German)

6th-10th May 2019
JAX 2019 (Germany):
Microservices mit Hibernate – typische Probleme und Lösungen (Talk - German)

Looking for an on-site training?

Featured Post

14 YouTube Channels You Should Follow in 2019

Getting Started With Hibernate

Entities or DTOs – When should you use which projection?

Ultimate Guide – Association Mappings with JPA and Hibernate

Ultimate Guide to JPQL Queries with JPA and Hibernate

Recent Posts

  • Hibernate Tips: How to Customize a Constructor Expression for Different Subclasses
  • Hibernate Tips: How to Map java.time.Year with JPA and Hibernate
  • Why, When and How to Use DTO Projections with JPA and Hibernate
  • Hibernate Tip: Map a bidirectional one-to-one association with shared composite primary key
  • Implementing the Repository pattern with JPA and Hibernate
  • Hibernate Tips: How to Handle NULL Values while Ordering Query Results in a CriteriaQuery
  • 6 Hibernate Best Practices for Readable and Maintainable Code
  • Can you use Hibernate/EclipseLink/JPA for your microservice?
  • 14 YouTube Channels You Should Follow in 2019
  • Hibernate Tips: How To Map an Entity to a Query
Don't like ads?
Become a Thoughts on Java Supporter.

Copyright © 2019 Thoughts on Java

  • Impressum
  • Disclaimer
  • Privacy Policy