How to Encrypt and Decrypt column values use it in both nativeQueries and JPA?

Question

I need to encrypt values in a MySQL database table :

user

| userId |      email   | userAge | firstName| lastName  | userType |
|--------|--------------|---------|-----------|----------|--------- |
|    1   |[email protected]| 20      | John      | Smith    | 1        |

My user class as following

 @Entity
 @Table(name = "user")
 public class User{

 @Id @GeneratedValue
 @Column(name = "userId ")
 private int userId;
 
 @Column(name = "firstName")
 private String firstName;
 
 @Column(name = "email")
 private String email;
 
 @Column(name = "lastName")
 private String lastName;

 @Column(name = "userType ")
 private String userType ;

 // getters and setters
 }

I tried to do this by using AttributeConverter

@Component
public class ValueAttributeConverter implements AttributeConverter<String, String> {

private static final String AES = "AES";
private static final String SECRET = "secretkey";

private final Key key;
private final Cipher cipher;

public AttributeEncryptor() throws Exception {
    key = new SecretKeySpec(SECRET.getBytes(), AES);
    cipher = Cipher.getInstance(AES);
}

@Override
public String convertToDatabaseColumn(String value) {
    try {
        cipher.init(Cipher.ENCRYPT_MODE, key);
                                                                                
   return Base64.getEncoder().encodeToString(cipher.doFinal(value.getBytes()));
    } catch (IllegalBlockSizeException | BadPaddingException | InvalidKeyException e) 
    {
        throw new IllegalStateException(e);
      }
     }

@Override
public String convertToEntityAttribute(String value) {
    try {
        cipher.init(Cipher.DECRYPT_MODE, key);
        return new String(cipher.doFinal(Base64.getDecoder().decode(value)));
    } catch (InvalidKeyException | BadPaddingException | IllegalBlockSizeException e{
        throw new IllegalStateException(e);
      }
      }     
      }

And added @Convert annotation to the attribute

@Column
@Convert(converter = ValueAttributeConverter.class)
private String email;

This will encrypt the column values.

But the problem is I have native queries that I use in my UserRepository

 @Query(nativeQuery = true , value = "select * from users where email=?1 
  and 
 firstName =?2")

In this case, it is not working since the email value is encrypted. If I use other columns rather than email such as lastName. It is working and returns the decrypted value.

I tried to encrypt the string before suing it in the query

 email = valueAttributeConverter.convertToEntityAttribute(email);

 

It works but I don't know if this approach is good.

I also tried using @ColumnTransformer

  @Column
  @ColumnTransformer(
      read = "cast(aes_decrypt(email, 'secretkey') as char(255))", 
      write = "aes_encrypt(?, 'secretkey')"
       ) 
     private String email;

This is also working fine when encrypting but won't work when I need to decrypt. Even if I select using another column

 @Query(nativeQuery = true , value = "select * from users where userId 
   =?1")

It doesn't decrypt the value, it returns the encrypted value.

My question is are there any better approaches that I could take to resolve this encrypt and decrypt issue and also use it in both JPA and nativeQuery ?

Answer 1

I think you should rethink using native queries in your code. You could take a look into using the Data Access Object pattern: https://www.baeldung.com/java-dao-pattern

Using native queries should be a last resort and used only in cases where you absolutely need some platform specific feature.

Architecturally you could keep your User entity as it is, but substitute the native query for a JPQL query in your Data Access Object, for example your native query example

@Query(nativeQuery = true , value = "select * from users where userId =?1")

would be transformed to a DAO method:

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

public class UserDao implements Dao<User> {
    
    private EntityManager entityManager;
    
    // standard constructors
    
    @Override
    public List<User> findByEmail(String plaintextEmail) {
        String encryptedEmail = myEncryptionMethod(plaintextEmail);
        
        TypedQuery<User> query = entityManager.createQuery("SELECT u FROM User u WHERE u.email = :email", User.class);
        return query.setParameter("email", encryptedEmail).getResultList();
    }
    
    // ... other DAO methods like getById, update, delete, ...
    
}

The DAO pattern allows you to create arbitrarily complex queries with business logic (like in your case) and gives you more control. JPA should handle the encrypted values automatically in the returned User object if you use the AttributeConverter from your example.

The nice thing about JPQL is that it is not native SQL and it is interpreted by JPA before generating platform specific queries automatically. Your code would work on any DB backend supported by JPA. Also, you get security benefits like SQL injection protection for free.