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

[aixinming]

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

[sehrope]

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]

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