12 Replies Latest reply: Jul 2, 2013 8:48 AM by Luis RSS

    Validation function, PL/SQL, bind parameters, substitution strings

    Luis
      Hello there,

      As I discussed in this thread, Validation function, PL/SQL is not being executed I am able to execute my PL/SQL validation function. But it seems that the list of parameters that I can pass to this function is limited to *:URL*. Would it be possible to use any other substitution string (http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/concept_sub.htm#autoId2) like :APP_ID or :APP_PAGE_ID?

      Thanks in advance,

      Luis
        • 1. Re: Validation function, PL/SQL, bind parameters, substitution strings
          riedelme
          Luis wrote:
          As I discussed in this thread, Validation function, PL/SQL is not being executed I am able to execute my PL/SQL validation function. But it seems that the list of parameters that I can pass to this function is limited to *:URL*. Would it be possible to use any other substitution string (http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/concept_sub.htm#autoId2) like :APP_ID or :APP_PAGE_ID?
          This forum is for Application Express Listener questions - your question is a general Apex function. You should get a better answer if you close this thread and re-open it in the Apex forum Oracle Application Express (APEX)
          • 2. Re: Validation function, PL/SQL, bind parameters, substitution strings
            Kris Rice-Oracle
            Any chance you can shoot me your config and I can take a look?
            -kris
            • 3. Re: Validation function, PL/SQL, bind parameters, substitution strings
              Luis
              Hello riedelme,

              mmm, IMHO is an Application Express Listener question. I am talking about the...
              <entry key="security.requestValidationFunction">
              ... property of the apex_listener.2.0.0.268.17.04

              Cheers,

              Luis
              • 4. Re: Validation function, PL/SQL, bind parameters, substitution strings
                Luis
                Hello Kris,

                Thank you very much for answering, sure you can:

                defaults.xml:
                <?xml version="1.0" encoding="UTF-8"?>
                <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
                <properties>
                
                <comment>
                   Listener Administration
                   Oracle SQL Developer 3.2.20.09.87
                   Last Update: Fri May 10 17:32:49 CEST 2013
                </comment>
                
                <entry key="db.username">APEX_PUBLIC_USER</entry>
                <entry key="db.password">xxx</entry>
                <entry key="db.connectionType">tns</entry>
                <entry key="db.hostname">trainingdb.my.domain</entry>
                <entry key="db.port">10121</entry>
                <entry key="db.sid">TRAINING</entry>
                <entry key="db.servicename"/>
                <entry key="db.tnsAliasName">trainingdb</entry>
                <entry key="db.tnsDirectory">/home/lurodrig/developing/oracle</entry>
                <entry key="db.customURL"/>
                
                <entry key="jdbc.DriverType">thin</entry>
                <entry key="jdbc.InitialLimit">3</entry>
                <entry key="jdbc.MinLimit">1</entry>
                <entry key="jdbc.MaxLimit">10</entry>
                <entry key="jdbc.MaxStatementsLimit">10</entry>
                <entry key="jdbc.InactivityTimeout">1800</entry>
                <entry key="jdbc.statementTimeout">900</entry>
                
                <entry key="security.inclusionList"/>
                <entry key="security.exclusionList"/>
                <entry key="security.disableDefaultExclusionList">false</entry>
                <entry key="security.validationFunctionType">plsql</entry>
                <entry key="security.requestValidationFunction">APEX_040200.is_allowed(app_id=>:APP_ID)</entry>
                <entry key="security.maxEntries">100</entry>
                <entry key="security.verifySSL">false</entry>
                
                <entry key="icap.server"/>
                <entry key="icap.port"/>
                
                <entry key="cache.caching">false</entry>
                <entry key="cache.procedureNameList"/>
                <entry key="cache.type">lru</entry>
                <entry key="cache.maxEntries">25</entry>
                <entry key="cache.expiration">7</entry>
                <entry key="cache.duration">days</entry>
                <entry key="cache.directory"/>
                
                <entry key="procedure.preProcess"/>
                <entry key="procedure.postProcess"/>
                
                <entry key="misc.defaultPage">apex</entry>
                <entry key="apex.docTable">FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$</entry>
                <entry key="apex.extensions"/>
                <entry key="log.procedure">false</entry>
                <entry key="debug.debugger">false</entry>
                <entry key="debug.printDebugToScreen">false</entry>
                
                <entry key="apex.excel2collection">false</entry>
                <entry key="apex.excel2collection.onecollection">true</entry>
                <entry key="apex.excel2collection.useSheetName">false</entry>
                <entry key="apex.excel2collection.name"/>
                
                <entry key="sqldev.name">trainingdb</entry>
                <entry key="sqldev.description"/>
                <entry key="sqldev.version">3.2.20.09.87</entry>
                
                </properties>
                apex.xml:
                <?xml version="1.0" encoding="UTF-8"?>
                <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
                <properties>
                
                <comment>
                   Listener Administration
                   Oracle SQL Developer 3.2.20.09.87
                   Last Update: Fri May 10 17:32:49 CEST 2013
                </comment>
                
                <entry key="db.hostname"/>
                <entry key="db.port">1521</entry>
                <entry key="db.sid">orcl</entry>
                
                <entry key="security.requestValidationFunction"/>
                
                <entry key="sqldev.name">apex</entry>
                <entry key="sqldev.description"/>
                
                </properties>
                This configuration is generated via sqldeveloper 3.2.20.09...

                The :APP_ID substitution string was my last try, did not work, sigh...

                Thanks in advance,

                Luis
                • 5. Re: Validation function, PL/SQL, bind parameters, substitution strings
                  Kris Rice-Oracle
                  3 followup questions:


                  1) Is APEX_040200.is_allowed executable by apex_public_user? Also, you shouldn't be installing objects into the apex schema
                  2) Is there anything in the log file?
                  2.1) If nothing is in the log file, put it into debug and check the log file again.


                  -kris
                  • 6. Re: Validation function, PL/SQL, bind parameters, substitution strings
                    Luis
                    Hello Kris,

                    Thanks for the followup. Answers:

                    1) Yes it is, and yes I know that I should not (most probably my DBA will kill me!), it was for testing purposes...
                    2) Tomcat: no it is not. When the apex context is deployed it shows its configuration, but nothing else. Oracle DB: I have no access, I will talk with the DBA.
                    3) org.apache.catalina.level=FINEST The same as above

                    What I can see is that the function it is being executed. I have:
                    create or replace 
                    function is_allowed(url in varchar2)
                        return boolean as
                        what VARCHAR2 (2048);
                    begin
                        what:= 'url: ' || url;
                        INSERT INTO LOG VALUES
                          (sysdate, what
                          );
                       return true;
                    end;
                    In the LOG table:

                    29-MAY-13     url: http://localhost:8080/apex/f?p=141:8
                    29-MAY-13     url: http://localhost:8080/apex/f?p=141:8
                    29-MAY-13     url: http://localhost:8080/apex/f?p=141:8
                    29-MAY-13     url: http://localhost:8080/apex/

                    Good! But if I try with any other parameter, like flow_id=>:P_FLOW_ID, I get a blank value.

                    Thanks in advance,

                    Luis
                    • 7. Re: Validation function, PL/SQL, bind parameters, substitution strings
                      Kris Rice-Oracle
                      You'll have to turn the debugging on in the listener also. In the defaults.xml there's a debug parameter, flip that to true and let me know what's in the logs.

                      -kris
                      • 8. Re: Validation function, PL/SQL, bind parameters, substitution strings
                        Luis
                        Hello Kris,

                        Ok, great, I am starting to see the light. I have configured the log (standalone mode), following this post: http://cdivilly.wordpress.com/2013/03/08/configuring-logging-in-oracle-application-express-listener-2-0-1/

                        In the standard output I can see that the value of P_FLOW_ID is always null:
                        ++Adding:p=4550:1:0:::::
                        
                        ==== doGet()==== 
                        
                        
                         isValidRequest(), procedure name: <f>
                        
                        Validating:f
                        
                        *** Total number of arguments: 15Security Bind: URLSecurity Bind: PROCNAMESecurity Bind: P_FLOW_ID--->null
                        
                        Security Bind: P_FLOW_STEP_IDSecurity Bind: P_INSTANCESecurity Bind: P_PAGE_SUBMISSION_IDSecurity Bind: P_REQUEST
                         ** checkRequestValidationFunction(), <f> exclude=false
                        
                        SID:25Parse: 18 ms
                        -----
                        begin 
                         f(p=>?);
                        commit;
                          end;
                        
                        *** Total number of arguments: 15p=4550:1:0:::::
                        
                        Exec: 64 ms
                        Got results length:10228
                        Any idea?

                        Thanks again,

                        Luis

                        Edited by: Luis on May 31, 2013 5:31 PM
                        • 9. Re: Validation function, PL/SQL, bind parameters, substitution strings
                          Luis

                          Hello Kris,

                           

                          It seems that the only two possible binds for the parameters of the validation function are PROCNAME and URL. It is weird because if I set a wrong parameter like PROCNAM, I get the following exception in the logs...

                           

                          java.sql.SQLException: The number of parameter names does not match the number of registered praremeters
                          

                           

                          ... and the next message:

                           

                          Security Bind: PROCNAMESecurity Bind: P_FLOW_IDSecurity Bind: P_FLOW_STEP_IDSecurity Bind: P_INSTANCESecurity Bind: P_PAGE_SUBMISSION_IDSecurity Bind: P_REQUESTJun 21, 2013 3:51:24 PM oracle.ucp.jdbc.proxy.CallableStatementProxyFactory invoke
                          

                           

                          So, in theory, the P_FLOW_ID and P_FLOW_STEP_ID should work.

                           

                          Any idea?

                           

                          Thanks in advance,

                           

                          Luis

                          • 10. Re: Validation function, PL/SQL, bind parameters, substitution strings
                            Luis

                            Hello again,

                             

                            Could this issue be caused by a Javascript problem? I have realized (decompilating a bit and taking a look at the Javascript) that and at the end of the day, the listener has to translate from f?p= syntax (f?p=4550:1:4595084190080) to "standar url syntax" (...p1=v1&p2=v2=...&pn=vn)...

                             

                            Thanks in advance,

                             

                            Luis

                            • 11. Re: Validation function, PL/SQL, bind parameters, substitution strings
                              Kris Rice-Oracle

                              I'm reading the code trying to see how this is happening.  Do you see the same on both POSTs and GETs?

                               

                              When something has a value you should see:

                              Security Bind: P_FLOW_STEP_ID ---> <VALUE>

                              • 12. Re: Validation function, PL/SQL, bind parameters, substitution strings
                                Luis

                                Hello Kris,

                                 

                                Thanks for your answer. Yes I do:

                                 

                                • GET /apex/f?p=4550:1

                                 

                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: URL
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: PROCNAME
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_FLOW_ID
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: ---> null
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: 
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_FLOW_STEP_ID
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: ---> null
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: 
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_INSTANCE
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_PAGE_SUBMISSION_ID
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_REQUEST
                                Jul 02, 2013 3:27:46 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: ** checkRequestValidationFunction(), <f> exclude=false
                                

                                 

                                • POST /apex/f?p=4550:1

                                 

                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: URL
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: PROCNAME
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_FLOW_ID
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: ---> null
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: 
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_FLOW_STEP_ID
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: ---> null
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: 
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_INSTANCE
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_PAGE_SUBMISSION_ID
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: Security Bind: P_REQUEST
                                Jul 02, 2013 3:29:23 PM oracle.dbtools.apex.security.Security checkRequestValidationFunction
                                FINE: ** checkRequestValidationFunction(), <f> exclude=false
                                

                                 

                                Thanks,

                                 

                                Luis