Escape characters are mishandled in the LIKE clause on PostgreSQL

[ajaypaul-ibm]

Current Behavior
When persisting an entity to a PostgreSQL database EclipseLink
with a column that contains a string with the backslash character ()
the data is correctly inserted into the table.

For example, using the following entity:

@Entity(name = "Orders") // overrides the default name PurchaseOrder
public class PurchaseOrder {

    @GeneratedValue(strategy = GenerationType.UUID)
    @Id
    public UUID id;

    public String purchasedBy;

    public float total;

    @Version
    public int versionNum;

    public static PurchaseOrder of(String purchasedBy, float total) {
        PurchaseOrder inst = new PurchaseOrder();
        inst.purchasedBy = purchasedBy;
        inst.total = total;
        return inst;
    }
}

Executing the following persist methods:

PurchaseOrder order1 = PurchaseOrder.of("Escape\\Characters", 23.93f);
PurchaseOrder order2 = PurchaseOrder.of("Escape\\\\Characters", 27.97f);

tx.begin();
em.persist(order1);
em.persist(order2);
tx.commit();

Results in the following table in PostgreSQL

[12/11/24, 14:56:26:465 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] INSERT INTO ORDERS (ID, PURCHASEDBY, TOTAL, VERSIONNUM) VALUES (?, ?, ?, ?)
	bind => [d07ccfe8-07df-4d1f-8702-f0e40503237a, Escape\\Characters, 27.97, 1]
[12/11/24, 14:56:26:550 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] INSERT INTO ORDERS (ID, PURCHASEDBY, TOTAL, VERSIONNUM) VALUES (?, ?, ?, ?)
	bind => [8f96987f-1760-4493-9a9e-95b69df6277a, Escape\Characters, 23.93, 1]

id	purchaseBy	total	v
8f96987f-1760-4493-9a9e-95b69df6277a	Escape\Characters	23.93	1
d07ccfe8-07df-4d1f-8702-f0e40503237a	Escape\\Characters	27.97	1

Later when attempting to execute the a query with a LIKE clause:

tx.begin();
List<Float> totals = em.createQuery("SELECT total FROM Orders WHERE purchasedBy LIKE ?1 ORDER BY total", Float.class)
                .setParameter(1, "Escape\\\\Characters") //attempt to find `Escape\\Characters` in the database
                .getResultList();
tx.commit();

Eclipselink attempts to execute the following SQL query:

[12/11/24, 14:56:26:741 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] SELECT TOTAL FROM ORDERS WHERE PURCHASEDBY LIKE ? ORDER BY TOTAL
	bind => [Escape\\Characters]

Which returns the result:
[23.93000030517578]

Because PostgreSQL will always escape backslash characters in a LIKE clause by default.
As described in their documentation here: https://www.postgresql.org/docs/7.1/functions-matching.html
To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one may be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
NOTE: The PostgreSQL setting standard_conforming_strings=on affects the default behavior of backslash in other areas, but never within a LIKE clause.

Expected behavior
I would expect EclipseLink to modify the input parameter for any LIKE clause to escape any backslashes.
Which would result in the following SQLQuery:

[12/11/24, 14:56:26:741 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] SELECT TOTAL FROM ORDERS WHERE PURCHASEDBY LIKE ? ORDER BY TOTAL
	bind => [Escape\\\\Characters]

[rfelcman]

https://github.com/eclipse-ee4j/eclipselink/blob/master/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/PostgreSQLPlatform.java#L576C23-L576C23

[rfelcman]

https://github.com/eclipse-ee4j/eclipselink/blob/master/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/PostgreSQLPlatform.java#L709

[pardhivkrishna]

Hi @rfelcman ,
I thought of trying out some custom logic in setParameterValueInDatabaseCall() in PostgreSQLPlatform.java to modify query parameters, but it seems like EclipseLink logs SQL statements before they reach PostgreSQLPlatform.java preventing modifications to query parameters from being reflected in the logs. Specifically, when executing a query like

SELECT TOTAL FROM ORDERS WHERE PURCHASEDBY LIKE ? ORDER BY TOTAL

the following log entry appears before any custom logic in PostgreSQLPlatform.setParameterValueInDatabaseCall() is executed:

[EL Fine]: sql: 2025.02.14 11:30:34.551--ServerSession(-1685302777)--Connection(1483520773)--Thread(Thread[http-nio-8080-exec-1,5,main])--SELECT TOTAL FROM ORDERS WHERE PURCHASEDBY LIKE ? ORDER BY TOTAL 
        bind => [Escape\\Characters]

Since the SQL is logged before reaching the platform-specific handling, any changes made inside setParameterValueInDatabaseCall() (such as modifying \ to \\ in LIKE clause parameters for PostgreSQL) do not appear in the logs. Any guidance on this would be appreciated.

[pardhivkrishna]

@rfelcman
Here's a sample test app to demonstrate the scenario
2324 Escape Characters.zip

Different results are obtained when we use different databases.
For DBs which treats '\' as escape character by default in LIKE clause parameters (PostgreSQL, MySQL) the result obtained is

[23.93]

which corresponds to the row with purchasedBy as Escape\Characters

For DBs which treats '\' as normal character by default (DB2, Oracle DB, etc) the result obtained is

[27.97]

which corresponds to the row with purchasedBy as Escape\\Characters