NamedParameterJdbcTemplate fails on PostgreSQL array [SPR-16340]

[spring-projects-issues]

Nikita Khateev opened SPR-16340 and commented

There is a possible bug while parsing SQL query for PostgreSQL DB. If you will try to execute a named query like this:

UPDATE table SET name = array[:param]

The name of param will be parsed as 'param]', which is incorrect. However, if you will try to execute this query:

UPDATE table SET name = array[:param ]

It will parse param name correctly.

---

Affects: 5.0.2

Issue Links:

• NamedParameterUtils.parseSqlStatement can't handle quote following comment end [SPR-7958] #12613 NamedParameterUtils.parseSqlStatement can't handle quote following comment end
• [Regression] NamedParameterUtils::parseSqlStatement cannot parse chinese param name [SPR-16473] #21018 [Regression] NamedParameterUtils::parseSqlStatement cannot parse chinese param name
• NamedParameterUtils.isParameterSeparator throws ArrayIndexOutOfBoundsException for non ASCII characters [SPR-16472] #21017 NamedParameterUtils.isParameterSeparator throws ArrayIndexOutOfBoundsException for non ASCII characters
• NamedParameterUtils.parseSqlStatement should parse :{x} style parameter correctly [SPR-16663] #21204 NamedParameterUtils.parseSqlStatement should parse :{x} style parameter correctly
• Expose parameter names from ParsedSql [SPR-16211] #20759 Expose parameter names from ParsedSql
• NamedParameterJdbcTemplate doesn't support postgresql dollar quoting $$ [SPR-16512] #21055 NamedParameterJdbcTemplate doesn't support postgresql dollar quoting $$
• SqlParameterSource needs method for extracting parameter names [SPR-16361] #20908 SqlParameterSource needs method for extracting parameter names

Referenced from: pull request #1637

[spring-projects-issues]

Juergen Hoeller commented

Thanks for raising this! I ended up up with a different implementation, refactoring the separator detection in NamedParameterUtils to avoid iterating over even more separator characters now. That said, I've been reusing your test and attributed you as an author in NamedParameterJdbcTemplateTests.

[spring-projects-issues]

Juergen Hoeller commented

Reopening due to some test failures in Spring Integration...

[spring-projects-issues]

Artem Bilan commented

Yes, after this change Spring Integration JDBC module has started failing: https://build.spring.io/browse/INT-FATS5IC-381

The main reason that we use BeanPropertySqlParameterSourceFactory by default with the treatment of param names as property accessors to the underlying BeanPropertySqlParameterSource with its BeanWrapper.
So, we can have a query like:

insert into foos (id, status, name) values (:headers[$foo_id], 0, :payload)

The :headers\[$foo_id] means to get a headers property against org.springframework.messaging.Message and perform key evaluation in a map style via [] operator:

/**
 * Marker that indicates the start of a property key for an
 * indexed or mapped property like "person.addresses[0]".
 */
String PROPERTY_KEY_PREFIX = "[";

In the end I would like to say that it looks like the value is evaluated correctly, only the problem that our param "name" isn't replaced properly i the final query:

org.springframework.messaging.MessageHandlingException: error occurred in message handler [org.springframework.integration.jdbc.JdbcMessageHandler#0]; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into foos (id, status, name) values (?[$foo_id], 0, ?)]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO FOOS (ID, STATUS, NAME) VALUES (?[$FOO_ID[*]], 0, ?) "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ]"; SQL statement:
insert into foos (id, status, name) values (?[$foo_id], 0, ?) [42001-196], failedMessage=GenericMessage [payload=foo, headers={$foo_id=abc, id=2333574d-d76e-0f39-9500-e581f54edb38, timestamp=1515642860349}]
	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:153)
	at org.springframework.integration.dispatcher.AbstractDispatcher.tryOptimizedDispatch(AbstractDispatcher.java:116)
	at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:132)
	at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:105)
	at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:73)
	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:438)
	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:388)
	at org.springframework.integration.jdbc.config.JdbcMessageHandlerParserTests.testDollarHeaderOutboundChannelAdapter(JdbcMessageHandlerParserTests.java:79)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	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.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:114)
	at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:57)
	at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:66)
	at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:51)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
	at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
	at com.sun.proxy.$Proxy1.processTestClass(Unknown Source)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:108)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:146)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:128)
	at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:404)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:63)
	at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:46)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:55)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into foos (id, status, name) values (?[$foo_id], 0, ?)]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO FOOS (ID, STATUS, NAME) VALUES (?[$FOO_ID[*]], 0, ?) "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ]"; SQL statement:
insert into foos (id, status, name) values (?[$foo_id], 0, ?) [42001-196]
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:871)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:316)
	at org.springframework.integration.jdbc.JdbcMessageHandler.executeUpdateQuery(JdbcMessageHandler.java:198)
	at org.springframework.integration.jdbc.JdbcMessageHandler.handleMessageInternal(JdbcMessageHandler.java:153)
	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:141)
	... 54 more
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO FOOS (ID, STATUS, NAME) VALUES (?[$FOO_ID[*]], 0, ?) "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ]"; SQL statement:
insert into foos (id, status, name) values (?[$foo_id], 0, ?) [42001-196]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.getSyntaxError(DbException.java:205)
	at org.h2.command.Parser.getSyntaxError(Parser.java:541)
	at org.h2.command.Parser.read(Parser.java:3258)
	at org.h2.command.Parser.readTerm(Parser.java:3048)
	at org.h2.command.Parser.readFactor(Parser.java:2390)
	at org.h2.command.Parser.readSum(Parser.java:2377)
	at org.h2.command.Parser.readConcat(Parser.java:2347)
	at org.h2.command.Parser.readCondition(Parser.java:2178)
	at org.h2.command.Parser.readAnd(Parser.java:2150)
	at org.h2.command.Parser.readExpression(Parser.java:2142)
	at org.h2.command.Parser.parseInsert(Parser.java:1093)
	at org.h2.command.Parser.parsePrepared(Parser.java:417)
	at org.h2.command.Parser.parse(Parser.java:321)
	at org.h2.command.Parser.parse(Parser.java:297)
	at org.h2.command.Parser.prepareCommand(Parser.java:258)
	at org.h2.engine.Session.prepareLocal(Session.java:578)
	at org.h2.engine.Session.prepareCommand(Session.java:519)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
	at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:230)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
	... 60 more

Thanks

[spring-projects-issues]

Juergen Hoeller commented

I'm afraid we'll have to back this out and reconsider it for 5.1 instead, possibly in combination with a smarter parsing algorithm.

[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