Oracle APEX Blog

Disabling Interactive Grid Columns

Written by Matt Mulvaney | May 14, 2018 9:06:49 AM
Find out how we can help you build solutions using Oracle APEX with our award-winning application development and consultancy services.

I’ve seen various information about how to disable IG columns, either through read-only attributes, using special classes and other nifty tricks. But thinking simply about this … a column should either be conditionally removed at page load or dynamically controlled – and what should we use for dynamic controls? Dynamic Actions.

There’s an example on Page 58 of the Sample Interactive Grids application of enabling/disabling an IG column through a dynamic action called Job = Salesman.


So what’s happening is, if the IG employee record is a Salesman it enables the Commission column. Otherwise, it disables the Commission column and sets it to Null.

It does this on a change of Job; however, the true/false actions are set to run on initialisation. So for an IG, this means it is enabling/disabling the Commission column on record initialisation; i.e. as the user clicks through the records in edit mode, e.g. click on a salesman, commission is enabled. Click on a Clerk, commission is disabled and nulled.

This is a great approach as the Job column is editable and likely to change. If it wasn’t possible to change this column, I might consider a Row Initialisation [Interactive Grid] event instead of the change event – however, the approach used on this page is great.

So you might think – pretty standard stuff; non-salesmen should not have a commission, therefore, remove any value they may have. However, let’s say that my users insist the commission should never be null and must always have a value. In our example, we’ll use 0 (zero). So let’s do two things:

  1. Change the Set Value action to set the column to 0 (zero) instead of null
  2. Update my database table, so all employees have a commission set (zero or otherwise).
update EBA_DEMO_IG_EMP
 set comm = 0
 where comm is NULL;

Now when I load the application and start editing, the Commission column is now zero and disabled for Non-Salesmen and everything looks like I expect it to…

… Until I make some modifications.

What happened was I changed the Manager of the first three records (this issue only occurs when updating 2 or more records) and, depending on which record the user was last editing, up to 3 records lose their Commission amount. I was expecting these to be set to 0 instead. I closely monitored the IG model and it was a 0 all the way until the save, where the debug reported an absence of the Commission amount.

We never experienced this problem in the Packaged Application before we started modifying it because the original Set Value action was masking the issue. The disable dynamic action is disabling the field and it’s the submission of this disabled field which is resulting in a null Commission amount. So it’s not really the Set Value action which is doing the work; this is more for aesthetic value, i.e. so that the user believes the commission amount is being removed because they can see it on screen before the save button is pressed.

So we have established the nature of the Disable Dynamic Action (this behaviour exists in APEX 5.1 / 18.1); which is a shame because it would’ve been nice to prevent user updates to the field whilst retaining the column value in session.

Now we understand the behaviour; we can now start to think about solutions. My “go to” solution is to apply the is-readonly class instead of disabling the field, but this is not working in this instance.

Whilst observing the enable/disable functionality I found I could apply the apex_disabled class instead and this appears to do the trick … partially. It does grey out the item and it does retain the value in session when the IG is saved. But in some cases text entry is still allowed.

The foundation of this workaround is to replace my disable actions with add class actions and replace my enable actions with remove class actions, and then pair the addition/removal of this class with additional styling and/or attributes to prevent the text entry noted above in some cases.

However, before I jump into the detail, I’m thinking that whatever I suggest shouldn’t be any less secure than what is currently available.

In theory, a hacker could interact with the IG API via the console tools and force the commission amount to be editable in an attempt to add commissions to non-salesmen. In practice, and within a short amount of time, I managed to hack page 58 of the Sample Interactive Grid Packaged application on APEX 18.1 where I could give a non-salesman a commission of 10,000 and have this saved to the database. This exploit was achieved via the console alone.

As you can see from the picture, this has been saved to the database. Securing the IG takes us back to APEX security basics where all client side validations (in this case its applied business logic) have to have an equivalent server-side validation and… surprise! Page 58 doesn’t have a server side validation. Therefore page 58 is insecure and would fail security testing.

When I add a server side condition (to resolve this vulnerability) & run my exploit I cannot pass values to the DB and now my page is secure.

I am not, by any means, saying the IG is insecure. Nor am I saying APEX is insecure – both of which are secure. However, part and parcel of any web development, including APEX development should be coding defensively – including (but not limited to) doubling up client side validations with server-side conditions. APEX security is a broad subject and Explorer have blogged about this previously.

So now we know how we can secure our IG; we can focus on completing the workaround…

I’m going to replace the disable action with the following JS:

// Make the field readonly
$("#COMM").attr('readonly','readonly');
// Show the column as greyed-out
$("#COMM").addClass('apex_disabled');

And then I’m going to replace the enable with the following JS:

$("#COMM").removeAttr('readonly');
$("#COMM").removeClass('apex_disabled');

Of course, I need to give my COMM column a static ID of COMM.

I guess a plugin could be made, a JS function created, etc.

Before I finish up, I have to ensure my server-side validation is in place to ensure that any would-be hackers cannot wreck my table.

A quick test of this and it’s looking good.

Whenever editable IGs are involved, I try to be extra careful. They are not insecure, there’s just more to consider, some of many questions I ask myself are:

  • Assume any value can be changed. Do I have sufficient server-side validations?
  • Does my “Interactive Grid – Automatic Row Processing” process have the right authentication and conditions set?
  • A grid can still be saved even though the save button has not been clicked – i.e. through a page submit (even though there are no submit buttons on the page). Have I catered for this?
  • What IG actions are potentially available and am I happy with those?

If you would like to find out more information speak to one of our Oracle APEX experts, get in touch through enquiries@dsp.co.uk or book a meeting...

 

Author: Matt Mulvaney

Job Title: Senior Oracle APEX Development Consultant

Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.