Oracle Application Express
Encyclopedia
Oracle Application Express (Oracle APEX, previously named Oracle HTML DB) is a software development environment
based on the Oracle database
. It allows a fast development cycle to be achieved to create web based applications. It can be used for departmental-style applications with a dozen users, but can also scale up to handle thousands of users. The framework itself adds as little as 0.04 second of overhead to each page request; how well an application scales is primarily based on the efficiency of the SQL
queries used by the application developer.
In January 2006 Oracle renamed HTML DB to "Oracle Application Express". Version 2.1 of APEX was bundled with the free Oracle Express Edition (XE) database.
In 2007 Oracle released APEX 3.0. This third major version features several new features, notably PDF Printing and Flash charting
. APEX 3.0.1 was released in July 2007, and this version can also be installed into an Oracle XE database.
In Spring 2008 Oracle released APEX 3.1. This included a new major feature known as Interactive Reporting, which enables end-users to extensively customize a report without programmer intervention, using techniques such as filtering, sorting, group-by, choosing displayed columns, etc. The user can even save multiple versions of their customized reports. The programmer can limit which features are enabled. With this power comes a loss of programmer control over the layout of the report.
APEX 4.0 became available in June 2010. Some notable features are declarative dynamic actions which allow reacting to changes on a page without the developer having to write the javascript and RESTful capabilities.
Historically speaking, Application Express has gone through many name changes since its inception in 2000. A reasonably complete history of the names includes:
One popular misconception is that Application Express is a new version of Web DB. Mike Hichwa created Web DB, a successful web front-end for Oracle, but the development of Web DB started to move in a direction that diverged from Mike's vision. When tasked with building an internal web calendar, Mike enlisted the help of Joel Kallman and started "Flows". They co-developed the Web Calendar and Flows, adding features to Flows as they needed them to develop the calendar. In the earliest days of Flows, there was no front-end for it, so all changes to an application were made in SQL*Plus via inserts, updates and deletes. In some ways APEX is an evolution of Web DB, but it was developed with new code and no upgrade path.
A popular application developed in Application Express is the AskTom application developed by Thomas Kyte. Oracle's Metalink support site had been running on APEX, but was replaced with a Flash version in September 2008. Oracle's online store also runs on APEX.
flaws as other web applications based on more direct technologies such as PHP, ASP.net and Java.
The main classes of vulnerability that affect APEX applications are: SQL injection
, Cross-site scripting
(XSS), and Access Control.
APEX applications inherently use PL/SQL constructs as the base server-side language. As well as accessing data via PL/SQL blocks, an APEX application will use PL/SQL to implement authorization, and to conditionally display web page elements. This means that generally APEX applications suffer from SQL injection when these PL/SQL blocks do not correctly validate and handle malicious user input. Oracle implemented a special variable type for APEX called Substitution Variables (with a syntax of &NAME.) and these are not safe and lead to SQL Injection. Where the injection occurs within a PL/SQL block an attacker can inject an arbitrary number of queries or statements to execute.
Cross-Site Scripting vulnerabilities arise in APEX applications just like other web application languages. Oracle provide the htf.escape_sc function to escape user data that is displayed within a rendered HTML response. The reports that APEX generates also provide protection against XSS through the Display As setting on report columns. Originally the default was for reports to be created without any escaping of the columns, although recent versions now set the column type to escape by default. Column definitions can be queried programmatically to check for columns that do not escape the value.
To control access to resources within an APEX application a developer can assign authorization schemes to resources (such as pages and items). These must be applied consistently in order to ensure that resources are appropriately protected. A typical example of inconsistent access-control being applied is where an authorization scheme is set for a Button item, but not the associated Process that is performed when the button is clicked. A malicious user can perform the process (through JavaScript) without requiring the actual Button to be accessible.
Since APEX 4.0, the Application Builder interface provides some limited assessment of the security posture through the Advisor utility.
Development environment
In hosted software development, a development environment refers to a server tier designated to a specific stage in a release process....
based on the Oracle database
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
. It allows a fast development cycle to be achieved to create web based applications. It can be used for departmental-style applications with a dozen users, but can also scale up to handle thousands of users. The framework itself adds as little as 0.04 second of overhead to each page request; how well an application scales is primarily based on the efficiency of the SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
queries used by the application developer.
Background
Oracle Application Express can be installed in an Oracle 9.2 or higher database, and starting from Oracle 11g it will be preinstalled along with the database. APEX 4.0 and higher can be installed on an Oracle 10.2.0.3 or higher database.In January 2006 Oracle renamed HTML DB to "Oracle Application Express". Version 2.1 of APEX was bundled with the free Oracle Express Edition (XE) database.
In 2007 Oracle released APEX 3.0. This third major version features several new features, notably PDF Printing and Flash charting
Flash charting
A Flash chart is an animation that depicts a standard statistical chart using Adobe Flash. Flash charts are generated using a Flash charting tool and they can be incorporated in websites or in desktop applications. Flash charts can be viewed on all computers that have Adobe Flash player installed...
. APEX 3.0.1 was released in July 2007, and this version can also be installed into an Oracle XE database.
In Spring 2008 Oracle released APEX 3.1. This included a new major feature known as Interactive Reporting, which enables end-users to extensively customize a report without programmer intervention, using techniques such as filtering, sorting, group-by, choosing displayed columns, etc. The user can even save multiple versions of their customized reports. The programmer can limit which features are enabled. With this power comes a loss of programmer control over the layout of the report.
APEX 4.0 became available in June 2010. Some notable features are declarative dynamic actions which allow reacting to changes on a page without the developer having to write the javascript and RESTful capabilities.
Historically speaking, Application Express has gone through many name changes since its inception in 2000. A reasonably complete history of the names includes:
- Flows
- Oracle Platform
- Project Marvel
- HTML DB
- Application Express (APEX)
One popular misconception is that Application Express is a new version of Web DB. Mike Hichwa created Web DB, a successful web front-end for Oracle, but the development of Web DB started to move in a direction that diverged from Mike's vision. When tasked with building an internal web calendar, Mike enlisted the help of Joel Kallman and started "Flows". They co-developed the Web Calendar and Flows, adding features to Flows as they needed them to develop the calendar. In the earliest days of Flows, there was no front-end for it, so all changes to an application were made in SQL*Plus via inserts, updates and deletes. In some ways APEX is an evolution of Web DB, but it was developed with new code and no upgrade path.
A popular application developed in Application Express is the AskTom application developed by Thomas Kyte. Oracle's Metalink support site had been running on APEX, but was replaced with a Flash version in September 2008. Oracle's online store also runs on APEX.
Functions and Uses
- Fast development
- Web-based
- Easy to create mock-ups
- Easy to deploy (end user opens a URL to access an APEX application)
- Scalable
- Server-side processing and validations
- Strong and supportive user community (especially Oracle APEX forum)
- Basic support for group development
- Free hosting of demo applications provided by Oracle
- Individual components of an application can be retrieved or identified using SQL, facilitating customized reports
Disadvantages
- Primary keys can be at most two separate fields. However since version 4.1 Application Express supports the use of ROWID for updates, inserts and deletes as an alternative to specifying primary keys. Prior to version 4.1 APEX assumed by default that all tables would use generated keys such as from sequences or triggers, therefore, if a table had more than two key columns then the default DML processes could not be used.
- Pages in APEX can display at most 100 items and forms cannot handle more than 100 database items. Compare this to the Oracle Database where tables can have up to 1000 columns.
- APEX applications are created using Oracle's own tools and only can be hosted in an Oracle database, making an implementer susceptible to vendor lock-inVendor lock-inIn economics, vendor lock-in, also known as proprietary lock-in or customer lock-in, makes a customer dependent on a vendor for products and services, unable to use another vendor without substantial switching costs...
. - As an application framework, it can be difficult to customize an application outside of a set of expectations about how an APEX application is supposed to operate.
- Large installation size (V4.1 is 747Mb)
- Limited debugging facilities
- Very few webhosts offer APEX (Oracle Database) on their hosting service package (most of them offer PHPPHPPHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...
+ MySQLMySQLMySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
or ASPActive Server PagesActive Server Pages , also known as Classic ASP or ASP Classic, was Microsoft's first server-side script engine for dynamically-generated Web pages. Initially released as an add-on to Internet Information Services via the Windows NT 4.0 Option Pack Active Server Pages (ASP), also known as Classic...
+ Microsoft SQL ServerMicrosoft SQL ServerMicrosoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
). As a result, APEX applications are limited in their choice of webhosts.
APEX Security
There is a common misconception that the abstracted nature of APEX applications results in a relatively secure user environment. However, APEX applications suffer from the same classes of application securityApplication security
Application security encompasses measures taken throughout the application's life-cycle to prevent exceptions in the security policy of an application or the underlying system through flaws in the design, development, deployment, upgrade, or maintenance of the application.Applications only...
flaws as other web applications based on more direct technologies such as PHP, ASP.net and Java.
The main classes of vulnerability that affect APEX applications are: SQL injection
SQL injection
A SQL injection is often used to attack the security of a website by inputting SQL statements in a web form to get a badly designed website in order to dump the database content to the attacker. SQL injection is a code injection technique that exploits a security vulnerability in a website's software...
, Cross-site scripting
Cross-site scripting
Cross-site scripting is a type of computer security vulnerability typically found in Web applications that enables attackers to inject client-side script into Web pages viewed by other users. A cross-site scripting vulnerability may be used by attackers to bypass access controls such as the same...
(XSS), and Access Control.
APEX applications inherently use PL/SQL constructs as the base server-side language. As well as accessing data via PL/SQL blocks, an APEX application will use PL/SQL to implement authorization, and to conditionally display web page elements. This means that generally APEX applications suffer from SQL injection when these PL/SQL blocks do not correctly validate and handle malicious user input. Oracle implemented a special variable type for APEX called Substitution Variables (with a syntax of &NAME.) and these are not safe and lead to SQL Injection. Where the injection occurs within a PL/SQL block an attacker can inject an arbitrary number of queries or statements to execute.
Cross-Site Scripting vulnerabilities arise in APEX applications just like other web application languages. Oracle provide the htf.escape_sc function to escape user data that is displayed within a rendered HTML response. The reports that APEX generates also provide protection against XSS through the Display As setting on report columns. Originally the default was for reports to be created without any escaping of the columns, although recent versions now set the column type to escape by default. Column definitions can be queried programmatically to check for columns that do not escape the value.
To control access to resources within an APEX application a developer can assign authorization schemes to resources (such as pages and items). These must be applied consistently in order to ensure that resources are appropriately protected. A typical example of inconsistent access-control being applied is where an authorization scheme is set for a Button item, but not the associated Process that is performed when the button is clicked. A malicious user can perform the process (through JavaScript) without requiring the actual Button to be accessible.
Since APEX 4.0, the Application Builder interface provides some limited assessment of the security posture through the Advisor utility.