Monday, 6 March 2023

Let's talk Oracle APEX Security - APEX SERT

APEX SERT installation notes

How to start using APEX SERT?

This time we will take a look at how to get APEX SERT installed and running. 

In its essence it should only come own to one script and you are ready to start using it.

If you downloaded installation scripts regardless of what version of APEX you are on you have be be aware that these versions must match each other. 

Meaning if you are running APEX 21.2 then you should install APEX SERT 21.2 and not any other. Why is this we will come to it later on? Point is that SERT simply will not run. Of course there is a way to tweak things and make it work but I am guessing we do not want lose time and want to get things going. 

Lets look at installation and how easiest to do this?

Download matching SERT version and unzip it somewhere. Find and open ins_auto_setup.sql 

This is the script I would recommend you to use, the only difference from ins.sql is that it will not prompt you anything so you can run it as one click install. At least that was the idea. 

In here you will notice this section

which is trying to explain all parameters we can provide as an input. Most are self explanatory.

There is a section with a basic example how you could call this script 

If you closer it will require of you to provide a sysadmin password, why is this? 

It will as part of the installation try to create users with a special access to some of APEX internal tables. In order to do this it needs to have admin privilege otherwise it would not work. Is this the best way to go or not is now out of scope for this blog post. ;)

Last section we need to be aware of is this one


As you can see here there are references to APEX versions etc.... 

Okay now you should be ready to install APEX SERT. If you execute the script and it all works for you should get few new schemas installed. 


Looking at them in top to bottom order.... 

  • SV_SERT_XXXXXX is a schema is the brain and all what APEX SERT does. All configs and everything is stored here in its tables
  • SV_SERT_APEX is a layer that has read access to SV_SERT_XXXXXX schema
  • SV_SERT_LAUNCHER is a special schema that holds up a REST API call which will actually start your APEX SERT application

With this quick fly-through of how installation looks like we can start scanning our apps for any security wholes. 


We will look at the first APEX SERT run in the next post.

Happy APEXing,

SLino

Thursday, 2 March 2023

Oracle APEX Security - APEX SERT for APEX 19.1, APEX 19.2, APEX 20.2, APEX 21.1, APEX 21.2, APEX 22.1, APEX 22.2

 

ORACLE APEX Security scanning - APEX SERT upgraded

Free and easy security scanning tool for your APEX apps

I am hoping by this time we are all on the same page - we should start putting more efforts as developers into securing our apps. 

From first days of APEX Advisor and Patrick's work on it till today I always had a sense that with all these new cool latest features I wished there was a bit more flavor added to security scanning especially in page designer area where we developers live. 

Please do not read this wrong - we all know out of the box APEX is safe as low code framework. We developers with lack of knowledge and awareness for security are making our apps exposed to different exploitation like SQL injection, XSS scripting etc. 

APEX team is doing an amazing job improving security within APEX to latest standards with every release, we all get and appreciate that, but my point here is more looking at the perspective of have a free built-in ability to security scan your applications as we are developing our apps without the need of a third party vendors. 

I am talking here about the tool that can be incorporated into every day development life cycles. No need to criticize a great job that has been done in APEX for past few years now.

For example I do my changes which I am happy with, I click a button similar to what I do when we let's say create a new PL/SQL process, we then let APEX check and warn us about any code not compiling or any errors found. So we have an immediate feedback that things need to be addressed and we all love this.  

APEX Advisor is a great tool but across the community it is simply not used. I am not going into why's but this is from my experience. And with security the more layers we have the better.  

Going back to the point...


It has been some time in the making but finally it is ready to be shared with you all. 

If you ever wanted to find that tool to help you make your APEX apps more secure here comes the good news. 

You may have came across a super handy free to use tool called APEX SERT hiding under https://github.com/OraOpenSource/apex-sert link. Not sure if you ever used it or heard of it but that does not matter at this stage.

About history of the project in short - it was a team effort lead by Scott Spendolini originally that has done a great job getting us to APEX 5 version. 

Unfortunately last commits on the project happened more than 5 years ago which can happen with open source project like this. 

To me it only means the contributors behind the idea have been super busy :D.  

Could we build on it and make it work again so it could then be used with the latest version of APEX? All fair questions especially if you knew that APEX SERT at the end is one big APEX app.

From time to time I have heard people talking that Oracle APEX team is using SERT as their tool of choice in some of the development work that is taking place at Oracle. Which made many of us thinking that original tool is far from being a dead as a product and far from just being left behind. That is by all means a good news for us all but why it has never ended being updated/uploaded back into original Git repo?

Looking closely somewhere down the comments etc you will find a version someone adjusted for APEX 18 but still you may experience some gotchas when try using it. 

If you ever used it and explored it, there is tons of things you can learn about APEX in general and security of your apps so I highly encourage you to give it a try. It is just another APEX app in its core. 

Idea here is not to take away but to actually share/contribute to this whole APEX  security community space. All credits got to the community and team effort that has supported APEX SERT from day one.

With that said here are all available APEX SERT versions:

These versions are still not committed or shared under original APEX SERT project simply due to lack of time. I was never sure how best to do this. :) 

Never say never so it may happen in months to follow depending on your feedback.

I would appreciate if you could provide feedback in case when you used one of the versions, especially if there are any bugs you found down the line.

As with all things, I encourage you to read original documentation to get the sense of what it is and how it works, then simply check ins_auto_setup.sql script which should basically do the installation in 'one click' and you should be ready to go.

Things have been optimized, simplified updated for each APEX release which should make it simple enough. 

My only 'ask' is please think of using it (if not APEX SERT then any other security tool on the market is equally as good) and do not hesitate to provide any feedback - good or bad. 



Happy APEXing,

SLino

 

P.S. For all version of Oracle APEX 18+ you can find APEX SERT on https://github.com/lschilde APEX 23.1, APEX 23.2, APEX 24.1

Friday, 30 September 2022

APEX 22 UT auto dismiss a success message revisited

 

APEX auto dismiss a success message revisited

APEX 21.x and 22.x auto success message handling

As it sometimes happens we have to revisit and update posts we made in a past. This is one of these ones. 

We all have wanted to auto hide our success messages and back in 2017 I wrote a post to re-conciliate how one would do this for APEX 5.1 and later. Since some things have changed and some JS API got deprecated here is a take two. 

This should take care of auto hiding of Success message in APEX 21 and 22 versions probably APEX 20.x version also. Since I did not have access to APEX 20.x version just bare it in mind it is a theory. 

We are starting with the usual  

 

Perfect, we got the success message. 

There are two sub scenarios how we can get it shown. First is when page submit with PLSQL process success message happens and second one is when DA success message triggers. 

Of course that we would remember we have seen this before and we've done it hundred times before so I would open apex.oracle.com/UT scrolled down to JS APIs and find this code 

apex.jQuery(function() {
  apex.theme42.util.configAPEXMsgs({
    autoDismiss: true,
    duration: 5000  // duration is optional (Default is 3000 milliseconds)
  });
});

If you copy it over into your On Page Load DA it would do the trick. Great, it works in every situation. Our job is done!!! 

Well not really, sometimes you may want to be little bit more sophisticated and you can opt for an option with a nice fading effect. Please correct me here but there is currently no option to specify fading effect here in apex.theme42.util.configAPEXMsgs. It would be such a nice add-on to it, wouldn't it. :)

How would we do it then? Again we rush to apex.oracle.com/jsapi and here we would find another interesting code that gives us more options. 

apex.message.setThemeHooks({
    beforeShow: function( pMsgType, pElement$ ){
        if ( pMsgType === apex.message.TYPE.SUCCESS ) {
            pElement$.addClass( "animate-msg" );
        }
    },
    beforeHide: function( pMsgType, pElement$ ){
        if ( pMsgType === apex.message.TYPE.SUCCESS ) {
            pElement$.removeClass( "animate-msg" );
        }
    }
});

With a little fine tuning we would get to

apex.message.setThemeHooks({
    beforeShow: function( pMsgType, pElement$ ){
        if ( pMsgType === apex.message.TYPE.SUCCESS ) {
           setTimeout(function() { 
$('.t-Alert').fadeOut('slow');
}, 3000);
} } });

Let's try this out and see what happens. Soon we would pick that things are still not quite there yet. 

If we submit a page and process emits a success error this would not cater for it. It almost seems a timing issue in APEX, like the success message comes before this event registers it. 

What on the other hand is working fine it does seem to catch any apex.message.showPageSuccess( "Changes saved!" ); messages raised in my app.

To sort page submit success message problem on my page I used this

$(".t-Alert .t-Alert--success").ready(function(){
console.log('OnSubmitSuccess Handler to execute');
setTimeout(function() {
$('.t-Alert').fadeOut('slow');
}, 3000);
});

Not sure if this is the most elegant way to go but it works. Now in combination of the two calls they handle situations correctly. I would call it too many lines of code if you ask me but it is what it is.

To conclude, I wish APEX JS APIs in the future gives use out of the box ability to specify fading option in apex.theme42.util.configAPEXMsgs - fewer lines of code and easier to use. Thank you.

JS Code is here - https://codeshare.io/9Onz8K.

Happy APEXing,

SLino