ManyToOne reference with encrypted fields gives doesn't exist error using Jasypt

Question

I have a problem on the following situation:

In my Spring, Hibernate application I got a User Entity and a UserCategory Entity. The table of the user entity got a username as identifier. This indentifierfield can't be encrypted because this table is also used by an older program without the possibility to do this.

To make a ManyToOne reference from UserCategory to User I need a field in the UserCategory table with the unique username of a User. What I want to do is to encrypt the username in the UserCategory table using Jasypt. And of course this work:

    @Type(type="encryptedString") 
    @ManyToOne 
    @JoinColumn(name = "username", insertable=false, updatable=false)   
    @ForeignKey(name = "none") 
    public User getUser(){ 
            return this.user; 
    } 
    public void setUser(User user ){ 
            this.user = user; 
    } 

But after putting the username encrypted in the UserCategory table I can't use this record because Hibernate can't make a reference to the User on encrypted field: You will get the following error:

"No row with the given identifier exists: com.foo.bar.models.User#M9LgndiyCsVGqfVRVblb3A=="

This is a logical error, but do you know a good solution. In think the code need something to first decrypt and then try to make the reference. But I'm stuck on how to do this.

Answer 1

I think you misunderstood how to use a custom user type and, as I commented in my previous answer, you are NOT supposed to declare the custom type at the association level here, you are supposed to use it at the attribute level i.e. on the username attribute of the User entity.

This is actually explained in the documentation (pasted from Google Cache as the Jasypt site seems to be currently down):

Integrating Jasypt with Hibernate 3

Jasypt provides an integration package (org.jasypt.hibernate.type) which provides several Hibernate UserType implementations to allow one or several of the properties in a mapped Hibernate entity to be declared as being of an encrypted type. Types allowed to be stored encrypted include strings, binaries (byte arrays), numeric types, booleans and dates.

Persistence of those properties will be done in an encrypted manner, but in a completely transparent way for the application.

This can be useful for encrypting personal data, private messages, etc, so that it is avoided that anyone with read access to the "critical" tables can read all of its contents.

For encryption at Hibernate, jasypt uses its password-based encryption capabilities, and any encryptor object implementing PBEStringEncryptor, PBEByteEncryptor, PBEBigIntegerEncryptor or PBEBigDecimalEncryptor can be used to encrypt data, even encryptors created by the user.

But encryption sets a limitation on your Hibernate usage: security standards establish that two different encryption operations on the same data should not return the same value (due to the use of a random salt). Because of this, none of the fields that are set to be encrypted when persisted can be a part of a WHERE clause in your search queries for the entity they belong to.

So, to sum up, 1) the @Type annotation should be applied to the username and 2) you won't be able to use the username as primary key (since it can't be part of a join as mentioned in the last paragraph above).

This means that you'll need something like this (assuming you declared the appropriate @TypeDef):

@Entity
public class User {

    @Id @GeneratedValue
    private Long id;

    @Type(type="encryptedString") 
    private String username;

    ...
}

And modify the ManyToOne association accordingly.