Thursday, April 9, 2015

APEX 5: Deprecated and Desupported Features and Changed Behavior

I've started to read APEX 5 BETA documentation (Beta Draft: 2015-01-16) Release Notes and I've found out two interesting chapters -  Deprecated and Desupported Features and Changed Behavior.

In chapter named Deprecated and Desupported Features you can find many interesting things about APEX 5 and what should you avoid to use in your future APEX 5 applications. 

Here are some things that caught my eye more than others:
  • Deprecated column types in Classic Report. Oracle recommends to use Tabular Forms or APEX_ITEM API for creating editable reports.
  • File Browse Item should use APEX_APPLICATION_TEMP_FILES table for select, update or delete operations instead of WWV_FLOW_FILES. Deletes are no longer necessary, as the file will automatically be purged after the request or when the session is purged.
  • If you used WWV_FLOW_FILES as a permanent store, the files should be copied into their own BLOB column(s) within a table within your schema.
  • In APEX 5.0 there is only one button type which can be positioned in a region or next to items. Finally, no more Item Buttons! :)
  • Dynamic actions based on DOM Objects have been deprecated. You should use jQuery Selector or JavaScript Expression instead.
  • Page computations and validations based on SQL expressions have been deprecated. You should use PL/SQL expressions or PL/SQL Function returning VARCHAR2 types
  • #PLUGIN_PREFIX# substitution string is not supported any more in plug-in files. You should use relative URLs instead.


Also, there's a nice chapter about Changed Behavior. Here are some nice bullets to remember:
  • Static files will be stored in central repository that is available for a specific application (Static Application Files) or for all applications within a workspace (Workspace Application Files)
  • Static application files uploaded to Shared Components are automatically included in an application export. No more recreating supporting object scripts. :)
  • In a future version of Oracle Application Express, the dictionary view APEX_WORKSPACE_FILES will no longer include CSS, image or static files uploaded in Shared Components. You should use the new views APEX_WORKSPACE_STATIC_FILES and APEX_APPLICATION_STATIC_FILES instead.
  • Because of internal changes to the views APEX_APPLICATION_FILES and WWV_FLOW_FILES, it is not possible anymore to lock rows with SELECT FOR UPDATE

Please check the whole documentation for details. Remember, for now this is only BETA documentation. 
Don't forget to check those chapters in full documentation before moving to APEX 5.


Saturday, April 4, 2015

APEX 5: jQuery Migration

If you like to write custom JS code in your APEX applications or to use lots of 3rd party plugins you should be aware of changes in jQuery and JS libraries in APEX 5.


APEX 5 (at least current version 5.0.0.00.28) uses jQuery 2.1.3 and if you plan to migrate from APEX 4.2 you should check for changes from jQuery version 1.7.1 which is included in APEX 4.2. There are some things that are deprecated and removed like .die() or .live() event handlers.

Here you can find things that are removed:

But APEX development team was one step ahead and they provided us with quick solution to include jQuery Migrate plugin that restores deprecated features and behaviors of jQuery.

You can turn it on under the User Interface Attributes Page, JavaScript tab. My advice is that you should clean up your code and not to use any deprecated code.

Note that, as you can read in jQuery documentation, jQuery 2.x has the same API as jQuery 1.x, but does not support Internet Explorer 6, 7, or 8. APEX development team also thought of that and they put conditional comment to handle this:

<!--[if lt IE 9]><script type="text/javascript" src="/i/libraries/jquery/1.11.2/jquery-1.11.2.min.js?v=5.0.0.00.28"></script><![endif]-->
<!--[if gte IE 9]><!--><script type="text/javascript" src="/i/libraries/jquery/2.1.3/jquery-2.1.3.min.js?v=5.0.0.00.28"></script><!--<![endif]-->

Also be aware that by default when you create new application in APEX 5 there is no more old non-namespaced APEX JavaScript functions in your application (as part of legacy.js). You can include them by turning on property Include Legacy Javascript in User Interface Attributes page, JavaScript tab.

Again, don't do that. Rather clean up your code!



Sunday, January 25, 2015

On Demand Process Bug

In my last post I wrote about security of on demand application processes. While I was testing this I've noticed a bug in APEX version 4.2.6.00.03 and APEX 5 EA2 (probably in some older versions too).

If you have on demand application process (without authorization scheme) and call it from public page (e.g. login page 101) you'll get additional unwanted data in response message.

For example, if you create public on demand application process like this that outputs some string like"OK":

and call it in some on load dynamic action on public page (like login page):

When you take a look at console window you'll see additional data with "OK" string:

If you are expecting that your message is returning something in JSON format you'll get an JS error on page.

I've reported a bug to APEX team and they've fix it for version 5.

Friday, January 23, 2015

Secure your On Demand processes

By default when you create On Demand application process there is no authorization scheme selected:


and that makes your On Demand processes very vulnerable and publicly available.

For example, if you create On Demand application process without authorization scheme named DUMMY that creates record in DEPT table:


it can easily be called from browser console window without being logged in into application (e.g. on login page 101) by executing this JS statement:
apex.server.process ("DUMMY" 
                    ,{x01: "IT",x02: "LOS ANGELES"}
                    ,{dataType:"text"}
                    );

Table before executed JS statement:
DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

Table after executed JS statement:
DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
50ITLOS ANGELES

If you change authorization scheme of your On Demand application process to Must Not Be Public User:

it will not be publicly available and unauthorized users will not be able to call it without logging in into application.

Environment: APEX 4.2.5.00.08 and Oracle XE 11.2.0.2.0 database.

Thursday, September 11, 2014

Remove Annoying Login Message

If you are annoyed by login message "...Please wait 5 seconds to login again..." that apears in APEX 4.2.* after failed login attempt you're lucky for reading this post. There is an easy way to switch it off.

Login to APEX Builder like instance admin and go to Manage Instance > Security option. Find General Login Control region and set property Delay after failed login attempts in Seconds to 0.




Tested with APEX 4.2.5.00.08 and Oracle XE 11.2.0.2.0 database.

Sunday, March 9, 2014

View Item Session State from PL/SQL IDE

One and a half year ago I had a post about viewing data from APEX collections from your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...). Similar to that you can view current item session state. First step should be getting grants from some internal APEX packages (executing as sys user):

grant execute on apex_040200.wwv_flow_security to test;
grant execute on apex_040200.wwv_flow_session_state to test;

After that you have to register application and session ID in your PL/SQL IDE (running in parsing schema, e.g. test):

begin
   -- Set Application ID       
   apex_040200.wwv_flow_security.g_flow_id := 121;
      
   -- Set Session ID  
   apex_040200.wwv_flow_security.g_instance := 802090201361;   
end; 

Any time you want to see current session state from some item you have to refresh current session state values by executing:

begin   
   -- Refresh Item Session State Values
   apex_040200.wwv_flow_session_state.fetch_into_substitution_cache(true);
end;

After that you can see value from any item in current session using function v or apex_util.get_session_state, for example:

select v('P1_TEST') from dual

Tested with APEX 4.2.4.00.08 and Oracle XE 11.2.0.2.0 database.

NOTE: It wouldn't be a good idea to give these grants on production instances. :)


Sunday, November 10, 2013

APEX Security Holes - Page Items to Submit Property (Part 1)

I've discovered some interesting things about APEX checksum and item session state. I'll show you some tricks (security holes) and how to avoid them.

Suppose you are authorized to see only departments 10 and 30. Your query should look like this:


  SELECT deptno, dname 
    FROM dept 
   WHERE deptno in (10,30)


and the report page:

where link "View Employees" points to page where you can see all employees in selected department. SQL on that page should look like this:


  SELECT ename    as "Name"
       , job      as "Job"
       , hiredate as "Hire Date"
       , sal      as "Salary"
       , deptno
   FROM emp
  WHERE deptno = :P2_DEPTNO


On this page checksum is turned on so you can't manipulate with URL and change department number:


The link that points to second page should look like this:


  f?p=150:2:6476219590424::NO:2:P2_DEPTNO:30&cs=3DBC2DC62D86556DA878EBB2420F038C0


But if you accidently put item that is used for filtering employees (in my case P2_DEPTNO) to "Page Items To Submit" property of report region there is an easy way to change it and to see employees that you are not supposed to see.


If you clicked on Accounting department on first page you should see 3 employees:





















After that, you just have to open your favorite web debug tool, in my case FireBug, and run following code (where R12587121803287243 is ID of your report region) that sets value of item P2_DEPTNO to 20 and refreshes report region:
















Voilà! Now you should see employees in department 20:
























The refresh of region can be done in other ways. For example, by clicking on pagination.
To see if report region have some items to submit in report properties you can execute this line in FireBug console:


  console.log(apex.widget.report.gPageItemsToSubmit);


When you change session state protection property of item P2_DEPTNO to "Checksum Required - ..." refresh of report will not work but neither the pagination on that report.

So watch out with property "Page Items to Submit"!!

In next post I'll show you how can you easily change item session state on some other ways.

Tested on APEX 4.2.3.00.08.