Message-ID: <gh-pgjdbc-pgjdbc-3757-c3173546687@github.com>
From: "vlsi (@vlsi)" <noreply+vlsi@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Mon, 11 Aug 2025 07:22:29 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #3757: PreparedStatement.toString() fails for bytea parameters with at least 42.7.7
In-Reply-To: <gh-pgjdbc-pgjdbc-3757@github.com>
References: <gh-pgjdbc-pgjdbc-3757@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: vlsi
X-GitHub-Comment-Id: 3173546687
X-GitHub-Comment-Type: issue_comment
X-GitHub-Edited-At: 2025-08-11T07:23:53Z
X-GitHub-Issue: 3757
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3757#issuecomment-3173546687
Content-Type: text/plain; charset=utf-8

@rlbdv , the analysis sounds quite right, however, there's an issue with the current way of handling literals.

The important point is that `org.postgresql.core.v3.SimpleParameterList#toString(int, org.postgresql.core.v3.SqlSerializationContext)` should not fall into SQL-injection issues.

Currently, every unknown literal goes through `org.postgresql.core.v3.SimpleParameterList#quoteAndCast` which ensures the literal is quoted.

However, it is not clear how to make it work with user-provided literals which might already be quoted.
For instance, in your example you provide `pgObject.setValue("\\x01020304")` which is a [hex format](https://www.postgresql.org/docs/current/datatype-binary.html#DATATYPE-BINARY-BYTEA-HEX-FORMAT) encoding for `bytea`. Apparently, the driver should not quote `\`, so wrapping the value with `quoteAndCast` would yield an invalid literal of `\\x01020304`. On the other hand, if we blindly trust user-provided literal, it might introduce SQL-injection.

It looks like `bytea` is an exception thanks to its two special encodings.

I would suggest the following:
a) If the literal starts with `\x`, then assume it is `hex format`. Then verify the rest of the string includes only hex digits or whitespace (see PG documentation). If the verification fails, throw an error.
b) If the literal does not start with `\x`, then assume it is `bytea escape format`. In this case, apply the regular `SimpleParameterList#quoteAndCast` logic.

In other words, there should be an extra `if (value instanceof String && !value.startsWith("\\x"))`-like check before `return PGbytea.toPGLiteral` in `SimpleParameterList`.

---

Of course, it would be a non-issue if one uses `org.postgresql.jdbc.PgPreparedStatement#setBytes`. At the same time, it might be a nice idea to have something like `PGobject` which would allow users to pass (in and out) binary-encoded values (I guess it was requested already some time ago).