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));
}
}