1

I'm currently trying to store encrypted data in some of the columns of a Postgres DB. After receiving helpful feedback from this question: client-side-encryption-with-java-and-postgres-database I am using converters/bindings to implement transparent encryption in the JDBC layer.

Right now I'm trying to insert a BigDecimal[][][] into a Postgres DB column of type bytea. The insertion works but the problem is that the encryption code I've added in the converters/binding doesn't seem to run. Unfortunately, when I check the database I'm seeing an unencrypted 3D matrix. (FYI my encryption utility code is tested and does work)

To test, I put my encryption code in the DAO layer and the BigDecimal[][][] matrix does get encrypted on DB inserts. Although I could do this it defeats the purpose of using converters/bindings for encryption.

So my question: With the code I provided below am I doing anything wrong that is preventing the encryption code in my converter/binding to be run? I thought after a Prepared Statement is executed the converter is the next step but maybe not? I have a lack of knowledge on just when the converter/binding code gets called in the whole JOOQ flow so any insight is much appreciated! Thanks :D

First I'm using a PreparedStatment in a DAO to execute the insert query. I can't show the full code but basically for the stmt I'm setting the BigDecimal[][][] as an object parameter:

private Result executeInsert(BigDecimal[][][] valueToEncrypt, String insertSql) {
try (Connection conn = config.connectionProvider().acquire();
         PreparedStatement stmt = conn.prepareStatement(insertSql)) {
         
    // Get a human readable version of the 3d matrix to insert into the db. 
    PostgresReadableArray humanReadableMatrix = getPostgresReadableArray(valueToEncrypt)
    stmt.setObject(parameterIndex++, humanReadableMatrix, Types.OTHER);
    
    ResultSet res = stmt.executeQuery();
}
...
}

I am currently attaching the binding to a codegen xml file here:

<forcedType>
  <userType>
    java.math.BigDecimal[][][]
  </userType>
  <binding>com.myapp.EncryptionBinding</binding>
  <includeExpression>matrix_column</includeExpression>
  <includeTypes>bytea</includeTypes>
</forcedType>

Here is my binding class EncryptionBinding:

public class EncryptionBinding implements Binding<byte[], BigDecimal[][][]> {

  @Override
  public Converter<byte[], BigDecimal[][][]> converter() {
    return new MatrixConverter();
  }

  // Rending a bind variable for the binding context's value and casting it to the json type
  @Override
  public void sql(BindingSQLContext<BigDecimal[][][]> ctx) throws SQLException {
  }

  // Registering VARCHAR types for JDBC CallableStatement OUT parameters
  @Override
  public void register(BindingRegisterContext<BigDecimal[][][]> ctx) throws SQLException {
    ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
  }

  // Converting the BigDecimal[][][] to a Encrypted value and setting that on a JDBC PreparedStatement
  @Override
  public void set(BindingSetStatementContext<BigDecimal[][][]> ctx) throws SQLException {
    ctx.statement().setBytes(ctx.index(), ctx.convert(converter()).value());
  }

  ...

Here is my converter class MatrixConverter used in the above EncryptionBinding class:

public class MatrixConverter extends AbstractConverter<byte[], BigDecimal[][][]> {

  private static final Logger logger = LoggerFactory.getLogger(MatrixConverter.class);

  public MatrixConverter() {
    super(byte[].class, BigDecimal[][][].class);
  }

  @Override
  public BigDecimal[][][] from(byte[] databaseObject) {
     return EncryptionUtils.decrypt(databaseObject); 
  }

  @Override
  public byte[] to(BigDecimal[][][] userObject) {
     return EncryptionUtils.encrypt(JsonUtils.toJson(userObject)); 
  }
}
jban
  • 23
  • 4

0 Answers0