[ISSUE] AcraServer 0.93 + PostgreSQL | Encryption not working with batch insert

[Machado117]

Describe the bug Following issue 575, despite being able to do batch inserts with acra now, if I do a batch insert with encryption enabled on a table column I get an error. If the batch has only one insert it works.

To Reproduce Steps to reproduce the behavior: 1.Use the same code from the previous issue

import java.sql.*;

public class AcraCrash {

    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:9393/testdb";
        Connection conn = DriverManager.getConnection(url, "postgres", "postgres");
        PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (name, age) VALUES (?, ?)");
        conn.setAutoCommit(false);

        st.setString(1, "name1");
        st.setInt(2, 1);
        st.addBatch();

        st.setString(1, "name2");
        st.setInt(2, 2);
        st.addBatch();

        st.executeBatch();

        conn.commit();

        st.close();
        conn.close();
    }
}

2. Use this encryptor_config:

   - table: mytable
   columns:
     - name
     - age
   encrypted:
     - column: name
       data_type: str

Expected behavior The insert should work and name field should be encrypted.

Environment (please complete the following information):

• Acra version: de034708d001e24728e077077e789b1499df9bbd
• Database server and its version: PostgreSQL 9.6.10
• Installed components:
  • [ ] AcraServer
• Data-in-transit encryption between Acra and the client-side application:
  • [ ] no transport encryption
• Installation way:
  • [ ] via Docker

[Lagovas]

I reproduced error with create table mytable(name bytea, age integer) and such encryptor's config. Java driver sends string as VARCHAR (oid=1043) to store it in the database as TEXT. AcraServer captures db's response with BYTEA type and replace it to TEXT type (oid=25). And it is mismatch with driver expectations. WIthout Acra database can cast some real types that it uses for storage to the types application sends if it can be casted as 1:1. For example if database stores 64 bit integer and application sends data as 32bit integer then database can return it as 32 bit integer. But cannot do it in opposite way: 32 -> 64.

For now, Acra maps str data type to TEXT type from PostgreSQL. So you should send and deal with results as with TEXT, not VARCHAR types. I didn't find how to do it with java PostgreSQL driver quickly, because I'm not java developer. I will try to find it one more time in driver's docs more deeply, but a bit later.

[Machado117]

I see, it works if instead I encrypt the age field as int64 in acra and use setLong in the code.

[Machado117]

Just confirmed that it works if I use setCharacterStream instead of setString

[vixentael]

that was fast