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: - #12613 NamedParameterUtils.parseSqlStatement can't handle quote following comment end - #21018 [Regression] NamedParameterUtils::parseSqlStatement cannot parse chinese param name - #21017 NamedParameterUtils.isParameterSeparator throws ArrayIndexOutOfBoundsException for non ASCII characters - #21204 NamedParameterUtils.parseSqlStatement should parse :{x} style parameter correctly - #20759 Expose parameter names from ParsedSql - #21055 NamedParameterJdbcTemplate doesn't support postgresql dollar quoting $$ - #20908 SqlParameterSource needs method for extracting parameter names
Referenced from: pull request https://github.com/spring-projects/spring-framework/pull/1637
Comment From: 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.
Comment From: spring-projects-issues
Juergen Hoeller commented
Reopening due to some test failures in Spring Integration...
Comment From: 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
Comment From: 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.
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