Parsing problem when escaping character

[croudet]

Jdbi: 3.12.2 DB: postgres 12

The following query fails with jdbi and works with direct jdbc call:

    static String sql =  "WITH main AS (SELECT sl.deviceid, replace(substr(sa.downloadurl, char_length(r.name) + 1), '\\', '/') AS downloadurl "
            + "FROM stagedassets sa "
            + "LEFT JOIN stagedlocations sl USING (stagedassetid) "
            + "LEFT JOIN syncpoints sp USING (syncpointid) "
            + "LEFT JOIN repositories r USING (repositoryid)),"
             + "repoassets AS (SELECT distinct downloadurl FROM main),"
             + "deviceassets AS (SELECT distinct ? || deviceid || '/resources' AS downloadurl FROM main),"
             + "assets AS (SELECT * FROM repoassets UNION ALL SELECT * FROM deviceassets) "
             + "SELECT * FROM assets";

    @Test
    public void jdbiBugJdbc() throws SQLException {
       try (Connection conn = ds.getConnection();
            PreparedStatement s = conn.prepareStatement(sql)) {
           s.setString(1, "aa");
           try (ResultSet rs = s.executeQuery()) {
               while (rs.next()) {

            }
           }
       }
    }

    @Test
    public void jdbiBug() {
       jdbi.useHandle(handle -> handle.createQuery(sql).bind(0, "aa").mapToMap().list());
    }

With jdbi I have the following exception:

org.jdbi.v3.core.statement.UnableToCreateStatementException: Superfluous named parameters provided while the query declares none: '{positional:{0:aa}, named:{}, finder:[]}'. This check may be disabled by calling getConfig(SqlStatements.class).setUnusedBindingAllowed(true) or using @AllowUnusedBindings in SQL object. [statement:"WITH main AS (SELECT sl.deviceid, CASE WHEN sa.source != sa.downloadurl THEN replace(substr(sa.downloadurl, char_length(r.name) + 1), '\', '/') ELSE sa.downloadurl END AS downloadurl FROM stagedassets sa LEFT JOIN stagedlocations sl USING (stagedassetid) LEFT JOIN syncpoints sp USING (syncpointid) LEFT JOIN repositories r USING (repositoryid) ),repoassets AS (SELECT downloadurl FROM main),deviceassets AS (SELECT ? || deviceid || '/resources' AS downloadurl FROM main),assets AS (SELECT * FROM repoassets UNION ALL SELECT * FROM deviceassets) SELECT * FROM assets", arguments:{positional:{0:aa}, named:{}, finder:[]}]
    at org.jdbi.v3.core.statement.ArgumentBinder.bindNamedCheck(ArgumentBinder.java:111)
    at org.jdbi.v3.core.statement.ArgumentBinder.bindNamed(ArgumentBinder.java:83)
    at org.jdbi.v3.core.statement.ArgumentBinder.bind(ArgumentBinder.java:60)
    at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1661)
    at org.jdbi.v3.core.result.ResultProducers.lambda$getResultSet$2(ResultProducers.java:67)
    at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:54)
    at org.jdbi.v3.core.result.ResultIterable.stream(ResultIterable.java:228)
    at org.jdbi.v3.core.result.ResultIterable.collect(ResultIterable.java:284)
    at org.jdbi.v3.core.result.ResultIterable.list(ResultIterable.java:273)

Looks like the problems is in replace(substr(sa.downloadurl, char_length(r.name) + 1), '\\', '/') in particular '\\' java escape sequence mixed with CTEs.

Note that following works:

 @Test
    public void jdbiWorks() {
       String s = jdbi.withHandle(handle -> handle.createQuery("select replace(substr('a\\b\\c\\d', 2), '\\', '/')").mapTo(String.class).one());
       System.out.println(s);
    }

Current workaround is to use chr(92) instead of '\\'.

[leaumar]

if the ? in SELECT distinct ? || is your target parameter for your "aa", I suspect you'll need to define instead of bind. Or you can try with a named binding.

It feels weird to me that it works in vanilla jdbc though. Is it possible the vanilla jdbc subtly malfunctions and interprets the ? as a literal, ignoring your setString? Does it still work if you omit the setString?

[croudet]

When I remove the setString for the jdbc case I have an Exception:

org.postgresql.util.PSQLException: No value specified for parameter 1.

[croudet]

I confirm that if I remove the placeholder in SELECT distinct ? || deviceid. The exception does no longer occur.

[stevenschlansker]

if the ? in SELECT distinct ? || is your target parameter for your "aa", I suspect you'll need to define instead of bind.

This is at best an ugly workaround: it opens SQL injection possibilities, so please don't generally recommend it. (This isn't the same case as needing to define e.g. table names, this is an actual bound parameter, and per OP it works fine in JDBC)

Or you can try with a named binding.

That's an interesting test, it may well avoid the parsing problem entirely.

[croudet]

define does not work. It does not replace the token in the sql. Named binding does not work, the same exception is thrown: SELECT distinct :prefix || deviceid ||

org.jdbi.v3.core.statement.UnableToCreateStatementException: Superfluous named parameters provided while the query declares none: '{positional:{}, named:{prefix:'aa'}, finder:[]}'. This check may be disabled by calling getConfig(SqlStatements.class).setUnusedBindingAllowed(true) or using @AllowUnusedBindings in SQL object. [statement:"WITH main AS (SELECT sl.deviceid, replace(substr(sa.downloadurl, char_length(r.name) + 1), '\', '/') AS downloadurl FROM stagedassets sa LEFT JOIN stagedlocations sl USING (stagedassetid) LEFT JOIN syncpoints sp USING (syncpointid) LEFT JOIN repositories r USING (repositoryid)),repoassets AS (SELECT distinct downloadurl FROM main),deviceassets AS (SELECT distinct :prefix || deviceid || '/resources' AS downloadurl FROM main),assets AS (SELECT * FROM repoassets UNION ALL SELECT * FROM deviceassets) SELECT * FROM assets", arguments:{positional:{}, named:{prefix:'aa'}, finder:[]}]
    at org.jdbi.v3.core.statement.ArgumentBinder.bindNamedCheck(ArgumentBinder.java:111)
    at org.jdbi.v3.core.statement.ArgumentBinder.bindNamed(ArgumentBinder.java:83)
    at org.jdbi.v3.core.statement.ArgumentBinder.bind(ArgumentBinder.java:60)

[stevenschlansker]

Oh well. Hopefully one parsing fix will end up fixing both issues

[leaumar]

Just to clarify: I suggested defining earlier because I assumed OP wanted to use aa as a column name, i.e. select distinct aa from ..., rather than as a value (select 'aa').

[stevenschlansker]

Yeah, I see how that could be confusing, thanks for clarifying.