search

wso2 / product-is

Welcome to the WSO2 Identity Server source code! For info on working with the WSO2 Identity Server repository and contributing code, click the link below.
http://wso2.github.io/
Apache License 2.0
746 stars 724 forks source link

SCIM2 users endpoint: multi-attribute filtering is broken with MsSQL userstore #9551

Closed tharindu-b-hewage closed 3 years ago

tharindu-b-hewage commented 4 years ago

Describe the issue: $subject. Debugging the code revealed that there is a syntax error while trying to execute https://github.com/wso2/carbon-kernel/blob/05acd1eed9fea71dc2943fa36eb90dd32b2ddc1f/core/org.wso2.carbon.user.core/src/main/java/org/wso2/carbon/user/core/jdbc/UniqueIDJDBCUserStoreManager.java#L3256.

I was able to debug and manually print the following stacktrace.

org.wso2.carbon.user.core.UserStoreException: Error occur while doGetUserList for multi attribute searching
    at org.wso2.carbon.user.core.jdbc.UniqueIDJDBCUserStoreManager.doGetUserListWithID(UniqueIDJDBCUserStoreManager.java:3275)
    at org.wso2.carbon.user.core.common.AbstractUserStoreManager.getUserListWithID(AbstractUserStoreManager.java:14337)
    at org.wso2.carbon.identity.scim2.common.impl.SCIMUserManager.getFilteredUsersFromMultiAttributeFiltering(SCIMUserManager.java:1674)
    at org.wso2.carbon.identity.scim2.common.impl.SCIMUserManager.getMultiAttributeFilteredUsers(SCIMUserManager.java:1464)
    at org.wso2.carbon.identity.scim2.common.impl.SCIMUserManager.filterUsers(SCIMUserManager.java:933)
    at org.wso2.carbon.identity.scim2.common.impl.SCIMUserManager.listUsersWithGET(SCIMUserManager.java:442)
    at org.wso2.charon3.core.protocol.endpoints.UserResourceManager.listWithGET(UserResourceManager.java:347)
    at org.wso2.carbon.identity.scim2.provider.resources.UserResource.getUser(UserResource.java:207)
    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.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:193)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:103)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96)
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:267)
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160)
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:216)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:301)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:225)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:276)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.wso2.carbon.ui.filters.cache.ContentTypeBasedCachePreventionFilter.doFilter(ContentTypeBasedCachePreventionFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:126)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:666)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at com.shrss.wso2.ciam.core.valve.PortalManagerValve.invoke(PortalManagerValve.java:30)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:86)
    at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:102)
    at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:123)
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:99)
    at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49)
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:62)
    at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:145)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
    at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:57)
    at org.wso2.carbon.tomcat.ext.valves.RequestEncodingValve.invoke(RequestEncodingValve.java:49)
    at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:119)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
    at sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy54.executeQuery(Unknown Source)
    at org.wso2.carbon.user.core.jdbc.UniqueIDJDBCUserStoreManager.doGetUserListWithID(UniqueIDJDBCUserStoreManager.java:3256)
    ... 67 more

How to reproduce:

  1. Create a user in a secondary userstore(domain name=SECONDARY) where displayName claim is tharindu.
  2. Make the following request. 
    curl --location --request GET 'https://localhost:9443/scim2/Users?domain=SECONDARY&filter=displayName+eq+tharindu+and+displayName+eq+tharindu' \
--header 'accept: application/scim+json' \
  3. Responds with, 
    {
"schemas": [
    "urn:ietf:params:scim:api:messages:2.0:Error"
],
"detail": "Error in filtering users by multi attributes in domain: PATRON",
"status": "500"
}

Expected behavior: Filtering should be successful and the users should be returned.

Environment information (Please complete the following information; remove any unnecessary fields) :

somindatommy commented 3 years ago

Cannot be reproduced. Tried with the following sample.

curl --location --request GET 'https://localhost:9443/scim2/Users?filter=userName+sw+s+and+name.givenName+sw+a&attributes=userName' \
--header 'Authorization: Basic YWRtaW46YWRtaW4='
tharindu-b-hewage commented 3 years ago

@somindatommy Please use the same environment configurations as mentioned in the description and check. You will not get the stack trace but should get the erroneous API response. This is a syntax error with MsSQL query as I observed.

somindatommy commented 3 years ago

@tharindu-bandara seems like the issue is there when using ew and co operations. Also, it's working when the filter attributes are username.