Automatically apply field conversion function in Hibernate

Question

I have a database table with a field that I need to read from and write to via Hibernate. It is string field, but the contents are encrypted. And for various reasons (e.g. a need to sort the plain text values), the encrypt/decrypt functions are implemented inside the database, not in Java.

The problem I'm struggling with now is finding a way to invoke the encrypt/decrypt functions in Hibernate-generated SQL everywhere that the field is referenced and in a way that's transparent to my application code. Is this possible? I've looked into Hibernate's support for "derived" properties, but unfortunately, that approach doesn't support read-write fields. Any ideas appreciated.

Answer 1

Assuming you have access to the encrypt/decrypt algorithm from within Java, I would set up my mapped class something like

public class encryptedTable {
    @Column(name="encrypted_field")
    private String encryptedValue;

    @Transient
    private String value;

    public String getEncryptedValue() {
        return encryptedValue;
    }

    public String getValue() {
        return value;
    }

    public void setEncryptedValue(String encryptedValue) {
        this.encryptedValue = encryptedValue;
        this.value = decrypt(encryptedValue);
    }

    public void setValue(String value) {
        this.value = value;
        this.encryptedValue = encrypt(value);
    }
}

And then use get/set Value as the accessor within your program and leave the get/set EncryptedValue for Hibernates use when accessing the database.

Answer 2

You could have a trigger internal to the database that, on retrieval, decrypts the value and replaces the returned result and on insert encrypts the value and replaces the stored result with the encrypted value. You could also do this with a view wrapper - i.e. have an insert trigger on the view, and have the view automatically decrypt the value.

To better explain: have a view that decrypts the value, and an on insert trigger that encrypts the value that is linked to the view.

Answer 3

I don't think there's a way to make encryption like you've described it completely transparent to your application. The closest thing you can get is to make it transparent outside of entity. In your entity class:

@Entity
@SQLInsert(sql="INSERT INTO my_table(my_column, id) VALUES(encrypt(?),?)")
@SQLUpdate( sql="UPDATE my_table SET my_column = encrypt(?) WHERE id = ?")
public class MyEntity {

  private String myValue;

  ....

  @Formula("decrypt(my_column)")
  public String getValue() {
    return myValue;
  }

  public void setValue(String value) {
    myValue = value;
  }

  @Column (name="my_column")
  private String getValueCopy() {
    return myValue;
  }

  private void setValueCopy(String value) {
  }

}

value is mapped as derived property, you should be able to use it in queries.
valueCopy is private and is used to get around derived property being read-only.
SQLInsert and SQLUpdate is black voodoo magic to force encryption on insert / update. Note that parameter order IS important, you need to find out what order Hibernate would generate parameters in without using custom insert / update and then replicate it.

Answer 4

Why not just use the SQl server encryption that seems to already be in place by calling a stored proc in Hibernate instead of letting Hibernate generate a query?

Answer 5

Actually, in the end, I went a different route and submitted a patch to Hibernate. It was committed to trunk last week and so I think it will be in the next release following 3.5. Now, in property mappings, you can specify SQL "read" and "write" expressions to call SQL functions or perform some other kind of database-side conversion.