Optional parameter "q" and 400 error (ORDS 18.3)

Olafur T

    Hi,

     

    Was testing the migration of some of our legacy services. DB version 11.2 and 12.1.02 all show the same 400 error.

     

    We used the parameter q as in "query string". Used in a lot of places, but ORDS will always give a 400 error.     

           Caused by: oracle.dbtools.json.JSONParserException: Expected one of: <<{,[>> but got: <<1>>   (where 1 is the first letter of the parameter)

     

    change it to q1= no problem, all of the other letters I tried did not produce the error. o,p,w,e,r etc..

     

    The test module is simple, call it with all kinds of parameters and it works fine. Use "q" and it results in a 400 error:

    BEGIN

      ORDS.DEFINE_MODULE(

          p_module_name    => 'Testing',

          p_base_path      => '/testing/',

          p_items_per_page =>  25,

          p_status         => 'PUBLISHED',

          p_comments       => NULL);     

      ORDS.DEFINE_TEMPLATE(

          p_module_name    => 'Testing',

          p_pattern        => 'date',

          p_priority       => 0,

          p_etag_type      => 'HASH',

          p_etag_query     => NULL,

          p_comments       => NULL);

      ORDS.DEFINE_HANDLER(

          p_module_name    => 'Testing',

          p_pattern        => 'date',

          p_method         => 'GET',

          p_source_type    => 'json/query;type=single',

          p_items_per_page =>  0,

          p_mimes_allowed  => '',

          p_comments       => NULL,

          p_source         =>

    'select sysdate from dual'

          );

      COMMIT;

    END;

     

    Thank you

    Olafur

      • 2. Re: Optional parameter "q" and 400 error (ORDS 18.3)
        Olafur T

        Hi,

        Nothing there about reserved words in optional parameters. I'll explain the problem better (I probably should raise an SR)

         

        Define a very simple GET service:

        begin
          ORDS.DEFINE_MODULE(p_module_name    => 'Testing Optional Parameters',
                             p_base_path      => '/testing/',
                             p_items_per_page => 25,
                             p_status         => 'PUBLISHED',
                             p_comments       => null);
        
          ORDS.DEFINE_TEMPLATE(p_module_name => 'Testing Optional Parameters',
                               p_pattern     => 'simple',
                               p_priority    => 0,
                               p_etag_type   => 'HASH',
                               p_etag_query  => null,
                               p_comments    => null);
        
          ORDS.DEFINE_HANDLER(p_module_name    => 'Testing Optional Parameters',
                              p_pattern        => 'simple',
                              p_method         => 'GET',
                              p_source_type    => 'json/item',
                              p_items_per_page => 25,
                              p_mimes_allowed  => '',
                              p_comments       => null,
                              p_source         => 'select sysdate from dual');
          commit;
        end;
        

         

        Testing with no parameters:

        curl http://localhost:8080/ords/ordstest/testing/simple

        {"sysdate":"2018-10-25T15:48:46Z","links":[{"rel":"collection","href":"http://localhost:8080/ords/ordstest/testing/"}]}

         

        Testing with some optional parameter:

        curl http://localhost:8080/ords/ordstest/testing/simple?p=123

        {"sysdate":"2018-10-25T15:48:46Z","links":[{"rel":"collection","href":"http://localhost:8080/ords/ordstest/testing/"}]}

         

        Testing with "q" as a parameter, no change to the service:

        curl http://localhost:8080/ords/ordstest/testing/simple?q=123

        results in 400 error

         

        BadRequestException [statusCode=400, reasons=[]]
        at oracle.dbtools.rest.resource.jdbc.JDBCResourcesProvider.queryStatement(JDBCResourcesProvider.java:316)
        at oracle.dbtools.rest.resource.jdbc.JDBCResourcesProvider.query(JDBCResourcesProvider.java:266)
        at oracle.dbtools.rest.resource.generator.json.JSONResourceGeneratorBase.service(JSONResourceGeneratorBase.java:66)
        at oracle.dbtools.rest.resource.generator.ResourceGeneratorsProvider.generate(ResourceGeneratorsProvider.java:99)
        at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:132)
        at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.access$100(ModulesDispatcherDelegate.java:26)
        at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate$GetDispatcher.doGet(ModulesDispatcherDelegate.java:174)
        at oracle.dbtools.http.etags.EntityTagsProvider.etag(EntityTagsProvider.java:114)
        at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:58)
        at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.service(JDBCResourceDispatcher.java:82)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
        at oracle.dbtools.http.servlet.DispatchableServletBase.service(DispatchableServletBase.java:58)
        at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.service(CachedResourceDispatcher.java:59)
        at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:143)
        at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:243)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73)
        at oracle.dbtools.http.forwarding.QueryFilteringRewrite.doFilter(QueryFilteringRewrite.java:90)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.forwarding.ForwardingFilter.doFilter(ForwardingFilter.java:68)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.cors.CORSPreflightFilter.doFilter(CORSPreflightFilter.java:66)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.cookies.auth.CookieSessionCSRFFilter.doFilter(CookieSessionCSRFFilter.java:71)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.auth.AuthenticationFilter.authenticate(AuthenticationFilter.java:101)
        at oracle.dbtools.http.auth.AuthenticationFilter.doFilter(AuthenticationFilter.java:64)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:161)
        at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:96)
        at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:127)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.rt.authentication.apex.ApexSessionQueryRewriteFilter.doFilter(ApexSessionQueryRewriteFilter.java:58)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:83)
        at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)
        at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:85)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:74)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.filters.Filters.filter(Filters.java:67)
        at oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:82)
        at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:102)
        at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
        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:52)
        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:198)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)
        Caused by: oracle.dbtools.query.filtering.impl.FilterSyntaxException: oracle.dbtools.json.JSONParserException: Expected one of: <<{,[>> but got: <<1>>
        at oracle.dbtools.query.filtering.impl.ClauseBuilderProvider.filterQuery(ClauseBuilderProvider.java:77)
        at oracle.dbtools.rest.resource.jdbc.JDBCResourcesProvider.queryStatement(JDBCResourcesProvider.java:290)
        ... 84 more
        Caused by: oracle.dbtools.json.JSONParserException: Expected one of: <<{,[>> but got: <<1>>
        at oracle.dbtools.json.JSONParser.error(JSONParser.java:38)
        at oracle.dbtools.json.JSONParserImpl.objectOrArray(JSONParserImpl.java:204)
        at oracle.dbtools.json.JSONParserImpl.next(JSONParserImpl.java:54)
        at oracle.dbtools.json.JSONReaderImpl.advance(JSONReaderImpl.java:41)
        at oracle.dbtools.json.JSONReaderImpl.advance(JSONReaderImpl.java:12)
        at oracle.dbtools.common.util.AbstractIterator._advance(AbstractIterator.java:140)
        at oracle.dbtools.common.util.AbstractIterator.start(AbstractIterator.java:153)
        at oracle.dbtools.common.util.AbstractIterator.hasNext(AbstractIterator.java:79)
        at oracle.dbtools.json.objects.JSONObjectsProvider.read(JSONObjectsProvider.java:111)
        at oracle.dbtools.json.objects.JSONObjectsProvider.read(JSONObjectsProvider.java:86)
        at oracle.dbtools.query.filtering.impl.ClauseBuilderProvider.filterQuery(ClauseBuilderProvider.java:63)
        ... 85 more
        
        • 3. Re: Optional parameter "q" and 400 error (ORDS 18.3)
          Pierre Yotti

          That is what you need here

          https://docs.oracle.com/cd/E56351_01/doc.30/e87809/developing-REST-applications.htm#GUID-091748F8-3D14-402B-9310-25E6A9116B47

           

           

          That is why you had that error "Caused by: oracle.dbtools.query.filtering.impl.FilterSyntaxException: oracle.dbtools.json.JSONParserException: Expected one of: <<{,[>> but got: <<1>>"

           

          - {,[ : expected a json object but get a value

          • 4. Re: Optional parameter "q" and 400 error (ORDS 18.3)
            Pierre Yotti

            3.2.5.2 Filtering in Queries

            This section describes and provides examples of filtering in queries against REST-enabled tables and views.

             

             

            Filtering is the process of limiting a collection resource by using a per-request dynamic filter definition across multiple page resources, where each page contains a subset of items found in the complete collection. Filtering enables efficient traversal of large collections.

             

             

            To filter in a query, include the parameter q=FilterObject, where FilterObject is a JSON object that represents the custom selection and sorting to be applied to the resource. For example, assume the following resource:

             

            https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.3/aelig/developing-REST-applications.html#GUID-0…

            https://docs.oracle.com/cd/E56351_01/doc.30/e87809/developing-REST-applications.htm#GUID-091748F8-3D14-402B-9310-25E6A91…

            https://stackoverflow.com/questions/50408450/how-to-use-clause-where-in-in-apex-ords-rest-api/50453786#50453786

            • 5. Re: Optional parameter "q" and 400 error (ORDS 18.3)
              Olafur T

              ah, thanks.

               

              That explains it. So "q" is really a reserved parameter even though it has no purpose in a defined service only for rest enabled objects like tables or views. (only using ORDS for user defined rest services not rest enabled objects)

               

              Ok, so there is no way for me to simply switch my old services with ORDS using the parameter "q", unless I do some fiddling with nginx rewrite rules.

               

              Best regards

              Olafur