Under preferQueryMode = simple, a PreparedStatement calls setBytes first and then call executeQuery, raise an exception. #3169

aixinming · 2024-03-19T03:30:54Z

Describe the issue
Under preferQueryMode = simple, a PreparedStatement calls setBytes first and then call executeQuery, resulting in the following exception. Through debug, setBytes will eventually call the toString method of the SimpleParameterList class, but this method doesn't handle the bytea type. Is this an expected behavior?

org.postgresql.util.PSQLException: ERROR: syntax error at or near "and"
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
        at JdbcTest.main(JdbcTest.java:22)

Driver Version?
42.7.3 release

Java Version?
openjdk 21.0.2

OS Version?
macOs 13.0

PostgreSQL Version?
PostgreSQL 14.11 (Homebrew) on aarch64-apple-darwin22.6.0, compiled by Apple clang version 15.0.0 (clang-1500.1.0.2.5), 64-bit

To Reproduce
Steps to reproduce the behaviour:

Expected behaviour
Under preferQueryMode = simple , PreparedStatement call setBytes, executeQuery, It returns the correct result instead of throwing an exception.

Logs
nologs

Using the following template code make sure the bug can be replicated in the driver alone.

import java.sql.*;
public class JdbcTest {
        public static void main(String[] args) {
                Connection conn = null;
                String url = "jdbc:postgresql://localhost:5432/test?preferQueryMode=simple";
                String user = "test";
                String pass = "";

                try {
                        conn = DriverManager.getConnection(url, user, pass);

                        Statement stmt = conn.createStatement();
                        stmt.execute("drop table if exists t");
                        stmt.execute("create table t (c1 int, c2 bytea, c3 text)");
                        stmt.execute("insert into t select i, i::text::bytea, i::text from generate_series(0, 9) i");

                        PreparedStatement pstmt = conn.prepareStatement("select c1 from t where c2 = ? and c3 = ?");
                        pstmt.setBytes(1, "3".getBytes());
                        pstmt.setString(2, "3");

                        ResultSet rs = pstmt.executeQuery();
                        while (rs.next()) {
                                System.out.println(rs.getInt(1));
                        }
                } catch (SQLException ex) {
                        ex.printStackTrace();
                }
        }
}

The text was updated successfully, but these errors were encountered:

sehrope · 2024-03-19T11:36:29Z

This is confirmed to be a bug. Here's a (failing) test case to add to the repo to verify it:

// pgjdbc/src/test/java/org/postgresql/jdbc/ParameterTest.java
/*
 * Copyright (c) 2024, PostgreSQL Global Development Group
 * See the LICENSE file in the project root for more information.
 */

package org.postgresql.jdbc;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import org.postgresql.test.TestUtil;

import org.junit.jupiter.api.Test;

import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ParameterTest {
  @Test
  public void handleBytea() throws SQLException {
    try (Connection conn = TestUtil.openDB()) {
      TestUtil.execute(conn, "CREATE TEMPORARY TABLE bytea_test (a int, b bytea)");
      TestUtil.execute(conn, "INSERT INTO bytea_test SELECT x, x::text::bytea FROM generate_series(1, 5) x");

      PreparedStatement stmt = conn.prepareStatement("SELECT FROM bytea_test WHERE b = ?");
      stmt.setBytes(1, "1".getBytes(StandardCharsets.UTF_8));
      ResultSet rs = stmt.executeQuery();
      rs.next();
    }
  }
}

Running this test on master gives the following error on the server:

2024-03-19 11:14:38.855 UTC [241] ERROR:  syntax error at end of input at character 35
2024-03-19 11:14:38.855 UTC [241] STATEMENT:  SELECT FROM bytea_test WHERE b = ?

The bytea parameter is indeed not being serialized and the driver is just putting a ? in its place.

At first I thought this was somehow related to the recent CVE fix for simple parameters but I'm able to reproduce this atop 42.7.1 (which precedes the CVE fix) as well as 42.6.0 (I didn't try anything else older). I don't think this has been working for a while and it just happens that nobody is using bytea params with simple query mode.

I think this is the line that's returning the ? for any unknown data types:

pgjdbc/pgjdbc/src/main/java/org/postgresql/core/v3/SimpleParameterList.java

Line 316 in 24f2c7e

return "?";

@davecramer @vlsi What's surprising is that I'd expect us to use setBytes(...) somewhere in the test suite as any uses with simple query mode should trigger this error. But searching for that in the test suite we see that those tests explicitly skip in simple query mode: https://github.com/pgjdbc/pgjdbc/blob/24f2c7eea4764cd4e09c2c71bc8c38ee5d4178e5/pgjdbc/src/test/java/org/postgresql/test/jdbc3/Jdbc3CallableStatementTest.java#L495C5-L495C12

vlsi · 2024-03-19T11:55:38Z

But searching for that in the test suite we see that those tests explicitly skip in simple query mode:

We have 67 skipped tests, so some of the cases are not implemented or not tested.

WARNING 178,9sec, 4930 completed,   0 failed,  67 skipped, Gradle Test Run :postgresql:test

fixes pgjdbc#3169

siddeshas07 mentioned this issue Mar 26, 2024

updated SimpleParameterList to fix the byte-array - Addresses a bug that caused syntax errors when executing SELECT statements with parameterized byte arrays #3181

Closed

aixinming mentioned this issue Apr 1, 2024

fix Issue # 3169 bytea types not handled in SimpleQuery mode #3187

Closed

vlsi added a commit to vlsi/pgjdbc that referenced this issue May 12, 2024

fix: support bytea in preferQueryMode=simple

9ba4f9d

fixes pgjdbc#3169

vlsi added a commit to vlsi/pgjdbc that referenced this issue May 12, 2024

fix: support bytea in preferQueryMode=simple

15d8a5c

fixes pgjdbc#3169

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Under preferQueryMode = simple, a PreparedStatement calls setBytes first and then call executeQuery, raise an exception. #3169

Under preferQueryMode = simple, a PreparedStatement calls setBytes first and then call executeQuery, raise an exception. #3169

aixinming commented Mar 19, 2024

sehrope commented Mar 19, 2024

vlsi commented Mar 19, 2024

Under preferQueryMode = simple, a PreparedStatement calls setBytes first and then call executeQuery, raise an exception. #3169

Under preferQueryMode = simple, a PreparedStatement calls setBytes first and then call executeQuery, raise an exception. #3169

Comments

aixinming commented Mar 19, 2024

sehrope commented Mar 19, 2024

vlsi commented Mar 19, 2024