A Spring Boot based project that showcases the options to do columnar encryption of PII ( Personally Identifiable Information ) storage and look up.
- Encrypt the PII fields in an entity automatically on db save and retrieve it as decrypted when reading from db.
- Search the field without doing any manual encryption.
- Spring boot ( 1.5.21 ) or later
- JPA
- Create a database
pii-encrypt-demo
- Update the host, username and password for db in the
application.yml
. - Run the application
- Use the Postman collection to save and get data
- Define an
AttributeConverter
(PIIAttributeConverter
) that will convert the string to encrypted value during save to database and decrypt when reading from database. - Annotate the fields to be encrypted using
@Convert(converter = PIIAttributeConverter.class)
- The findByQueries and JQL queries will automatically apply the converter
- The attribute converter is auto applied for all the annotated field during saving and reading back.
- This is even applicable when we do look up and will convert the passed value to encrypted version and compare.
- Like keyword lookup won't work
- JQL queries (
@Query
annotations) in repository also works without any additional code.
A Spring component that does the encryption and decryption of the attributes that are annoted.
The entity class holding PII data with PII fields annotated
Standard repository class with necessary repo methods for retireval
-- Encrypt existing field
update CUSTOMERS set name=cast(to_base64(aes_encrypt(name,"secret-key-12345")) as char) where id = 10;
-- Decrypt and select
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;
Make sure to take care of the below items before you implement this
- Ensure that the secret key is not stored in the source code and instead take from a vault of secret storage config property.