7 Replies Latest reply on Sep 12, 2017 9:07 AM by John_K

    Preventing value injection into LOV?

    John_K

      I have an application with session state protection enabled, which contains a list of values item controlling the display of data on the report. I.e. my item (P1_ITEM) LOV contains values 'FOO','BAR' and then my report does "select * from my_table where val=:p1_item". I enabled session state protection so my users can't pass a value in for P1_ITEM via the URL - which works fine.

       

      My LOV contains only FOO and BAR and is set to not allow other values.

       

      However - if I open up the developer in any browser (i.e. Chrome), I can select the item, and modify the HTML of the option element to change the return value. This then displays the data that I don't want it to!

       

      Demo:

       

      https://apex.oracle.com/pls/apex/f?p=93339:1

       

      Now I know I can modify my report SQL and include the constraint in there (there are actually a few different options I can use to prevent it), and that it is considered best practice to secure data at the database level, however I can't help but feel that Apex should be stopping this - if I have created an item and I have said "don't allow other values" then surely it shouldn't? It might not SHOW the other values, however it allows the item to have that value in session state, despite me securing that item - I can't modify it via the URL but it is trivial to modify it in the HTML.

       

      Thoughts?

       

      [edit]

       

      Just to clarify, my expectation/hope for this would be that apex re-validates the value against the LOV query upon page submission when protection is enabled and the allow extra values isn't checked.

        • 1. Re: Preventing value injection into LOV?
          fac586

          ORA-01033 wrote:

           

          I have an application with session state protection enabled, which contains a list of values item controlling the display of data on the report. I.e. my item (P1_ITEM) LOV contains values 'FOO','BAR' and then my report does "select * from my_table where val=:p1_item". I enabled session state protection so my users can't pass a value in for P1_ITEM via the URL - which works fine.

           

          My LOV contains only FOO and BAR and is set to not allow other values.

           

          However - if I open up the developer in any browser (i.e. Chrome), I can select the item, and modify the HTML of the option element to change the return value. This then displays the data that I don't want it to!

           

          Demo:

           

          https://apex.oracle.com/pls/apex/f?p=93339:1

           

          Now I know I can modify my report SQL and include the constraint in there (there are actually a few different options I can use to prevent it), and that it is considered best practice to secure data at the database level, however I can't help but feel that Apex should be stopping this - if I have created an item and I have said "don't allow other values" then surely it shouldn't? It might not SHOW the other values, however it allows the item to have that value in session state, despite me securing that item - I can't modify it via the URL but it is trivial to modify it in the HTML.

           

          Thoughts?

          There's a long-standing feature request regarding this. Obviously most of the community haven't seen it, and 75% of those that have don't take the issue as seriously as we do.

           

          See About modify the values by F12.(eg:LOV) for a previous discussion of the problem with some suggestions as to how to handle it, and select list tampering for the closest to an official response seen from Oracle so far.

           

          However, it's no longer just something that has to be handled on page submission: values could be modified prior to AJAX requests used to update cascading LOV page items or refresh reports. It's therefore essential that protected data is always accessed using some form of fine-grained access control, whether that be RAS, OLS, VPD, or basic views.

          1 person found this helpful
          • 2. Re: Preventing value injection into LOV?
            John_K

            Thanks for the info. Interestingly, I see that on the feature request there is a comment stating that validations will resolve this - they will - only if you are trying to stop processing. In the example I posted, I have modified it to have a validation on. So when you modify one of the LOV options to say "BAZ" then you get the validation message. However because validations are executed after the item has been set to session state then simply pressing F5 to refresh the page yields the results.

             

            In validations are being suggested as an option then surely items shouldn't be saved to session state when validation fails on them.

             

            But ultimately yes, I agree - it seems one needs to implement all such restrictions at the query level (or with FGAC). If we are just dealing with page submit then I think a computation on the item on-submit will suffice, by setting the value to say null if it doesn't exist in the LOV query. In some ways I kind of like the approach of "assume the user can pass in any value they want for any item) - however I guess that kind of makes all the session state protection stuff redundant. It's nice when Apex helps you out a bit.

            • 3. Re: Preventing value injection into LOV?
              Martijn Zanders

              The feature request probably has not been implemented because clients are inherently unreliable. A web page is downloaded to your browser, and you can manipulate anything you want in your client. Therefore the place to ensure the integrity your data is in the database, and the best way to ensure the integrity of your data is by using constraints.

               

              In the case of a simple domain, for example Yes/No, use a check constraint. No way a different value can be inserted.

               

              For more complex domains, the domain values often have a logical meaning in an application and to its users. In this case, I would always recommend creating a separate domain table, with a foreign key to it in the original table. As you are now inserting a foreign key in the original table, the foreign key constraint ensures that only existing values can be used. Maintenance of the domain table should be restricted to privileged users that maintain the application, and an additional client side validation can be performed against the domain table if needed.

               

              Creating domain tables and maintenance screens for them takes some work, but it has its benefits. When a domain value has to be updated, only the value in the domain table has to be changed instead of updating all relevant rows in the original table (which end users may not be able to do). Also, the foreign key index always ensures good performance. And this approach helps to create an application for which the end uses can easily maintain the data via the front-end.

               

              This is the way an RDBMS is designed to be used: data integrity is enforced by constraints.

              • 4. Re: Preventing value injection into LOV?
                John_K

                Absolutely, I'm all for constraints and normalised data models. This example refers however to a case where the contents of an LOV are restricted and the user modified it to contain another VALID value.

                 

                I would expect the checks to be done server-side - the client would be irrelevant because no matter what you change the value to, the Apex engine would pick that up prio to setting session state.

                • 5. Re: Preventing value injection into LOV?
                  fac586

                  ORA-01033 wrote:

                   

                  Thanks for the info. Interestingly, I see that on the feature request there is a comment stating that validations will resolve this - they will - only if you are trying to stop processing. In the example I posted, I have modified it to have a validation on. So when you modify one of the LOV options to say "BAZ" then you get the validation message. However because validations are executed after the item has been set to session state then simply pressing F5 to refresh the page yields the results.

                   

                  In validations are being suggested as an option then surely items shouldn't be saved to session state when validation fails on them.

                  They're saved to session state before validation otherwise the submitted values wouldn't be available on the server. There may be post-submit branches and computations that are dependent on them prior to validation.

                  But ultimately yes, I agree - it seems one needs to implement all such restrictions at the query level (or with FGAC). If we are just dealing with page submit then I think a computation on the item on-submit will suffice, by setting the value to say null if it doesn't exist in the LOV query.

                  It depends on how you propose to respond to these attempts at manipulation. If someone is fiddling around with LOV values, then what else are they up to? Logging them out, locking their account, and emailing IT security seems like the safest option.

                  In some ways I kind of like the approach of "assume the user can pass in any value they want for any item)

                  That's a given.

                  however I guess that kind of makes all the session state protection stuff redundant. It's nice when Apex helps you out a bit.

                  Session state protection serves a different purpose. It's there to prevent manipulation of data that the user shouldn't be able to change.

                   

                  With LOV tampering the issue is that we expect the user to set the values, but only select them from the restricted dataset provided. The question is whether APEX can do anything to help with the two main problems involved:

                   

                  • Detecting the attempt to specify a value that is not included in the rendered options: parsing and evaluating complex dynamic LOV queries potentially involving multiple session state values.
                  • Performing any checks outside of a full page submit, e.g. when values are submitted in the course of updating cascading LOVs, region refreshes, and dynamic actions.
                  • 6. Re: Preventing value injection into LOV?
                    fac586

                    Martijn Zanders wrote:

                     

                    The feature request probably has not been implemented because clients are inherently unreliable. A web page is downloaded to your browser, and you can manipulate anything you want in your client. Therefore the place to ensure the integrity your data is in the database, and the best way to ensure the integrity of your data is by using constraints.

                     

                    In the case of a simple domain, for example Yes/No, use a check constraint. No way a different value can be inserted.

                     

                    For more complex domains, the domain values often have a logical meaning in an application and to its users. In this case, I would always recommend creating a separate domain table, with a foreign key to it in the original table. As you are now inserting a foreign key in the original table, the foreign key constraint ensures that only existing values can be used. Maintenance of the domain table should be restricted to privileged users that maintain the application, and an additional client side validation can be performed against the domain table if needed.

                     

                    Creating domain tables and maintenance screens for them takes some work, but it has its benefits. When a domain value has to be updated, only the value in the domain table has to be changed instead of updating all relevant rows in the original table (which end users may not be able to do). Also, the foreign key index always ensures good performance. And this approach helps to create an application for which the end uses can easily maintain the data via the front-end.

                     

                    This is the way an RDBMS is designed to be used: data integrity is enforced by constraints.

                    As described in the linked threads, we expect data integrity to be enforced in the database using constraints. However database constraints offer no protection where

                     

                    • The options available to the user are supposed to be limited to a restricted subset of those existing in the database domain defined by the FK constraint. The user can forcibly set any value that exists in the domain, not only those presented to them by the application.
                    • Use cases where the submitted value(s) are not involved in database DML operations, such as cascading LOVs or report parameters.
                    • 7. Re: Preventing value injection into LOV?
                      John_K

                      fac586 wrote:

                       

                      ORA-01033 wrote:

                       

                      Thanks for the info. Interestingly, I see that on the feature request there is a comment stating that validations will resolve this - they will - only if you are trying to stop processing. In the example I posted, I have modified it to have a validation on. So when you modify one of the LOV options to say "BAZ" then you get the validation message. However because validations are executed after the item has been set to session state then simply pressing F5 to refresh the page yields the results.

                       

                      In validations are being suggested as an option then surely items shouldn't be saved to session state when validation fails on them.

                      They're saved to session state before validation otherwise the submitted values wouldn't be available on the server. There may be post-submit branches and computations that are dependent on them prior to validation.

                       

                      I think it would be helpful if Apex was to rollback the session state at the end of processing though when validations have failed (i.e. so when the page is re-rendered the values are as they were prior to submission). Because otherwise we create a validation to say "these are the valid values for this item" but then go and set it anyway to one of those values regardless of the error message being shown.