APEX and ORDS

Security parameter

wwv_flow_epg_include_modules.authorize

This is is a re-tweet of Joels' blog post published on 24th of July. 

If you are running APEX on ORDS definitely have a look at Joel's blog and you might want to re-check your configuration settings to make sure your apps are safe and secure.

It is all about setting this 

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

in your ORDS configuration.  Great news is this will be a default setting on ORDS 3.0.7.

Happy reading,
Lino

New line formatting

APEXOfficePrint - AOP tips

How to get your AOP showing the new line?

Just a quick demo on how you can create new line using AOP. 

Let's say we have a column that contains char(13)char(10) (Carriage return and Line feed) characters which user wants to have reflected on pdf or Word output. 

In you source query you would have to do something like:

select replace('a ' || chr(10)||chr(13)|| ' BBBBBB', chr(10)||chr(13), '\n')  as "cust_info" from dual

This is then at run time translated to new lines and everyone is happy. 

If you have any questions please do not hesitate.....

Thanks,
Lino

Oracle APEX 5.0.4 patch released

Oracle Application Express Release 5.0.4.00.12

New APEX patch available for download

Fresh news from this morning. Just notice a tweet by @msewtz from APEX team that new update is available. 

APEX 5.0.4 is now available for download.

Table 1 Bugs Fixed in the Oracle Application Express 5.0.4.00.12 Patch Set

Bug Number	Description
21437474	Intermittent bug with Oracle Application Express SQL Workshop uploads - random errors
21517239	Reopen cancelled modal dialog in Internet Explorer 11 results in disabled input fields
22110421	Editing a workspace end user failed with internal error
22493656	Upgrading Oracle Application Express 4.2 to 5.0.3 throws ORA-20001: Compile Error: "Package Body"
22532860	APEX_JSON: XML to JSON conversion treats 0 as string and 0123 as number
23249353	Classic report escaping: "Display As" for substitution taken from wrong column
23261092	SSO: // in redirect path when SSO success URL registered for different host

Cheers,
Lino

KScope2016 - ODTUG

Summary of sessions

What I liked the most?

Conference is over and it is nice to be back home. 

This is a brief post on things happening last week and notes that I taken with me while thoughts are still fresh. 

Please note that at each point there were 3 sessions available so attending and planning was made hard. These are notes from ones I attended.

Let's start with Sunday symposium:

Shakeeb was first to go, great little into into UI changes coming with APEX 5.1.

100+ UI improvements that can easily slip under the radar:

• button builder by example
• default label column template set now to span 2 columns
• APEX font extension on top of Font-awesome library for sharper and more modern application icons that will be kept up to date by the Team
• Inline help text
• Quick edit and live template options that allows us to see changes real time before saving them
• right to left support by switching an option
• migration from 5.0 to 5.1 made simple as possible - using Verify function, together with changing reference to new APEX font library and reapplying any old custom roller theme    

Followed by Marc Sewtz presentation on Page designer and Charts.

Partick Wolf - Interactive Grid 5.1

• ability to enable/disable it from the code
• conditional entering while in edit mode only
• new Reload Page on Submit process
• multiple file upload support
• file type filter for files being uploaded
• changes to yes/no item type
• ability to set status of application unavailability from PL/SQL
• new admin read only workspace instance role
• remote debugging
• regardless of IG 5.1 will finally create new session if page opened in new tabs         

Day 2 

Started with Martin's presentation on Open source amd APEX.

Excellent overview on available OraOpenSource projects and some must have features for every APEX project.

• Logger
• PL/SQL mark up tool for documentation
• OXAR - prebuild APEX DEV environment
• APEX-SERT
• OOS utils   

Karen's IR reporting Guts and performance overview

• how to tweak and optimize run of your reports
• tune your real SQL
• tune returned query using indexes
• train your users how to use it
• max row count has biggest impact as it changes query execution plans
• restrict max rows for large tables
• if download all rows needed provide separate functionality for this
• avoid x to y of z - if needed provide info separately of number of rows
• use IR_QUERY API to get number of rows for this view
• use LOV for filtered columns as initial LOV gets only 1000 rows or so     
• column filter options: none at all, exact match and name contains
• if none of this works try pipeline functions

Anton Nielsen - Exploiting power of database

• ORACLE_TEXT with ability to search content of files stored as blob
• stemming and search for plural words
• fuzzy logic and about options
• q operator for quoting all queries syntax used
• importance sql analytics bringing performance benefits
• OWA package 
• ORD_IMAGE multimedia objects that can read image, date information, resize
• Resource manager - manage how much influence can each app have down to user or app level
• utl_http
• apex_ldap
• external tables
• dbms_crypto
• 12c approximate count over exact count performance benefit        

Followed by Dimitri's presentation on Real Application Security, enterprise tool providing another security layer and protection to your data and application. 

Closed with Jorge Rimblas with his application on Classic report templates and Oliver Lemm's APEX and JIRA implementation.

Day 3

Started with APEX and Handlebars by Marko G. Excellent way how to improve report performance using customized templates that get injected with JSON. Very impressive and highly useful demonstration of upcoming technology.

John Scott - automated testing

• nodeJS and nightmare.js, phantom.js libraries to create test cases
• maximizing results with less efforts of manual work
• interesting alternatives to Sellenium and other classic automated testing tools

Followed by another great security presentation by RECX team.

• 96% apps have access control issues
• problematic are hidden items, page access and htp.p 
• already known report template issue when using #COLUMN# substitutions
• make sure to sanitize the data during the input
• use #COLUMN#!JS escaping functions
• look for htp.p functions that 'text ' || :P1_ITEM and use APEX_ESCAPE.html()
• look for execute immediate into variable
• when printing on exception also encode your variables
• when using htp.p use appropriate escaping function
• for example if data is JSON use APEX_ESCAPE.JSON function
• be aware of functions returning SQL query with where clause that uses || :P2_ITEM
• do not use || :P_ITEM always put it in string enclosed with ''
• always review your code among team 

Adrian showed us power of semantics in modern browsers. 

Day ended with another high tech presentations by Dimitri G. on service workers. Technology that promises and potentially presents a future of modern apps. Ability to provide caching and offline experience for end users, background synchronizations and application messaging when browser is not even opened. 

Day 4

Single sign on using Kerberos and Niels de Bruijn from MT-AG team where he highlighted advantages of using this approach over classic LDAP. There is a white paper available on this and all related details.

APEX and affordable workflow options by Niels - Comunda tool with commercial and community edition. Excellent presentation how to utilize and implement workflow processes using APEX applications. Can run on Tomcat and comes with cockpit java application, all matadata can be installed in ORACLE DB with webservice to make it move around the flows. Very well accepted among the audience and very intriguing solution in my personal opinion.

Among others would like to mention Slavica's presentation on Accessibility where she clearly outlined the importance of making your apps follow latest standards and recommendations. 

 Day was closed by RECX team and CCS attacks security overview. Notes:

• www_flow.show?p_flow_id=4500&p_flow_step_id=10000 be aware to remove this from being abled to be accessed by external ONLINE USERS
• moving application access policy to ORDS
• secure ORDS to only allow APEX based functions by creating security.inclusionList parameter apex_, p, v, f, www_flow and not to allow any public procedures
• have another ORDS for internal and public applications
• if needed  to run public procedures put it on page before header region with apex_stop_engine call;
• on workspace instance..... only allow Allow Hostnames: www.xxxx.co.uk
• return HTTP 400 error instead of errem under unhandeled exceptions for online apps as sometimes they return information about columns and tables which are DB related
• monitor your apex_workspace_activity_log and apex_workspace_ access_log 
• never allow production workspace login for developers as attacker can reuse and check if user is developer and use this session for further attacks

All in all something for everyone to learn and above all to have fun.

What I can add to this if you happen to have the opportunity to visit Chicago please do so as city is really nice, people are kind and friendly and it offers quite a few interesting sites like The Loop, Millennium park, the Bean, lake front, river cruise and navy Pier.

Thank you to ODTUG, presenters, great #orcleapex community and awesome city of Chicago - look forward to next year and San Antonio.

Regards,
SLino