Encrypt PII ( sensitive ) data in the database using Spring Boot AttributeConverter with direct search capabilities
Security and data privacy is paramount for any modern cloud application handling a customer’s PII ( Personally Identifiable Information ) data such as name, email, mobile, social security, etc. With the increased number of hacks and data leak incidents, it has reached a level where measures to prevent a hack are insufficient. We need to be in a mindset that even if the data is leaked, the customer identity information is not compromised.
Many organizations already follow the practice of columnar encryption where the critical customer information fields in a database are encrypted and stored. This is only decrypted at the application level when required for sending notifications or any other contact information displayed for authorized personnel.
In this blog post, we are going to see how to implement encryption for PII fields in a simple way using Spring boot AttributeConverters. This method is so simple and transparent in Spring if you are using JPA that you can continue with the existing database repository methods without any changes.
Objective
- Enable encryption of certain fields in a datastore.
- The data will be encrypted when persisting in the database and will be decrypted when it’s being read from the DB into the entity object.
- The repository methods should work seamlessly without requiring the service layer to explicitly encrypt and pass for matching or lookup.
Setup & Configuration
We will be achieving the above objectives in a Spring boot project using the AttributeConverters. You can see the complete sample project in the Github repository.
Project setup and dependencies
We can get this setup done on any standard spring boot application that is using spring-data-JPA dependency. We can head to https://start.spring.io and create a new project with the below dependencies.
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>javax.xml.bind</groupId> <artifactId>jaxb-api</artifactId> <version>2.3.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.37</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
I am using MySQL as the datastore. You can choose your favorite database as this is independent of the database and is supported by JPA.
Define the AttributeConverter
An AttributeConverter
is a specific interface that provides the capability to transform an attribute ( field ) before persisting and also while retrieving from the database. We will be defining an AttributeConverter that will encrypt the data before persisting to DB and also the logic to decrypt the field when retrieving.
Once defined, an AttributeConverter
can be mapped into an entity field using @Converter
annotation and Spring will invoke the AttributeConverter
whenever the field is being persisted or retrieved to or from DB respectively.
Let’s define our AttributeConverter, the key ingredient of this entire setup.
package com.microideation.samples.piiencryptdemo.support; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.core.env.Environment; import org.springframework.stereotype.Component; import javax.crypto.BadPaddingException; import javax.crypto.Cipher; import javax.crypto.IllegalBlockSizeException; import javax.crypto.NoSuchPaddingException; import javax.crypto.spec.SecretKeySpec; import javax.persistence.AttributeConverter; import java.security.InvalidKeyException; import java.security.Key; import java.security.NoSuchAlgorithmException; import java.util.Base64; @Component public class PIIAttributeConverter implements AttributeConverter<String,String> { private static final String AES = "AES"; private static final String SECRET = "secret-key-12345"; private final Key key; private static Environment environment; // As the AttributeConverter is called by the JPA per field, direct autowire does not work // We need to use the static field and set // Reference: https://stackoverflow.com/a/36856434 @Autowired public void setEnvironment(Environment environment) { PIIAttributeConverter.environment=environment; } public PIIAttributeConverter() { // IMPORTANT NOTE: Preferably get the secret from the configuration // environment.getProperty() etc that loads from Vault or some Secret storage key = new SecretKeySpec(SECRET.getBytes(), AES); } @Override public String convertToDatabaseColumn(String attribute) { try { Cipher cipher = Cipher.getInstance(AES); cipher.init(Cipher.ENCRYPT_MODE, key); return Base64.getEncoder().encodeToString(cipher.doFinal(attribute.getBytes())); } catch (InvalidKeyException | BadPaddingException | IllegalBlockSizeException | NoSuchPaddingException | NoSuchAlgorithmException e) { throw new IllegalStateException(e); // You can decide to return an empty or null value on error to be stored if don't want to throw exception } } @Override public String convertToEntityAttribute(String dbData) { try { Cipher cipher = Cipher.getInstance(AES); cipher.init(Cipher.DECRYPT_MODE, key); return new String(cipher.doFinal(Base64.getDecoder().decode(dbData))); } catch (InvalidKeyException | BadPaddingException | IllegalBlockSizeException | NoSuchPaddingException | NoSuchAlgorithmException e) { throw new IllegalStateException(e); // You can decide to return an empty or null value on error to be returned if don't want to throw exception } } }
We have done the following here
- Created a
PIIAttributeConverter
implementing theAttributeConverter<String,String>
- Implemented the convertToDatabaseColumn method that will do the encryption of the field passed and return the encrypted value back.
- Implemented the convertToEntityAttribute method that will do the decryption of field read from the DB and return the decrypted value back.
- Uses the AES algorithm with a static defined key for encryption and decryption.
We should never store the key in the code. This should preferably come from a property loaded into the environment sourced from a secret storage like HashiCorp vault.
Entity class
The Entity class is defined as a normal Entity and the only additional code is the @Converter
annotation applied to the fields that we need to encrypt ( or apply the PIIAttributeConverter
).
package com.microideation.samples.piiencryptdemo.domain; import com.microideation.samples.piiencryptdemo.support.PIIAttributeConverter; import lombok.Data; import javax.persistence.*; import java.io.Serializable; @Data @Entity @Table(name = "CUSTOMERS") public class Customer implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Convert(converter = PIIAttributeConverter.class) private String mobile; @Convert(converter = PIIAttributeConverter.class) private String name; }
We can see above that for the mobile and the name fields, we have added the annotation as @Convert(converter = PIIAttributeConverter.class)
. This will inform the JPA layer to apply PIIAttributeConverter
for these fields.
Repository class
The repository class is a standard repository interface that extends the JPARepository and you don’t have any changes here. You can use the standard findBy methods, @Query ( JPQL only as the native queries will not trigger the AttributeConverter by JPA.
package com.microideation.samples.piiencryptdemo.repository; import com.microideation.samples.piiencryptdemo.domain.Customer; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; public interface CustomerRepository extends JpaRepository<Customer,Long> { // Standard find by Query. Here the mobile number will be automatically encrypted and passed to match with encrypted value // in the db Customer findByMobile(String mobile); // Using JQL. // JQL automatically applies the attribute converters. // NOTE: Native query will not support this functionality and we are required to pass the encrypted value // from our side. @Query("select C from Customer C where C.mobile=?1") Customer findByMobileUsingJQL(String mobile); }
Note that when you are using findBy queries or searching using an encrypted field, you don’t need to pass encrypted values to the repository methods. The JPA will identify that there is an AttributeConverter
attached to the field and apply the same encryption to the value passed in the repo method or query. Since AES generates the same encrypted value, the matching will happen fine.
Service and Controller classes
There is not much to be done from the Service or the Controllers as the encryption handling part is already handled at the repo level by JPA.
Service
package com.microideation.samples.piiencryptdemo.service; import com.microideation.samples.piiencryptdemo.domain.Customer; import com.microideation.samples.piiencryptdemo.repository.CustomerRepository; import lombok.RequiredArgsConstructor; import org.springframework.stereotype.Service; @Service @RequiredArgsConstructor public class CustomerService { private final CustomerRepository customerRepository; public Customer saveCustomer(Customer customer) { return customerRepository.save(customer); } public Customer findByMobile(String mobile) { return customerRepository.findByMobile(mobile); } public Customer findByMobileJQL(String mobile) { return customerRepository.findByMobileUsingJQL(mobile); } }
The CustomerService
class contains only methods for saving and finding the Customer using encrypted fields. The calls are just proxied to the repository and we are not doing any business logic for the sake of simplicity.
Controller
package com.microideation.samples.piiencryptdemo.controller; import com.microideation.samples.piiencryptdemo.domain.Customer; import com.microideation.samples.piiencryptdemo.service.CustomerService; import lombok.RequiredArgsConstructor; import org.springframework.web.bind.annotation.*; @RestController @RequiredArgsConstructor public class CustomerController { private final CustomerService customerService; @PostMapping("/customer") public Customer saveCustomer(@RequestBody Customer customer) { return customerService.saveCustomer(customer); } @GetMapping("/customer/find/mobile/{mobile}") public Customer getByMobile(@PathVariable String mobile) { return customerService.findByMobile(mobile); } @GetMapping("/customer/find/mobile/jql/{mobile}") public Customer getByMobileJQL(@PathVariable String mobile) { return customerService.findByMobileJQL(mobile); } }
CustomerController
contains some endpoints to invoke the Service methods and test the functionality of saving a customer with the encrypted information and then searching and retrieving the information.
Testing the setup
Now it’s time to test the configuration and confirm that the details are getting stored as encrypted and are able to search details and retrieve unencrypted.
If you are using the sample Github project, you can import and use this postman collection for the APIs.
Running application
Please ensure that you make the necessary changes to the application.yml for connecting to the database. Start the application as you would do for a Spring boot and this should run it in port 8080. The base URL would http://localhost:8080/pii-encrypt-demo
Saving the customer
Let’s call the API to save the customer and ensure that the mobile and name fields are encrypted and persisted in the database.
We hit the /customer API and get the response with id as “5”. Now let’s check this in the DB.
You can see that the id 5 is having values encrypted for the mobile and name as expected.
Read the profile using mobile ( findBy )
Let’s call the API to find the customer by mobile. Note that here we pass the search string as plain text and JPA will do the encryption and matching internally.
And that’s working seamlessly
Read the profile using mobile ( JPQL query )
Now let’s try the search using a repository method that is using @Query
annotation.
That also worked flawlessly. So we can also see that the fields in the @Query annotations are also handled fine.
Encrypting / Decrypting in DB
If there are cases where you want to have the data encrypted or decrypted in the DB during select, that is also possible if you know the secret key. Usually, it’s recommended to have the key and decryption only managed by the application only. But for cases like encrypting an existing database field, we can use the built-in methods available in the respective DB engine.
For example, in the case of MySQL, I can issue the below commands for encrypting and decrypting using the key. Note that I am using the aes specific methods.
-- Encrypt an existing DB field update CUSTOMERS set name=cast(to_base64(aes_encrypt(name,"secret-key-12345")) as char) where id = 10; -- Decrypt and select the encrypted fields SELECT id,name,mobile, cast(aes_decrypt(from_base64(name),"secret-key-12345") as char) as 'Decrypted Name', cast(aes_decrypt(from_base64(mobile),"secret-key-12345") as char) as 'Decrypted Mobile' FROM `pii-encrypt-demo`.CUSTOMERS;
Pitfalls and limitations
So far we have seen that the encryption of specific data fields setup is simple and largely transparent to the developer. We can save them as encrypted fields and also match them again by passing plain text that will be encrypted by the JPA. There are certainly some pitfalls and limitations
- If you are trying to do a like keyword or a partial match, this will fail. Lookups can only be done with a direct match. This may not be an issue in most of the use cases, but if you require this capability, I would suggest using alternate methods.
- Ensure that the key for the encryption is not stored on the code. This need to be taken from secure secret storage that is only accessible to the application from the production environments.
Conclusion
With the increased security and data privacy awareness, it is highly recommended to safeguard the customer’s PII information using encryption so that even in the case of a breach, the DB won’t have the plain details. This is becoming a standard world over and is recommended and required by many enterprises.
Let me know if you have any queries regarding the setup.
Great article. Is there a way to disable encryption/decryption declaratively for each field via external property ?