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

[spring-projects-issues]

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

[spring-projects-issues]

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

[foal]

Still relevant

[Chen-Clark]

?

[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");
}

[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