passing members to any SCIM operation fails with sql persistence v8.0.17 and v8.0.18

[moabu]

Running scim cli to create a group with members as the payload below or to update a group with members or to do any operation that passes members fails. This is due to the MEMBER keyword
being reserved in the mysql versions 8.0.17 and 8.0.18. The solution is to avoid these mysql version and to upgrade immediately. We need to check with reserved words across our endpoints payloads.

Example payload that failed :

{
  "schemas": [
    "urn:ietf:params:scim:schemas:core:2.0:Group"
  ],
  "id": "0c7f4a14-3453-4508-9f4c-9613f272f4cd",
  "displayName": "tester",
  "members": [
    {
      "value": "408f9a45-00d3-41be-b4a3-946b2d920b16"
    }
  ]
}

Error received :

{'schemas': ['urn:ietf:params:scim:api:messages:2.0:Error'], 'status': '500', 'scimType': '', 'detail': "Unexpected error: Failed to persist entry: 'inum=0c7f4a14-3453-4508-9f4c-9613f272f4cd,ou=groups,o=jans'"}

Error from the auth server:

io.jans.orm.exception.EntryPersistenceException: Failed to persist entry: 'inum=0c7f4a14-3453-4508-9f4c-9613f272f4cd,ou=groups,o=jans'
	at io.jans.orm.sql.impl.SqlEntryManager.persist(SqlEntryManager.java:216) ~[jans-orm-sql-1.0.0-SNAPSHOT.jar:?]
	at io.jans.orm.impl.BaseEntryManager.persist(BaseEntryManager.java:125) ~[jans-orm-core-1.0.0-SNAPSHOT.jar:?]
	at jdk.internal.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
	at org.jboss.weld.bean.proxy.AbstractBeanInstance.invoke(AbstractBeanInstance.java:38) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:106) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at io.jans.orm.PersistenceEntryManager$EntityManager$319119935$Proxy$_$$_WeldClientProxy.persist(Unknown Source) ~[jans-orm-core-1.0.0-SNAPSHOT.jar:?]
	at io.jans.scim.service.GroupService.addGroup(GroupService.java:59) ~[jans-scim-service-1.0.0-SNAPSHOT.jar:?]
	at io.jans.scim.service.GroupService$Proxy$_$$_WeldClientProxy.addGroup(Unknown Source) ~[jans-scim-service-1.0.0-SNAPSHOT.jar:?]
	at io.jans.scim.service.scim2.Scim2GroupService.createGroup(Scim2GroupService.java:288) ~[classes/:?]
	at io.jans.scim.service.scim2.Scim2GroupService$Proxy$_$$_WeldClientProxy.createGroup(Unknown Source) ~[classes/:?]
	at io.jans.scim.ws.rs.scim2.GroupWebService.createGroup(GroupWebService.java:180) ~[classes/:?]
	at io.jans.scim.ws.rs.scim2.GroupWebService$Proxy$_$$_WeldSubclass.createGroup$$super(Unknown Source) ~[classes/:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
	at org.jboss.weld.interceptor.proxy.TerminalAroundInvokeInvocationContext.proceedInternal(TerminalAroundInvokeInvocationContext.java:51) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.interceptor.proxy.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:78) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at io.jans.scim.service.scim2.interceptor.ReferenceURIInterceptor.manage(ReferenceURIInterceptor.java:64) ~[classes/:?]
	at jdk.internal.reflect.GeneratedMethodAccessor259.invoke(Unknown Source) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
	at org.jboss.weld.interceptor.reader.SimpleInterceptorInvocation$SimpleMethodInvocation.invoke(SimpleInterceptorInvocation.java:73) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.interceptor.proxy.InterceptorMethodHandler.executeAroundInvoke(InterceptorMethodHandler.java:84) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.interceptor.proxy.InterceptorMethodHandler.executeInterception(InterceptorMethodHandler.java:72) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.interceptor.proxy.InterceptorMethodHandler.invoke(InterceptorMethodHandler.java:56) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.bean.proxy.CombinedInterceptorAndDecoratorStackMethodHandler.invoke(CombinedInterceptorAndDecoratorStackMethodHandler.java:79) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at org.jboss.weld.bean.proxy.CombinedInterceptorAndDecoratorStackMethodHandler.invoke(CombinedInterceptorAndDecoratorStackMethodHandler.java:68) ~[weld-core-impl-3.1.8.Final.jar:3.1.8.Final]
	at io.jans.scim.ws.rs.scim2.GroupWebService$Proxy$_$$_WeldSubclass.createGroup(Unknown Source) ~[classes/:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?]
	at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:170) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:130) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:643) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:507) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:457) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:459) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:419) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:393) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:68) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:492) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:261) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:161) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:164) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:247) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:249) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:60) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:55) ~[resteasy-core-4.5.7.Final.jar:4.5.7.Final]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590) ~[jetty-servlet-api-4.0.6.jar:?]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764) ~[jetty-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1619) ~[jetty-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.websocket.servlet.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:164) ~[websocket-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202) ~[jetty-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1594) ~[jetty-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:506) ~[jetty-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:580) ~[jetty-security-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1571) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1372) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:463) ~[jetty-servlet-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1544) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1294) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:149) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.Server.handle(Server.java:562) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:406) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:663) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:398) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282) ~[jetty-server-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:319) ~[jetty-io-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100) ~[jetty-io-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.io.SocketChannelEndPoint$1.run(SocketChannelEndPoint.java:101) ~[jetty-io-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:412) ~[jetty-util-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:381) ~[jetty-util-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:268) ~[jetty-util-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:138) ~[jetty-util-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:378) [jetty-util-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:894) [jetty-util-10.0.6.jar:10.0.6]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1038) [jetty-util-10.0.6.jar:10.0.6]
	at java.lang.Thread.run(Unknown Source) [?:?]
Caused by: io.jans.orm.exception.operation.PersistenceException: Failed to add entry
	at io.jans.orm.sql.operation.impl.SqlOperationServiceImpl.addEntryImpl(SqlOperationServiceImpl.java:199) ~[jans-orm-sql-1.0.0-SNAPSHOT.jar:?]
	at io.jans.orm.sql.operation.impl.SqlOperationServiceImpl.addEntry(SqlOperationServiceImpl.java:168) ~[jans-orm-sql-1.0.0-SNAPSHOT.jar:?]
	at io.jans.orm.sql.impl.SqlEntryManager.persist(SqlEntryManager.java:211) ~[jans-orm-sql-1.0.0-SNAPSHOT.jar:?]
	... 92 more
Caused by: com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for insert into `production-gluu-idp-tb`.jansGrp (jansMetaCreated, jansMetaLastMod, jansMetaLocation, displayName, inum, member, o, jansStatus, objectClass, dn, doc_id)

[moabu]

The solution is to avoid these mysql versions and to upgrade immediately.

[iromli]

The actual issue is we have unescaped query that injecting value into jansGrp.member column.

[moabu]

I agree . We need to escape the query.

[devrimyatar]

We advice Ubuntu20, CentOS8 or SuSE15 for Linux setup (https://github.com/JanssenProject/jans/wiki/Install-Jans-on-a-VM#supported-linux-distributions)
These distros have
Ubuntu20 MySQL-8.0.28
CentOS8 MySQL-8.0.19
SuSE15 10.5.15-MariaDB

And we don't have such issue.

[yurem]

In SQL ORM we use http://querydsl.com library which build queries internally and use standard JDBC layer to execute them. I think it's problematic DB version rather than bug in querydsl.