Spring Incorrect named parameter parsing results in InvalidDataAccessApiUsageException: No value supplied for the SQL parameter [SPR-13181]

Hendy Irawan opened SPR-13181 and commented

For the following SQL:

UPDATE ppdbbandung2015.school
  SET
    name=:name,
    code=:code,
    level=:level,
    address=:address,
    address_district=:address_district,
    address_subdistrict=:address_subdistrict,
    address_rw=:address_rw,
    address_rt=:address_rt,
    is_border=:is_border,
    foreigner_percentage=:foreigner_percentage,
    option_i=:option_i,
    option=ARRAY[:option]::json[]
  WHERE id=:id;

Notice that we have ARRAY[:option] which should be proper when executed in PostgreSQL, however Spring parses the ] after :option as part of the parameter name, resulting in:

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'option]': No value registered for key 'option]'
    at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:336)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:374)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318)
    at org.soluvas.scrape.core.sql.TableDmlGenerator.lambda$upsert$6(TableDmlGenerator.java:128)
    at org.soluvas.scrape.core.sql.TableDmlGenerator$$Lambda$3/1563634025.doInTransaction(Unknown Source)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
    at org.soluvas.scrape.core.sql.TableDmlGenerator.upsert(TableDmlGenerator.java:108)
    at org.soluvas.scrape.core.UpsertTest.upsert(UpsertTest.java:76)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

Workaround: just add a space after the parameter name, however such special characters (e.g. "]", "{" ) should never form part of a named parameter.

Related to #17770, #17771.

---

Affects: 4.1.7

Comment From: spring-projects-issues

Bulk closing outdated, unresolved issues. Please, reopen if still relevant.

Comment From: foal

Still relevant

Comment From: Chen-Clark

?

Comment From: snicoll

I did a quick test and didn't manage to reproduce this issue. Given that several people here claim it is relevant, please share a small test that reproduces the problem. Tests of NamedParameterUtils are quite easy to build.

This is the test that I used:

@Test // gh-17773
public void parseSqlStatementWithPostgresArray() {
    String sql = """
        UPDATE ppdbbandung2015.school
        SET
            name=:name,
            code=:code,
            level=:level,
            address=:address,
            address_district=:address_district
            address_subdistrict=:address_subdistrict
            address_rw=:address_rw
            address_rt=:address_rt
            is_border=:is_border,
            foreigner_percentage=:foreigner_percentage,
            option_i=:option_i,
            option=ARRAY[:option]::json[]
        WHERE id=:id;""";

    ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
    assertThat(parsedSql.getParameterNames()).containsOnly(
            "name", "code", "level", "address", "address_district",
            "address_subdistrict", "address_rw", "address_rt",
            "is_border", "foreigner_percentage", "option_i",
            "option", "id");
}

Comment From: sbrannen

This appears to have been fixed in #27716 and #27925.

If you still encounter this issue with Spring Framework 6.0.13 (or a later version), please create a new issue.

Thanks