[I] PostgreSQL text[] parameters: backslashes silently dropped, NULL elements rejected [shardingsphere]

["viminizer (via GitHub)" <gi...@apache.org>]

viminizer opened a new issue, #39394:
URL: https://github.com/apache/shardingsphere/issues/39394

   ## Bug Report
   
   ### Which version of ShardingSphere did you use?
   
   master @ 3c47902d
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   
   ShardingSphere-Proxy
   
   ### Expected behavior
   
   When a `text[]` parameter is bound to a prepared statement, the element values should reach the storage node unchanged, including elements that are `NULL` and elements containing `\` or `"`.
   
   ### Actual behavior
   
   Backslashes are silently removed from the stored value. `NULL` elements and elements containing a double quote cause the statement to fail.
   
   No configuration is needed to reach this. pgjdbc sends `text[]` in binary format by default, so a plain `setArray` on a default connection hits it on the first execution.
   
   Backend is PostgreSQL 16, `t_order` sharded across `t_order_0` and `t_order_1` by `order_id % 2`, column `tags text[]`.
   
   | parameter sent | PostgreSQL 16 direct | ShardingSphere-Proxy |
   |---|---|---|
   | `{"a", "b"}` | `{a,b}` | `{a,b}` |
   | `{"a", null, "c"}` | `{a,NULL,c}` | `ERROR: Unknown exception. More details: java.lang.NegativeArraySizeException: -1` |
   | `{"a\b", "c"}` | `{"a\\b",c}` | `{ab,c}` |
   | `{"a\"b", "c"}` | `{"a\"b",c}` | `ERROR: malformed array literal: "{"a"b","c"}"` |
   | `{"a,b", "c"}` | `{"a,b",c}` | `{"a,b",c}` |
   | `{"", "c"}` | `{"",c}` | `{"",c}` |
   
   The backslash case is the most serious because it does not report an error. Querying the storage node directly:
   
   ```
   demo_ds_0=# SELECT tags, tags[1], length(tags[1]) FROM t_order WHERE order_id = 22;    -- written directly
       tags    | tags | length
   ------------+------+--------
    {"a\\b",c} | a\b  |      3
   
   demo_ds_0=# SELECT tags, tags[1], length(tags[1]) FROM t_order_0 WHERE order_id = 22;  -- written through the proxy
     tags  | tags | length
   --------+------+--------
    {ab,c} | ab   |      2
   ```
   
   The value on disk is wrong and the application is not told.
   
   The proxy itself stays up. Only the individual statement fails in the `NULL` and double quote cases.
   
   ### Reason analyze (If you can)
   
   `PostgreSQLTextArrayBinaryProtocolValue#read` decodes the binary array into a list of strings, then rebuilds a PostgreSQL array literal from them.
   
   Two problems in that rebuild.
   
   **1. Element length `-1` is not treated as NULL.**
   
   ```java
   private Collection<String> extractArrayElements(final byte[] data) {
       ByteBuffer buffer = ByteBuffer.wrap(data);
       Collection<String> result = new LinkedList<>();
       while (buffer.remaining() > 0) {
           int length = buffer.getInt();
           if (buffer.remaining() < length) {
               ...
           }
           byte[] packetData = new byte[length];
   ```
   
   In the PostgreSQL binary array format a NULL element is written with length `-1`. `buffer.remaining() < -1` is false, so execution reaches `new byte[-1]` and throws `NegativeArraySizeException`.
   
   **2. Element values are not escaped when the array literal is rebuilt.**
   
   ```java
   private String getArrayString(final Collection<String> elements) {
       StringBuilder sb = new StringBuilder();
       sb.append("{");
       Iterator<String> iterator = elements.iterator();
       while (iterator.hasNext()) {
           sb.append("\"").append(iterator.next()).append("\"");
   ```
   
   Inside a quoted element of a PostgreSQL array literal, `\` escapes the following character and `"` ends the element. Neither is escaped here, so:
   
   - `a\b` is emitted as `"a\b"`, which PostgreSQL reads back as `ab`
   - `a"b` is emitted as `"a"b"`, which is not a valid array literal
   
   This code path was added in #32848.
   
   ### Steps to reproduce the behavior
   
   1. Start PostgreSQL 16 and create the shards.
   
   ```sql
   CREATE TABLE t_order_0 (order_id int PRIMARY KEY, tags text[]);
   CREATE TABLE t_order_1 (order_id int PRIMARY KEY, tags text[]);
   ```
   
   2. Start ShardingSphere-Proxy with `t_order` sharded by `order_id % 2` over those tables.
   
   3. Bind a `text[]` parameter. No connection properties and no driver internals are required.
   
   ```java
   String url = "jdbc:postgresql://127.0.0.1:3308/sharding_db?user=root&password=root";
   try (Connection connection = DriverManager.getConnection(url);
           PreparedStatement ps = connection.prepareStatement("INSERT INTO t_order (order_id, tags) VALUES (?, ?)")) {
       ps.setInt(1, 22);
       ps.setArray(2, connection.createArrayOf("text", new String[]{"a\\b", "c"}));
       ps.executeUpdate();
   }
   ```
   
   4. Read the value back from the storage node directly and compare with the same insert run against PostgreSQL.
   
   Repeating the same statement does not change the outcome. The value is already wrong on the first execution.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] PostgreSQL text[] parameters: backslashes silently dropped, NULL elements rejected [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu closed issue #39394: PostgreSQL text[] parameters: backslashes silently dropped, NULL elements rejected
URL: https://github.com/apache/shardingsphere/issues/39394


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org