Anatomy of an Attack

To succeed, SQL injection attacks rely on developers improperly handling variables that users can influence in their code. The key here is how a developer incorporates variables provided by the user in their actual SQL or PL/SQL code. In cases that are susceptible to SQL injection attacks, this is done by concatenating the static portion of the query with the values provided by the user. What this does is give a malicious user the opportunity to provide a snippet of SQL as a value rather than an expected value, such as a name or ID. Rather than be used as part of the predicate, this snippet will be used to restructure the actual SQL statement before it executes.

Let’s start with a simple example that can be illustrated with SQL*Plus. The code in Listing 7-1 takes in a single input parameter, p_ename. This input parameter will be used when constructing the SQL that will be executed. For this example to work, be sure that the EMP demonstration table is installed in the schema.

Listing 7-1.  A Simple PL/SQL Procedure That Is Susceptible to SQL Injection

CREATE OR REPLACE PROCEDURE sqli_example
  (
  p_ename IN VARCHAR2
  )
AS
  l_sql VARCHAR2(100);
  type emp_t IS TABLE OF emp%ROWTYPE;
  emp_r emp_t := emp_t();
BEGIN
-- Concatenate the SQL statement, including quotes
l_sql := 'SELECT * FROM emp WHERE ENAME = '''
  || p_ename || '''';

-- Print the SQL statement about to be executed
DBMS_OUTPUT.PUT_LINE(l_sql);

-- Execute the SQL statement
EXECUTE IMMEDIATE l_sql BULK COLLECT INTO emp_r;

-- Loop through the results and print the name of the employee
FOR x IN emp_r.FIRST..emp_r.LAST
LOOP
  DBMS_OUTPUT.PUT_LINE('Emp: ' || emp_r(x).ename
    || ' - Dept:' || emp_r(x).deptno);
END LOOP;
END sqli_example;
/

Entering any valid name of an employee in the EMP table will result in printing part of that employee record. For example, if the value KING is passed in, the results would resemble those in Listing 7-2, which is to be expected.

Listing 7-2.  The Results of the Procedure Using the Value KING for p_ename

SQL> BEGIN
  2  sqli_example(p_ename => 'KING'),
  3  END;
  4  /
SELECT * FROM emp WHERE ENAME = 'KING'
Emp: KING - Dept:10

PL/SQL procedure successfully completed.

However, if a snippet of SQL were entered instead of just a value—specifically, 'KING'' OR ''X'' = ''X'—the results would be very different, as shown in Listing 7-3.

Listing 7-3.  The Results of the Same Procedure Using a SQL Injection Attack

SQL> BEGIN
  2  sqli_example(p_ename => 'KING'' OR ''X'' = ''X'),
  3  END;
  4  /
SELECT * FROM emp WHERE ENAME = 'KING' OR 'X' = 'X'
Emp: KING - Dept:10
Emp: BLAKE - Dept:30
Emp: CLARK - Dept:10
Emp: JONES - Dept:20
Emp: SCOTT - Dept:20
Emp: FORD - Dept:20
Emp: JONES - Dept:20
Emp: ALLEN - Dept:30
Emp: WARD - Dept:30
Emp: MARTIN - Dept:30
Emp: TURNER - Dept:30
Emp: ADAMS - Dept:20
Emp: JAMES - Dept:30
Emp: MILLER - Dept:10

PL/SQL procedure successfully completed.

It is very clear that in the SQL that is about to be executed that the input provided added an additional portion to the WHERE clause of the query, namely, OR 'X' = 'X'. This caused the logic of the SQL to change, and since a literal always equals the same literal, the right half of the OR clause evaluates to TRUE, thus triggering all records to be returned versus just the one that matches the input parameter.

The reason that this is possible is that the SQL is altered before the database has a chance to parse it. Parsing is one of the first steps to occur when the database attempts to run a SQL statement. During this phase, the database examines the SQL and ensures that it is syntactically correct and all objects that it references are accessible and valid. In some sense, the database is “stupid” because it is unable to make a distinction between an unaltered and an altered SQL statement. It simply parses what is passed to it.

Fortunately, there is a simple solution to this problem: bind variables. As their name implies, bind variables are evaluated during the bind phase of processing a query. During the bind phase, any placeholder in the SQL statement is replaced with the corresponding value. Since the structure of the SQL has already been processed, it can no longer be influenced with a SQL injection attack during the bind phase.

Therefore, with a small alteration to the code, the previously vulnerable PL/SQL procedure can be made safe by using bind variables, as outlined in Listing 7-4.

Listing 7-4.  A Simple PL/SQL Procedure That Uses Bind Variables and Is Not Susceptible to SQL Injection Attacks

CREATE OR REPLACE PROCEDURE sqli_fixed_example
  (
  p_ename IN VARCHAR2
  )
AS
  l_sql VARCHAR2(100);
  type emp_t IS TABLE OF emp%ROWTYPE;
  emp_r emp_t := emp_t();
BEGIN
-- Assemble the SQL statement with a bind variable
l_sql := 'SELECT * FROM emp WHERE ENAME = :ename';
-- Print the SQL statement about to be executed
DBMS_OUTPUT.PUT_LINE(l_sql);
-- Execute the SQL statement
EXECUTE IMMEDIATE l_sql BULK COLLECT INTO emp_r USING p_ename;
-- Loop through the results and print the name of the employee
IF emp_r.COUNT > 0 THEN
  FOR x IN emp_r.FIRST..emp_r.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('Emp: ' || emp_r(x).ename
      || ' - Dept:' || emp_r(x).deptno);
  END LOOP;
ELSE
  DBMS_OUTPUT.PUT_LINE('No Data Found'),
END IF;
END sqli_fixed_example;
/

There are three main changes to this version of the procedure. First, the string assembled into the l_sql variable is no longer concatenated; rather, a bind variable reference called :ename is included. Second, when the EXECUTE IMMEDIATE line is run, it will bind the value passed into the parameter p_ename to the query stored in l_sql. Bind variables are positional when called from an EXECUTE IMMEDIATE statement, so it will simply use the :ename reference when binding in the value from p_ename. Third, a simple check to ensure that there is at least one record present before entering the loop was added. Otherwise, the procedure would throw an error when no matching records were found.

Now, if the same SQL injection attack is implemented against the updated procedure, very different results occur, as shown in Listing 7-5.

Listing 7-5.  Results of a SQL Injection Attempt Against a Secure Procedure

SQL> begin
  2  sqli_fixed_example(p_ename => 'KING'' OR ''X'' = ''X'),
  3  end;
  4  /
SELECT * FROM emp WHERE ENAME = :ename
No Data Found

Since APEX will call named PL/SQL program units quite frequently, it is important to ensure that any use of EXECUTE IMMEDIATE or DBMS_SQL with SQL that contains variables does so properly by using bind variables. In addition to being more secure, bind variables are a much more performant way to write SQL. So, as a side benefit, secure SQL will typically run faster.

SQL Injection in APEX

Let’s take a look at an example SQL injection attack on an APEX application. This example also requires the EMP table to be installed in your parse-as schema. To start, create a report on a page using the SQL in Listing 7-6. Next, create a page item of type text field called P1_ENAME, as well as a button that will submit the page.

Listing 7-6.  SQL Statement for the Report

SELECT * FROM emp WHERE deptno = 10 AND ename LIKE '&P1_ENAME.'

The SQL used in the report will do two things to limit the records retrieved. First, it will return only those records that have a value of 10 for the DEPTNO column. Next, it will filter the ENAME column with the value of the user’s input in the item P1_ENAME. The operator used to filter the records is LIKE, so it is possible for the user to enter a %, which will act as a wildcard.

Running the page with the report on it returns no records, as shown in Figure 7-1.

This is because no value was entered into P1_ENAME, causing the LIKE operator to not find any matches. The fact that only records where DEPTNO is 10 was irrelevant. Now, rerun the report, but this time, enter the name of a valid employee in the EMP table who has a DEPTNO of 10, such as KING. The report behaves as expected, returning only a single record, as shown in Figure 7-2.

Since the SQL uses the LIKE operator, it is possible to execute a fuzzy search by using the % character. In fact, entering just a single % into P1_ENAME should return all records that have a DEPTNO of 10, as illustrated in Figure 7-3.

Even though a wildcard character was entered, the first half of the predicate ensured that only those employees who have a DEPTNO of 10 were returned.

So far, the report has behaved exactly as expected, returning the corresponding records based on the values passed in to P1_ENAME. Remember, SQL injection attacks use a value that will modify the SQL that will be executed rather than be used to actually filter the results. You can see an example of such a string in Listing 7-7.

Listing 7-7.  Value to Enter to Implement a SQL Injection Attack

KING' or 'x' = 'x

Entering this string into P1_ENAME and running the report would return all of the records in EMP, as shown in Figure 7-4.

Displaying records that have a DEPTNO equal to anything other than 10 was not the developer’s intention when this report was created. But clearly when the illicit value was passed in to P1_ENAME, that is exactly what happened.

The SQL injection attack succeeded for one simple reason: the wrong APEX item syntax was used in the SQL query. Taking another look at Listing 7-6, the &ITEM. syntax was used when referring to P1_ENAME. When using the &ITEM. syntax, APEX will evaluate and replace all variables before it parses the SQL statement. So, much like the previous example, it is possible to pass in a SQL snippet and have that snippet rewrite the SQL statement before it gets parsed, thus allowing for a SQL injection attack to succeed.

Fortunately, the same solution that worked for the sample PL/SQL procedure can be applied in APEX as well. By using bind variable syntax instead of the &ITEM. syntax, APEX will bind in the value for P1_ENAME during the bind phase as it runs the query. This way, the structure of the query will remain intact, and only the value passed in will be able to be influenced by the user.

To do this, simply change the way that the P1_ENAME item is referred to in the SQL for the report, as shown in Listing 7-8.

Listing 7-8.  Updated SQL for the Report, Using Bind Variable Syntax

SELECT * FROM emp WHERE deptno = 10 AND ename LIKE :P1_ENAME

When the report is rerun with the same value for P1_ENAME, very different results will occur, as shown in Figure 7-5.

Since the SQL is using the bind variable syntax now, attempting to change the SQL will fail, and unless there is an employee whose name is actually KING' or 'x' = 'x, no data will be found.

Therefore, the bind variable syntax should be used anytime a variable needs to be referenced within any SQL or PL/SQL region in APEX. This also includes conditions, authorization schemes, dynamic actions, or any other place within the APEX tool itself that SQL or PL/SQL is used with a variable.

Bind Variable Notation and Dynamic SQL in APEX

There is one exception to the approach of always using bind variables in APEX SQL and PL/SQL regions, and it is applied when using dynamic SQL. Dynamic SQL reports in APEX—also referred to as the PL/SQL Function Body Returning SQL Query report—essentially allow the developer to assemble a SQL statement and then use that as the source of a report. Dynamic SQL can also be utilized in other areas of APEX, such as the source for lists of values and lists.

Dynamic SQL statements consist of two components: the static component and the variable component. You can see a simple example of a dynamic SQL statement in Listing 7-9.

Listing 7-9.  A Simple Example of a Dynamic SQL Statement

DECLARE
  l_sql VARCHAR2(255);
BEGIN
-- Start the SQL statement
l_sql := 'SELECT * FROM emp';
-- If P1_ITEM is set to Y, include the WHERE clause
IF :P1_ITEM = 'Y' THEN
  l_sql := l_sql || ' WHERE deptno = 10';
END IF;
-- Return the SQL
RETURN l_sql;
END;

This snippet can produce two different SQL queries, based on the value of the APEX item P1_ITEM. If P1_ITEM is set to Y, then the SQL returned will be SELECT * FROM emp WHERE deptno = 10. Otherwise, the SQL returned will be SELECT * FROM emp. The APEX report that this SQL is returned to will then use either of those statements when producing the report. There is also no inherent SQL injection risk with this example, because no variables are used in the construction of the SQL.

The next example uses a variable using bind variable syntax when constructing the SQL statement. If the user enters a value into the item P1_DEPTNO, the code will then incorporate that value into the WHERE clause of the query, as shown in Listing 7-10.

Listing 7-10.  An Example of Dynamic SQL That Incorporates a Bind Variable

DECLARE
  l_sql VARCHAR2(255);
BEGIN
-- Start the SQL statement
l_sql := 'SELECT * FROM emp';
IF :P1_DEPTNO IS NOT NULL THEN
  -- Apply the filter if a value is provided
  l_sql := l_sql || ' WHERE deptno = ' || :P1_DEPTNO;
ELSE
  -- Otherwise, force the query to return no rows
  l_sql := l_sql || ' WHERE 1=2';
END IF;
-- Print the SQL
htp.p(l_sql);
-- Return the SQL
RETURN l_sql;
END;

When this page is run and a valid department number is passed in to P1_DEPTNO, the reports adjusts its results accordingly, returning only those records that match the corresponding department, as shown in Figure 7-6.

However, when a SQL injection attack is attempted on this report—which, remember, uses bind variable syntax—the results may be a bit surprising, as illustrated in Figure 7-7.

What happened? Because this is a dynamic SQL query, the database executes it twice—the first time to concatenate and return the SQL query and the second time to execute it. During the first time it is run, the value of P1_DEPTNO is evaluated, replaced, and then concatenated with the rest of the l_sql string. If a snippet of SQL is passed in via P1_DEPTNO, it will simply get concatenated as part of the string before the PL/SQL block is parsed. Thus, a SQL injection attack can still be achieved, despite that bind variable notation was used.

The proper way to use dynamic SQL in APEX is to ensure that variables that should be part of the SQL are not evaluated while concatenating the string but are instead included as part of the string in their bind variable syntax form. In the case of the previous example, this would be achieved simply by moving in the reference to :P1_DEPTNO into the string rather than concatenating it with the string. You can see the correct version of the line in question in Listing 7-11.

Listing 7-11.  The correct way to concatenate bind variables into dynamic SQL in APEX

-- Apply the filter if a value is provided
l_sql := l_sql || ' WHERE deptno = :P1_DEPTNO';

With the reference to :P1_DEPTNO moved back into the string, any SQL injection attack on the report will fail with an invalid number error message, as shown in Figure 7-8.

Getting the hang of where to include references to APEX items in dynamic SQL can be tricky. There is a technique that you can use to assist in understanding it better. First, the end goal when using a dynamic SQL statement is to produce a sound, secure SQL statement that makes use of bind variables where needed. It may help to try to visualize or even write down what the expected end result looks like and then instrument your code to produce that.

To assist with the visualization, run the page in debug mode, and then search through the debug information. APEX will automatically insert a record that includes the SQL that it used for dynamic SQL reports, as shown in Figure 7-9.

Alternatively, you can add a simple line such as htp.p(l_sql) that prints the variable used to store the SQL statement, as was done in the examples. If any variables are evaluating too early, they will be evident on the page where the query prints. This technique is a bit quicker but more intrusive than debug mode because the results of the SQL are displayed right above the report as soon as it is generated. Be sure to remove code to display the SQL before deploying to production.

SQL injection is a real risk for APEX and any web application. Fortunately, most APEX developers are already in the habit of using the bind variable syntax to represent APEX items, which is the recommended best practice. Bind variables alone are not the entire answer because attention also needs to be paid when using dynamic SQL in APEX or concatenating strings in named PL/SQL program units that will be passed to EXECUTE IMMEDIATE.

Anatomy of an Attack

XSS attacks involve passing in a snippet of malicious JavaScript that is then executed by the browser. Since the browser cannot tell the difference between JavaScript that was intended to be on a page from illicit JavaScript, anything that gets passed to it without being properly handled will execute. Thus, in order to implement an XSS attack, you need to have at least an intermediate understanding of JavaScript. When executed, the malicious JavaScript can do any number of things: alter hidden values on a form, open up an iFrame that contains a different site, steal session or cookie values and pass them back to the malicious user’s server, or even try to initiate transactions from other parts of the site that the victim is authenticated to. The severity of an XSS attack is limited only by the malicious user’s imagination and technical capabilities.

One of the best defenses against any type of XSS attacks is to escape all data before rendering it on the page. When data is escaped, the browser will not try to execute the code but rather harmlessly render it on the page. You can find more details on how to escape data later in this section.

XSS attacks can mostly be categorized into a couple different categories: reflexive and persistent. While both types are equally as dangerous, the methods in which they are implemented do differ. In an APEX environment, it is much more likely to see a persistent attack.

Reflexive Attacks

Reflexive XSS attacks—sometimes referred to as nonpersistent attacks—rely on user input being displayed or rendered on the very next page of a web site. In APEX, an example of this would be an error message that contained a reference to a page or application item or a search page that displayed the search term alongside the results. If the page is not properly secured, a malicious user can inject some JavaScript code into a form, and when the resulting page is rendered, the JavaScript will execute.

In APEX 4.0 and older, reflexive attacks were a lot easier to pull off, since values passed to application items via the URL were not escaped. Consider this example: a page contained a region that had a title that included reference to an application item, such as Transactions for &G_USER.. If a malicious user altered the URL to include a value for G_USER, as shown in Listing 7-12, the attack would be successful.

Listing 7-12.  Passing Illicit JavaScript to the G_USER Item via the URL

f?p=139:2:3628920663345303::::G_USER:<script>alert('Hello'),</script>

You can see the result of this attack in Figure 7-10 because the JavaScript alert function executes and produces a dialog box.

Clearly there is no harm in displaying a dialog box. However, if this simple JavaScript code can be executed, more sophisticated and damaging code could also be executed. Fortunately, this vulnerability was fixed in APEX 4.1 so that any subsequent release is protected against such an attack. The fix involved ensuring that any value passed through the URL is escaped before it is rendered. Using the same syntax illustrated in Listing 7-12 in APEX 4.2, the result is very different, as shown in Figure 7-11.

This time, instead of the JavaScript code being executed, it was harmlessly rendered as the title of the region. This example is just one of many that underscores the importance of keeping current with the release of APEX. With each release, more and more potential security vulnerabilities are fixed.

APEX has long since automatically escaped page items when they are rendered on the page using the &ITEM. syntax. Thus, if the previous example tried to pass a value to a page item instead of an application item via the URL, there would be no chance that the XSS attack would succeed.

But what if the value of the item is not passed through the URL but rather set via a page computation? The next example does just that—incorporates the value of a page item in the region title. When a snippet of JavaScript is passed in, it is safely escaped and rendered, as shown in Figure 7-12.

APEX doesn’t care how the page item got its value; it will always escape any page item before rendering it regardless as to whether it was set via the URL or a computation.

On the other hand, application items are never escaped before being rendered. If an application item is used in part of a region title and that item contains malicious JavaScript, that code will execute, not render harmlessly on the page. This can occur by using a computation that sets the value of an application item to the value of a page item that contains malicious code or by fetching unescaped data from a table or view and assigning it to the application item.

In the next example, the user can input a string into an item called P1_SEARCH. On the same page, there is an after submit computation that sets an application item called G_SEARCH to the value of P1_SEARCH. The region title on the page contains a reference to G_SEARCH using the &ITEM. syntax, with the intent being to alter the region title based on what the user searched for.

Since G_SEARCH is being set from an APEX computation, not via the URL, the string passed to it is not escaped by default. If a bit of malicious JavaScript—<script>alert('Hello'),</script>—is entered, it will in turn be used to set G_SEARCH and, then when the page renders, execute and display an alert, as shown in Figure 7-13.

This risk exists in all versions of APEX, up to and including APEX 4.2. Therefore, it is recommended that you be cautious when setting values of application items that will be used as part of a static component of an application such as the region title, breadcrumb, tab, list item, and so on. When this is necessary, the value passed to the application item should be escaped by using the apex_escape.html function (more on this later in this chapter) so that any malicious scripts that may be embedded in the item are rendered harmless.

Persistent Attacks

Persistent XSS attacks are similar to reflexive XSS attacks, in that they inject JavaScript code in hopes of it getting executed. The main difference between the two is that a reflexive attack is carried out and impacts only a single user, whereas a persistent attack is carried out by a single user but is designed to impact many. It does this by storing the malicious script in the database rather than simply manipulating a single page. This way, many users can potentially fall victim to the attack over and over again. If undetected, a persistent XSS attack can remain active indefinitely, doing a tremendous amount of damage as more and more users fall victim to it.

Persistent XSS attacks rely on a developer fetching and displaying data from the database without first escaping the data. When this is the case, any malicious code that is stored within the data will be executed rather than displayed. Every time that the record that has been injected with the malicious code is displayed, the attacks occur. When implemented properly, there will be no evidence to the end user that an attack is occurring. Any malicious code will silently execute, giving the user no indication that there may be a problem.

To illustrate a persistent XSS attack, let’s consider an example of a simple contact management system. When a malicious user wants to launch a persistent XSS attack, the user would update their own record and embed the malicious JavaScript within one of their fields, as illustrated in Figure 7-14.

The intent here is that anyone who searches for the malicious user’s record and views it as part of a report will fall victim to the attack because that is where the malicious code would be executed. Every time their record came up as part of a report, the code would execute again, as shown in Figure 7-15. In a real attack, it would be unlikely that the malicious code made any indication as to its existence by displaying an alert. Rather, the attack would likely attempt to steal session values or other sensitive information and send it back to the malicious user.

In more modern versions of APEX, a developer would have to go out of their way to enable the persistent XSS attack to occur because report columns are now created with built-in escaping routines by default. Applications written in older versions of APEX may also still be susceptible to this type of attack, even if upgraded to APEX 4.1 or 4.2.

Persistent XSS attacks do not have to be limited to the same application in which they were implemented. It is possible to launch the persistent attack through one application and have it manifest itself through a completely different application written in a completely different technology, making them that more dangerous.

Restricted Characters

New in APEX 4.2, the Restricted Characters attribute allows the developer to choose which characters are allowed on a per-item basis. There are five different options for this attribute:

• All Characters Allowed
• Whitelist for a–Z, 0–9 and Space
• Blacklist HTML command characters (<>”)
• Blacklist &<>”;,*|=% and --
• Blacklist &<>”/;,*|=% or -- and new line

By restricting which characters are allowed, the risk of a malicious user executing an XSS attack is greatly reduced. APEX will validate the values entered in an item as the page processes, and if an invalid character is discovered, APEX will produce an error message, as shown in Figure 7-16.

As previously mentioned, while useful in some cases, simply escaping all input fields may not be the most effective strategy to prevent XSS attacks.

APEX_ESCAPE

The guidelines as to when to sanitize output are a little more cut and dry. If the data is going to be rendered as part of an HTML page, it needs to be sanitized. Fortunately, there are relatively simple ways to do this within APEX. While many components within APEX offer declarative controls that enable escaping, any regions that contain code that produces output need to handle this via calling the proper API. This includes any region that contains a SQL query—such as a report, chart, or calendar—as well as PL/SQL regions. Traditionally, APEX used a function called htf.escape_sc. Part of the Oracle Web Toolkit, htf.escape_sc would return an escaped version of the string passed to it, replacing instances of <, >, ", and & with their escaped counterparts.

New in APEX 4.2, an API called apex_escape was introduced. This API is designed to take the place of the older htf.escape_sc by providing more functionality and utilities for different contexts, such as JavaScript and LDAP. The apex_escape API was designed exclusively for use with APEX, so the level of integration is strong. Depending on the escaping mode, which can be set at the application level, apex_escape can behave the same way as htf.escape_sc does, or it can escape all four characters that htf.escape_sc does as well as the , and /.

When rendering HTML from a PL/SQL region or applying a function to a SQL query, ensure that the output string is passed to apex_escape.html. This will ensure that the values rendered are properly escaped, thus neutralizing any potential XSS attacks. For example, consider a PL/SQL region that loops through some records and prints the name and job of each record, as shown in Listing 7-13.

Listing 7-13.  Code for a PL/SQL Region That Prints Unescaped Text

BEGIN
FOR x IN (SELECT ename, job FROM emp ORDER BY ename)
LOOP
  htp.prn(x.ename || ' (' || x.job || ')<br />'),
END LOOP;
END;

When this region renders, it will display any data stored in the EMP and JOB columns without escaping it. If there is malicious code in either of those columns, it will be executed rather than rendered on the page. Adding a call to apex_escape.html, as shown in Listing 7-14, will ensure that any data output from this region will get properly escaped and rendered harmlessly.

Listing 7-14.  Code for a PL/SQL Region That Includes a Call to apex_escape.html

BEGIN
FOR x IN (SELECT ename, job FROM emp ORDER BY ename)
LOOP
  htp.prn(apex_escape.html(x.ename || ' (' || x.job)
    || ')<br />'),
END LOOP;
END;

Thus, be sure that anytime a PL/SQL region is used to render data on the page that the data gets properly escaped beforehand using the apex_escape.html function.

Column Formatting

Oftentimes, developers will intersperse HTML markup or references to images within a SQL query in order to include it as part of a report. While this practice does allow a lot of flexibility in the look and feel of a report, it could introduce the potential for a cross-site scripting attack. For example, take the SQL query in Listing 7-15.

Listing 7-15.  A SQL Query That Embeds Image References

SELECT
  e.ename,
  CASE
    WHEN e.deptno = 10 THEN '<img src="/i/green_flag.gif"> '
      || d.dname
    WHEN e.deptno = 20 THEN '<img src="/i/red_flag.gif"> '
      || d.dname
    WHEN e.deptno = 30 THEN '<img src="/i/grey_flag.gif"> '
      || d.dname
    WHEN e.deptno = 40 THEN '<img src="/i/yellow_flag.gif"> '
      || d.dname
    ELSE '<img src="/i/white_flag.gif"> ' || d.dname
  END icon
FROM
  emp e,
  dept d
WHERE
  e.deptno = d.deptno

In the recent few releases of APEX, all report columns in any report are configured by default so that their output will be escaped. Therefore, the data in the report that is based on the SQL query from Listing 7-15 will initially be escaped and render the HTML tags rather than interpret them. The result would look similar to Figure 7-17.

Each column in any APEX report—standard or interactive—contains a Display As attribute. This attribute determines how that column will be rendered when the report is run. By default, it will be set to “Display as Text (escape special characters, does not save state),” as shown in Figure 7-18.

As its name implies, this option will ensure that all data that is output is first escaped before being displayed on the page. The first option on the list—Standard Report Column—does not have nearly as obvious a name. When selected, data in this column will not be escaped and rendered on the page as is. If malicious code exists for data in this column, it will be executed as the page renders.

In keeping with the example, if the desired result is to render the image inline with the value in the ICON column, the Display As needs to be set to Standard Report Column. If the report is run now, the date in the ICON column is not escaped, and the image renders inline with the data, as shown in Figure 7-19.

Finally, the report works as desired because the icon renders just before the department name. However, since the ICON column is no longer being escaped, it is wide open to a XSS attack. If a user was able to set the value of Department Name to a snippet of malicious JavaScript—such as ACCOUNTING<script>alert('Hello'),</script>—that code will be executed, as shown in Figure 7-20.

Wrapping the entire CASE statement in apex_escape.html will of course escape the data returned from it, but the result will look exactly like Figure 7-17. This is clearly not what the developer intended because the images are still not rendering. In many instances, developers will simply just give up trying to secure this report the proper way and disable escaping on the ICON column. This does not have to be the case because there is a viable and secure alternative.

Instead of embedding the HTML code in the SQL, it is better to simply select the data and use the HTML Expression attribute at the column level to apply the HTML code needed. With this approach, the SQL query can be rewritten to simply return the name of the employee, department, and corresponding image, as illustrated in Listing 7-16.

Listing 7-16.  A SQL Query That Will Be Used to Display an Image but Contains No HTML

SELECT
  e.ename,
  d.dname,
  CASE
    WHEN e.deptno = 10 THEN 'green_flag.gif'
    WHEN e.deptno = 20 THEN 'red_flag.gif'
    WHEN e.deptno = 30 THEN 'grey_flag.gif'
    WHEN e.deptno = 40 THEN 'yellow_flag.gif'
    ELSE 'white_flag.gif'
  END icon
FROM
  emp e,
  dept d
WHERE
  e.deptno = d.deptno

Instead of including the img tag in the SQL, it will be placed in the HTML Expression attribute in the Column Formatting region. Column aliases surrounded by a # can be used to represent their corresponding column values from the SQL query. As shown in Figure 7-21, the img tag referencing the ICON column as well as a reference to the DNAME column is included in the HTML expression.

Now, when the report is run again, the result that the developer was after is finally achieved in a secure fashion. In a standard report, there is an option in the report attributes called Strip HTML. When selected, APEX will automatically strip any HTML tags from the original column value fetched from the database. HTML that is entered via the HTML expression of column link will still be applied. Since this option is enabled by default, APEX will automatically remove the <script> tags before rendering the data. Thus, only part of the malicious string that was added is displayed in Figure 7-22.

In an interactive report, the <script> tag is not removed from the HTML expression; rather, it is escaped with the rest of the string, as shown in Figure 7-23.

Escaping data programmatically can be tricky because it is not as simple as always passing any column that will be displayed through a function. Take adequate time to secure output rendered from either SQL or PL/SQL regions in your application by ensuring that the Display As attribute is set to escape the data and any HTML is entered in the HTML Expression attribute.

Escaping Regions and Items

Similar to report columns, a number of other components in APEX can have their output escaped, so as long as the proper attribute is set. these component types include HTML regions, some item types, cookies, and frames. Be sure to ensure that any output rendered within these components is escaped.

First, consider HTML regions. There are actually three subtypes of HTML regions: HTML Text, HTML Text (with shortcuts) and HTML Text (escape special characters). These regions are often used simply as containers for items, and text is rarely entered in them, so the need to ensure that their contents are escaped is typically low. Given that more recent versions of APEX automatically escape any value stored in page items, the need to worry about HTML regions is even lower.

However, if the HTML region contains reference to an application item using the &ITEM. syntax, then the “HTML Text (escape special characters)” region type should be selected so that any malicious code in the application item is escaped rather than executed.

Next, it is important to reiterate how page items differ from application items when rendered on the page. APEX will automatically escape any output from a page item when it is referred to with the &ITEM. syntax. There is no option to enable for this to occur; it happens automatically. Application items, on the other hand, do not automatically get escaped when referred to with the &ITEM. syntax. For example, developers will often reference a page or application item in a region title so that it reflects the name of the customer being edited. If the name of the customer is stored in a page item, then no additional precautions need to be taken. However, if the name of the customer is stored in an application item, be sure to escape that value before assigning it to the application item so that any malicious code is neutralized.

Speaking of items, there are a small number of item types—Checkboxes, Display Only items, Radio Groups, and Text Fields with Autocomplete—that contain an attribute called “Escape special characters.” When enabled, the value of the item itself or of the item values in the corresponding lists will be escaped. This attribute is enabled by default and should be always be left that way unless there is a specific need to render an item or items in a list using HTML tags. If this is the case, then great care should be taken to ensure that those vales cannot be tampered with or altered.

Protecting Cookies

Cookies are nothing more than text files that a browser reads and writes to in order to maintain a value-attribute pair that can persist longer than a single page view. Developers have long used cookies to store session-specific information so that as a user goes from one page view to another, the value of the cookie can be used to determine whether their session is valid. Many web sites, regardless of their underlying technologies, also use cookies in this manner.

APEX uses cookies for both the application development environment as well as any user-developed applications. The APEX development team made it a point to store very little information in the cookies because any data stored there is not very secure. This best practice is a good model for any developer or any web application. Chapter 9 discusses how APEX uses cookies in much more detail.

Most APEX application developers elect to use preferences to store user-specific values that need to persist across sessions for a number of reasons. First, preferences are automatically stored in the database. This way, there is no local trace of them and their associated values are not left on the user’s workstation. Second, since preferences are stored in the database, they are available no matter which physical device the user uses to log in. Cookies, on the other hand, are available only when the user is using the application from the same physical device. And lastly, there is a robust set of APIs that APEX provides that easily is called to manage preferences. This set of APIs can also be used by developers to set default preferences, for instance.

Should cookies still be needed, there are a couple of security concerns that need to be reviewed. One of the attributes used when setting a cookie is called HttpOnly. When enabled, this attribute prevents any client-side script from accessing the values of the cookie, essentially blocking it from a potential XSS attack. The downside is that this value is also blocked from legitimate code. Thus, if sensitive, session-based information needs to be stored in a cookie, the HttpOnly parameter should be enabled.

Unfortunately, the version of owa_cookie that comes with APEX does not support the HttpOnly parameter. The attribute is simply not part of the SEND procedure, making it impossible to enable. Oddly enough, the version of owa_cookie that is part of Fusion Middleware does support this option. Short of creating a custom version of owa_cookie that supports HttpOnly or copying the version that ships with Fusion Middleware, there is no cut-and-dry solution to setting cookies with the HttpOnly attribute via APEX until the version of owa_cookie is updated to support it.

Second, when setting cookies in APEX, the secure flag should always be set to Y. By enabling the secure attribute, it will ensure that the web session is running over SSL before the cookie is allowed to be set. If the session is running over just HTTP and is not encrypted, the cookie will simply not be set because there is a risk that someone would be able to see the value passed back by sniffing the network traffic. For this to work, the associated APEX web listener needs to be configured to work with SSL first. You can find more details about secure cookies in Chapter 14.

Frames

As mentioned in Chapter 6, there is a new setting in APEX 4.2 called Embed in Frames. Depending on the setting, this attribute controls whether an APEX application will be allowed to run within an iFrame. A specific type of XSS attack called clickjacking attempts to trick the user into clicking one link, when in reality they are clicking a malicious link set up by the malicious user. The Embed in Frames option is designed to prevent such an attack from occurring. You can find details of how to securely set Embed in Frames properly in Chapter 6.

Authorization Inconsistencies

One of the most common security vulnerabilities in APEX applications is when a developer forgets to secure a page or group of pages from unauthorized users. Oftentimes this occurs as an application grows in size and sophistication. For example, a simple application based on a few tables may at some point require a couple of pages used to manage the application itself. These pages contain forms on somewhat sensitive data. To protect users from accessing these pages, the developer may add an authorization scheme to the tab that leads to the admin pages but forget to protect the page itself. Thus, if a malicious user tampers with the URL and happens to enter the ID of one of the administrative pages, he will be able to run those pages as if he were an admin user.

This type of vulnerability in APEX is known as an authorization inconsistency. An authorization inconsistency can be defined as when a component—such as a list entry, tab, or process—is restricted by an authorization scheme but the page or other component that it is associated with is not. In some cases, both the component and target page may have different authorization schemes. This may be acceptable, depending on the criteria of each scheme. Also, any on-demand process that does not have a corresponding authorization scheme is at risk because a malicious user could reference and execute that process via the URL.

Keep in mind that once a user authenticates to an APEX application, that user can tamper with the URL and change the current page and/or pass values to attempt to set items or execute processes. Therefore, if there is not adequate protection on the target pages or components, a malicious user may gain access to them.

Authorization inconsistencies are difficult to protect against because there is no easy way or single report to determine whether and where they exist within APEX. In fact, they tend to be more common in larger, more sophisticated applications that have been developed by more advanced developers because the sheer size and complexity of such applications is ripe for these types of vulnerabilities. Fortunately, tools like Enkitec eSERT has such a report, as shown in Figure 7-24.

Once eSERT identifies any potential authorization inconsistency, it is up to the developer to inspect and either mitigate the risk by securing the corresponding component or determine that there is no risk based on the specific instance.

Page and Item Protection

Consider a report based on the simple SQL statement SELECT * FROM emp WHERE deptno = 30. When the report is run, only those employees in department 30 will be returned, as shown in Figure 7-25.

This report also has a link to a form that allows the user to edit any of the records returned in the report. After clicking the edit link for the user WARD, there is a new URL displayed in the browser, as shown in Listing 7-17.

Listing 7-17.  The URL When Editing the Record for WARD from the EMP Table

http://server/apex/f?p=149:2:233212876525::::P2_EMPNO:7521

This URL—and the link that leads to it—uses APEX’s ability to pass a value-attribute pair through the URL. In Listing 7-17, the item P2_EMPNO is passed the value 7521 using the seventh and eighth positions in the parameterized string. When page 2 loads, the automatic row fetch process will incorporate the value of P2_EMPNO when it fetches a row to be displayed on the form.

If a malicious user took note of how the mechanics of processing this value worked, he may be inclined to pass a different value to P2_EMPNO through the URL. If the malicious user changed the value from 7521 to 7782 and submitted that URL to the page, APEX would in turn fetch the record for CLARK, as shown in Figure 7-26.

How would the user guess which value was used? It all depends. Perhaps the IDs are nothing more than employee IDs, which are easy to find by other means. Or perhaps the IDs are all sequential, so that all a user would have to do is take theirs and add or subtract 1 to see someone else’s record. It is even possible that the malicious user was skilled enough to use an automated tool, such as URL Flipper, to quickly cycle through many IDs with the tap of a key. It really doesn’t matter how the user found data they were not supposed to see or how long it took for them to find it. At the end of the day, there was a data breach that could have easily been prevented.

APEX provides a feature called Page Access Protection, which is part of APEX’s Session State Protection mechanism. When enabled, this feature will prevent a malicious user from manipulating the values passed via the URL. It does this by appending an additional checksum to the end of the URL. If the any portion of the URL is modified, then the checksum will no longer equal what the APEX engine expects, and an error message will be displayed, as shown in Figure 7-27. Chapter 6 discusses Page Access Protection and its different options.

When running an application and logged into the APEX development environment, a more detailed error message will be displayed when a session state protection violation occurs, as shown in Figure 7-28. There is little risk here, even though the error message includes specific details about the PL/SQL call stack and checksums, because this will occur only when the user is also logged in as a developer.

Enabling Page Access Protection essentially puts a “shield” around a specific page, protecting it based on the level of protection applied. In the case of the Arguments Must Have Checksum setting, any attempt to set values while accessing that page will require a proper checksum as part of the URL. If the checksum is modified or absent, then they page will simply not render, and an error message will be displayed.

What is deceiving to many developers is that Page Access Protection does nothing to directly protect the individual items on that page. Rather, it simply determines what level of security is applied when a user attempts to access that page. Consider this example: if an application had page 1 set to Arguments Must Have Checksum and page 2 set to Unrestricted, a clever, malicious user would be able to set the value of an item on page 1—or any other page, for that matter—by passing its name and value to page 2. The fact that page 1 is protected at the page level is irrelevant and does not protect against any individual item on page 2 from being set from the URL.

Fortunately, there is a feature that prevents such an attack from occurring: Item Protection. Item Protection is part of APEX’s Session State Protection mechanism and is applied at the item level. Item Protection can be set to five values. The number of settings available depends on whether Session State Protection is enabled at the application level. If it is disabled, then only Unrestricted and “Restricted—may not be set from browser” will be available. If it is enabled, then all five options will be available. The details of each option are outlined here:

• Unrestricted: When set to Unrestricted, item values may be set either by using a form or by passing values through the URL. This is the default setting when creating new items.
• Restricted—may not be set from browser: As the name of this option implies, values for the associated item cannot be set from the browser—either by using a form or by passing a value through the URL. This option can be associated only with items of type Display Only (save state = no), Text Field (Disabled, does not save state) and Start and Stop Grid Layout.
• The next three options are available only when Session State Protection is enabled at the application level. All of them require that an additional checksum be present when attempting to change the value of the item. The only difference is the scope at which each option is applied.

• Checksum Required—Application Level: The value of the item may be set via the URL provided that a checksum common to the application is passed along with it.
• Checksum Required—User Level: The value of the item may be set via the URL provided that a checksum common to the user is passed along with it.
• Checksum Required—Session Level: The value of the item may be set via the URL provided that a checksum common to the session is passed along with it.

While the difference between the latter three options is subtle, only the last one, Checksum Required—Session Level, should be used because it offers the most security, preventing the same checksum from being used in a different session by any other user. Always enabling Item Protection on hidden primary key items is a good best practice to adopt. This way, a malicious user cannot modify these values by passing them through the URL of a page that is not properly protected.

Unfortunately, enabling Item Protection on items that are modified through an Ajax call will result in an error. The APEX engine cannot distinguish how a value was changed. It can tell only if a value was changed, based on the current and expected checksum. Thus, if a value is altered by an Ajax call, the checksum that APEX expects will not match what was originally sent with the page. In this case, it may be necessary to disable Item Protection in order to preserve the intended functionality. When this is necessary, it is recommended you provide an additional layer of validation via an APEX validation to ensure that the value passed in is, in fact, a legitimate one.

Virtual Private Database and Secure Views

Enabling session state protection is a critical step in securing any APEX application because it prevents malicious users from tampering with the URL and manipulating item values. However, session state protection is but one layer in a multilayered approach that should be used to secure the application data. Also important to consider is that while session state protection does offer adequate protection for APEX applications, it does nothing to protect applications that access the same data using a technology other than APEX.

Chapter 11 discusses how to use a standard Oracle view combined with an application context to secure what data is available to the application. These “secure views,” as they are called, are used in the place of any reference to a table in the APEX application. The security embedded in the view automatically limits what data is exposed through the application without having to rely on the developer to add a sophisticated WHERE clause to each query. The benefit of applying this security at the data layer is that it will be available regardless of what technology is used to access it. This allows for a more centralized data security model to be built, which is easier to manage over time.

Chapter 12 expands on the idea of securing data at the database but does so using Virtual Private Database (VPD). VPD is a no-cost supported feature of the Oracle Database Enterprise Edition. It can be managed either through Oracle Enterprise Manager or via SQL*Plus and is completely compatible with APEX applications. VPD essentially dynamically rewrites the WHERE clause of a query before it executes, based on rules specified in a function. It is also more robust and offers more features than a secured view.