Search code examples
javajooqjooq-codegen

How to apply MySQL cryptography functions in Jooq custom Converter?

I'm trying to achieve below using Jooq library,

  • Encrypt and decrypt specified column using org.jooq.util.mysql.MySQLDSL methods
  • This must be a jooq global configuration, so that whenever a new query method return fetch/insert/update the record encryption/decryption should be automatically taken care.
  • Secret key for encryption/decryption will be value from the another column in the same table
  • I'm trying to avoid Spring framework here.

Currently I'm trying to do this using org.jooq.Converter and org.jooq.meta.jaxb.ForcedType But

  • Not able to access org.jooq.RecordContext in the CustomConverter class to get the secret key
  • Also I'm not able to make this generic configuration

Links I'm referring to:

Update: In short this is what I'm trying to achieve through Jooq:

CREATE TABLE TESTCRYPT(
Id mediumint AUTO_INCREMENT,
Secret int NOT NULL,
encrypteddata BLOB,
PRIMARY KEY(Id)
);

INSERT INTO TESTCRYPT(Secret,encrypteddata) VALUE ('1234',AES_ENCRYPT('my-data','1234'));

SELECT Secret ,AES_DECRYPT(encrypteddata,Secret) from TESTCRYPT t ;
Solution

  • An out of the box feature

    As of jOOQ 3.18, there isn't an out of the box feature for this sort of computation. I've created a feature request, as this isn't the first time someone was looking for it:

    Custom implementation on the server side

    If you must encrypt / decrypt on the server side using SQL expressions, perhaps you can get away with the new experimental query object model traversal and replacement features.

    The idea is that you:

    • Specify which columns or types are supposed to be encrypted (either in a configuration or using a custom data type)
    • Then transform all SQL queries to always
      • Replace Field<MyType> by Field<byte[]> using encryption in contexts where a bind variable is written
      • Replace generated SQL for all Field<MyType> to decryption in contexts where a projection is made
      • Leave other expressions untouched

    Custom implementation on the client side

    All of this seems much easier to achieve with a simple Converter, if you can encrypt / decrypt on the client instead of the server.