[FIXED] JPA Converter with contains query

Issue

I'm having some trouble with a particular query in my Spring Boot application. I'm using Spring's built-in text encryption in order to encrypt text in my database

I have an entity called BookRecord

@Entity
@Table(name = "books")
@EntityListeners(AuditingEntityListener.class)
public class BookRecord extends Auditable {

@EmbeddedId
private BookIdentifier identifier;

@Column(name = "title")
@Convert(converter = TitleEncryptionConverter.class)
private String title;

And a title encryption converter which looks like this

public class TitleEncryptionConverter implements AttributeConverter<String, String> {
    private TextEncryptor textEncryptor;

    public TitleEncryptionConverter (TextEncryptor textEncryptor) {
        this.textEncryptor = textEncryptor;
    }

    @Override
    public String convertToDatabaseColumn(String attribute) {
        return textEncryptor.encrypt(attribute);
    }

    @Override
    public String convertToEntityAttribute(String dbData) {
        return textEncryptor.decrypt(dbData);
       }
    }

I'm trying to search the database using a "contains" on the title, however because the title is encrypted I'm having trouble getting the proper results. Here is my query:

Page<BookRecord> findByIdentifier_UserIdAndTitleContains(String uid, String searchTerm,  Pageable pageable);

I'm trying to write an endpoint which will query the database and look for titles which contain certain characters. Because the titles are encrypted in the database, the query is returning no matches (it's searching the db with the encrypted "searchTerm" value.

How should I handle this? I think my options are:

1. Figure out how to not encrypt the searchTerm before searching in the database - and try to decrypt the db results before running the contains
2. Search all items in the db just by user_id, and then manually filter out the titles by the searchTerm (since the BookRecord will already have the decrypted values, this should work. Downside of this is paging becomes an issue

Any suggestions how I can solve this?

Solution

This is a tough topic. Since you can't really make use of indexes due to the fact that the data is encrypted there is not much you can do, other than load the data and filter somehow. If you can implement the decryption on the database, you could at least avoid transferring unnecessary data, but the performance will still be bad if the cardinality per user_id is very high.

Answered By - Christian Beikov
Answer Checked By - Pedro (JavaFixing Volunteer)