Oracle APEX & Cross Site Scripting (XSS) - Part 2

In previous post, we covered Validating on Input as the first "preferred" line of defense against XSS Vulnerabilities. In this post, we will discuss Escaping on Output mechanism which basically helps addressing malicious XSS scripts that already exist in your application (Ex. malicious scripts injected directly into backend database, or user input submitted prior to implementing some XSS input validation mechanism), by making sure that such scripts won't be executed when they are rendered in the application, but will rather be rendered as plain text.
As I explained before, while Validating on Input can be sufficient in most cases, it is a good practice to bundle that with some sort of Escaping on Output mechanism in order to implement a concrete XSS solution.

For example, if you have First Name text field in Employee form and you populate it with something like:

"<script>alert("Your application is hacked!!"); </script>"

Then you submit your input (assuming you don't have some sort of XSS input validation implemented yet). The First Name will be stored in Employees table successfully. Now, if you have a report (interactive report or classic report) on Employees table that displays First Name as one of its columns (and it does not escape special characters), the JavaScript will be automatically executed upon rendering the report. So in this case, you will get a "Your application is hacked!!" alert when you run that report.

Now, in real XSS attacks, attackers won’t just inject an alert that indicates that your application has been hacked (well, unless they just want to scare you and brag about it), but they will actually inject some malicious scripts that can cause several types of damage, such as: hijacking user's session, hacking user's account credentials, accessing and stealing sensitive data, and much more. So, you want your application to render such scripts as plain text without being executed at runtime to mitigate such threat, which can be accomplished by making sure that your APEX application escapes special characters where needed.
In APEX, there are mainly three types of components that usually render user’s input data and you need to make sure they escape on output:

Text Items

APEX naturally escapes special characters when rendering values of text items, so you don't need to worry about text items in this case.

Display Only Items

APEX offers a native property to escape special characters when displaying values of Display Only items. Setting the Escape special characters property to Yes ensures that the item's value will be displayed “as is” without being executed at run time. For example, if you have a Display Only item with source set to static text:

"<script>alert("Your application is hacked!!"); </script>"

and you set Escape Special Characters property to Yes, the item's value will be rendered as plain text and it won't be executed:

However, if you set Escape Special Characters property to No, then you will get a “Your application is hacked!!” alert when the page that has that item is rendered.

Report Columns (Interactive and Classic reports)

Similar to Display Only items, APEX offers a native properties to escape special characters when displaying report columns. However, report columns can be a little tricky, as you might have certain columns that are meant to be clickable links or icons (Ex. Edit icon/link and download attachment icon/link). Such columns are expected to have some JavaScript that need to be executed. In such cases, you do not want not make these columns escape on output.

Classic Reports:

When you create a classic report, you can edit each column and set the Display As property as needed. In the context of XSS, we are mainly interested in two options:

o Standard Report Column: if you want underlying JavaScript/HTML to be executed when column is rendered (Ex. Edit, View, Download links/icons) when the column is rendered.

o Display as Text (escape special characters): if you want underlying JavaScript/HTML to be rendered as plain text (i.e. not executed).

Interactive Reports:

When you create an interactive report, you can edit each column and set the Display As property as needed. In the context of XSS, we are mainly interested in three options:

o Standard Report Column: if you want underlying JavaScript/HTML to be executed when column is rendered (Ex. Edit, View, Download links/icons) when the column is rendered.

o Display as Text (escape special characters): if you want underlying JavaScript/HTML to be rendered as plain text (i.e. not executed).

o Remove HTML and escape special characters: if you want to completely strip out JavaScript/HTML when column is rendered.

Now, you know what properties should be utilized when you design and develop your APEX applications. I actually suggest that you add this to your code review checklist and best practices to make sure your applications stay XSS compliant. However, if you already have an APEX application in production, and you want to make sure that your application does escape HTML/JavaScript where required, you might want to consider writing some queries against APEX metadata to identify gaps. This should save you a lot of time and efforts compared to having to manually review all your APEX items and report columns, which can take forever especially for large APEX applications.

I will now provide some sample queries that you can build on to come up with a programmatic approach to identify and fix APEX items and columns that need to escape special characters. Again, these are just sample queries that you will likely need to adjust and tweak to meet your specific XSS requirements.

Display Only items

Step 1: Get a list of candidate items (i.e. items that currently do not escape HTML/JavaScript)

select to_char(item_id) item_id,application_id, page_id, page_name, region,item_name, label, display_as, item_source, item_source_type
from APEX_APPLICATION_PAGE_ITEMS
where application_id in (100) -- replace this with your desired application ID
and display_as_code in ('NATIVE_DISPLAY_ONLY') -- display only items
and nvl(ESCAPE_ON_HTTP_OUTPUT,' ') ='No' -- Escape Special Characters currently set to No
and nvl(condition_type_code,' ') <> 'NEVER' -- you may want to exclude inactive/unused items
order by 2,3,4,5,6,7

Step 2: Fix required items
Review this list of candidate items obtained by the query above and identify those that should be updated (i.e. should escape special characters). You can then update identified items with a query like this:

update  apex_050100.wwv_flow_step_items
set escape_on_http_output = 'Y' -- escape special characters 
where id in (111,222,333.... ); -- list of comma separated item IDs from query above 
Commit;

P.S. Replace apex_050100 with the correct schema name based on your current APEX version (this was coded and tested in APEX 5.1)

Classic Reports

Step 1: Get a list of candidate columns (i.e. columns that currently do not escape HTML/JavaScript)

select to_char(t1.region_report_column_id) id, t1.application_id, t1.page_id, t1.page_name, t1.region_name, t1.column_alias, heading,Regexp_replace(heading, '<.+?>') column_name, t1.COLUMN_LINK_URL, t1.COLUMN_LINK_TEXT
from APEX_APPLICATION_PAGE_RPT_COLS t1, APEX_APPLICATION_PAGE_REGIONS t2
where t1.application_id  in (100) -- replace with your desired application ID
and display_as_code ='WITHOUT_MODIFICATION' -- currently does not escape special characters 
and NVL(t1.column_is_hidden,'yes') = 'No' -- not hidden
and nvl(t1.condition_type,' ') <> 'NEVER' --only active columns
and t1.application_id = t2.application_id
and t1.page_id = t2.page_id
and t1.region_id = t2.region_id
and upper(nvl(t2.condition_type,' ') ) <> 'NEVER' -- only active regions/reports
order by 2,3,4,5,6,7

You can add more filters to narrow down the list of candidate columns by utilizing other metadata such as:

and COLUMN_LINK_TEXT is not null
or
UPPER(NVL(COLUMN_ALIAS,' '))  like '%LINK%'
or 
UPPER(NVL(HEADING,' '))  like '%ACTION%'
or 
UPPER(NVL(HEADING,' '))  like '%DELETE%'
or 
UPPER(NVL(HEADING,' '))  like '%EDIT%'
or 
UPPER(NVL(HEADING,' '))  like '%SELECT%'
     

Step 2: Fix required columns
Review the list of candidate columns obtained by the query above and identify those that should be updated (i.e. should escape special characters). You can then update identified columns with a query like this:

update APEX_050100.WWV_FLOW_REGION_REPORT_COLUMN
set display_as = 'ESCAPE_SC'  -- escape special characters
where id in (111,222,333.... ); -- list of comma separated column IDs from query above 
Commit;

Interactive Reports

Step 1: Get a list of candidate columns (i.e. columns that currently do not escape HTML/JavaScript)

select to_char(t1.column_id) id,t1.application_id, t1.page_id, t2.page_name, t1.region_name, t1.column_type, t1.column_alias,Regexp_replace(nvl(report_label,form_label), '<.+?>') column_name, column_link, column_linktext
from APEX_APPLICATION_PAGE_IR_COL t1 , APEX_APPLICATION_PAGES t2 , APEX_APPLICATION_PAGE_REGIONS t3
where t1.application_id in (129,159,169,299)
and display_text_as ='WITHOUT_MODIFICATION'-- currently does not escape special characters
and nvl(display_condition_type_code,' ') <> 'NEVER' -- only active columns
and t1.page_id = t2.page_id
and t1.application_id = t2.application_id
and t1.region_id = t3.region_id
and t1.application_id = t3.application_id
and t1.page_id = t3.page_id
and upper(nvl(t3.condition_type,' ') ) != 'NEVER' -- only active regions/reports
order by 2,3,4,5,6,7
     

Similar to Classic Reports, you can add more filters to narrow down the list of candidate columns returned by the query.

Step 2: Fix required columns
Review this list of candidate columns obtained by the query above and identify those that should be updated (i.e. should escape special characters). You can then update identified columns with a query like this:

update APEX_050000.wwv_flow_worksheet_columns
set display_text_as ='ESCAPE_SC' -- escape special characters
where id in (111,222,333.... );  -- list of comma separated column IDs from query above 
Commit;

Note 1: The queries above deal with APEX metadata, which can cause serious issues to your application if not handled with extra precaution. I suggest that you take backups of your APEX application and updated tables in case anything goes wrong.

Note 2: Page Designer IDE (introduced in APEX 5.0 and become the default APEX IDE since then) has made some changes to Display As property values for Interactive and Classic Reports. “Standard Report Column” and “Display as Text (escape special characters)” options have been removed and replaced by “Plain Text” along with a “Escape Special Characters” property under Security section that you can set to Yes or No (similar to Display Only Items). However, this is just a difference in how these options and properties are displayed in Page Designer, so if you switch back to legacy component view, you should see old options.
Oracle has nicely documented property naming differences between Page Designer and Component View, which can be found here.

Also, steps needed to switch from Page Designer to legacy component view (not recommended) can be found here.

Note 3: Radio Groups and Checkboxes also have Escape Special Characters property. However, since these items' text (i.e. options) usually do not come from user’s input directly but rather come from lookup data created by developers, I have not cover them in this post.

Oracle APEX & Cross Site Scripting (XSS) - Part 1

Cross Site Scripting (XSS) is a common security vulnerability that targets web applications by injecting malicious JavaScript or HTML into the application (or backend database). Attacker usually takes advantage of application’s poor input validation and submits malicious scripts as legit input (Ex. in a Description text field). The inputted scripts become dynamically included in application's web pages when they are rendered by victim user, allowing underlying malicious scripts to be automatically executed. This can cause several types of damage, such as: hijacking user's session, hacking user's account credentials, accessing and stealing sensitive data, and much more. 

Since Oracle APEX applications are usually exposed to the Internet, they are potential targets for XSS attacks. This does not imply that internal APEX application (i.e. on intranet) are not subject to XSS attacks. However, they require either an internal attacker, or an attacker who hacks into the network and application/database first, which makes it less likely to happen. In any case, the XSS remediation tips provided in this post apply to both public-facing and internal APEX applications similarly.

Developing a web applications that fully address XSS vulnerabilities can be very tricky. It requires making sure that developers follow certain best practices during development cycle, and that applications regularly go through security reviews to uncover any XSS candidates before code gets deployed to production. It is a bit more challenging to address XSS vulnerabilities in already existing APEX applications especially large ones as you will likely need to apply the fix to hundreds or thousands of candidate items, and make sure that whatever you do does not break existing functionality.

My preferred strategy for tackling XSS is to look at the problem from both sides: 1) make sure that user cannot submit input that contains XSS scripts (a.k.a. Validate on Input), and 2) make sure that if for any reason XSS scripts make their way into the application, they won't be executed when rendered by the application (a.k.a. Escape on Output).

Validating on Input

This is where you make sure that user’s attempts to submit input that contains malicious XSS scripts are blocked and rejected by the application right away. This can be relaxed so that it seamlessly encodes HTML and JavaScript content and store user's input in a “safe format” without notifying user. However, I personally go that route only if user strictly ask for it. I think that making the application completely reject malicious XSS scripts is more secure, and it prevents accidental user’s data alteration and corruption.

Typically, APEX applications allow users to enter text input through the following text items:
o Text Fields
o Text Areas
o Rich Text Editor

APEX provides a native property called Restricted Characters that validates content of these text items upon page submission. The property offers the following options:

Whitelist options:

o Whitelist for a-Z, 0-9 and space

Throws standard error:
contains special characters. Only the characters a-Z, 0-9 and spaces are allowed.

Blacklist options:

o Blacklist HTML command characters (<>")

Throws standard error:
contains <, > or " which are invalid characters.

o Blacklist &<>"/;,*|=% and --

Throws standard error:
contains one of the following invalid characters &<>"/;,*|=% or --

o Blacklist &<>"/;,*|=% or -- and new line

Throws standard error:
contains one of the following invalid characters &<>"/;,*|=% or -- or new line.

While this property can be helpful, it might not work in all cases. For example, if you want to allow greater than > and less than < operators in certain text fields, or if you want to allow certain "safe" HTML tags for text formatting such as: <b> , <u> , <i>…etc. in such cases, this property won’t help, so you should consider developing a custom validation solution.

Once you create your custom validation solution, you then need to apply it to all applicable text items, preferably through a script to avoid having to do that manually especially if you are working on an already existing "large" application, not a small application or a brand new application.

Creating a custom validation solution

You can create a simple function that takes two parameters:
o Text item value (P_TEXT)
o Text item label (P_ITEM_LABEL). This can be optional as it will only be used to print targeted validation error messages that reflect item’s label

Then, write some PL/SQL to parse out P_TEXT, and validate against a set of whitelisted tags.

Why whitelisting, not blacklisting? Well, blacklisting requires you to identify all tags that can be malicious, which you might not exactly know right away. Also, as hackers are getting better (maybe I should say worse) every day, new tags or combination of tags might become malicious which requires you to update your blacklist frequently. If you do not update your blacklist immediately, your application will be exposed to XSS attacks. On the other hand, it should be much easier for you (and users) to identify a “short” list of tags that should be allowed in the application. This list rarely needs to be updated, and having to whitelist new tags adds another layer of security if you create an approval process to handle whitelist updates.

In order to make this flexible, let's create a whitelist table so you can add/remove tags through DMLs instead of code changes.

Create whitelist table:

CREATE TABLE TBL_XSS_WHITELIST AS
(TAG_ID NUMBER
,TAG VARCHAR2(500)
);

Insert few tags into the table, for example:

INSERT INTO TBL_XSS_WHITELIST values (1,'<i>');
INSERT INTO TBL_XSS_WHITELIST values (2,'</i>');
INSERT INTO TBL_XSS_WHITELIST values (3,'<b>');
INSERT INTO TBL_XSS_WHITELIST values (4,'</b>');
INSERT INTO TBL_XSS_WHITELIST values (5,'<u>');
INSERT INTO TBL_XSS_WHITELIST values (6,'</u>');

Now let's create the validation function:

CREATE OR REPLACE FUNCTION validate_text_xss (
   p_text      VARCHAR2,
   p_label    VARCHAR2 DEFAULT NULL)
   RETURN VARCHAR2
AS
   l_string        VARCHAR2 (4000);
   l_lt_cnt        NUMBER;
   l_gt_cnt        NUMBER;
   l_start_idx     NUMBER;
   l_end_idx       NUMBER;
   l_is_safe       NUMBER;
   l_return        VARCHAR2 (4000);
   l_err_msg       VARCHAR2 (4000) := 'contains prohibited JavaScript or HTML tags';
   l_label         VARCHAR2 (4000);
BEGIN


   IF ( p_text IS NOT NULL AND INSTR (p_text, '<') != 0 AND INSTR (p_text, '>') != 0 ) THEN

      --p_text is not null, and contains both < and > which might represent HTML or JS tags
      --validate string(s) between < and > against a whitelist


      -- get count of < sign in p_text
      l_lt_cnt := REGEXP_COUNT (p_text, '<');

      --scan through p_text and parse out each string between consecutive < and > signs
      FOR i IN 1 .. l_lt_cnt
      LOOP
         l_start_idx :=
            INSTR (p_text,
                   '<',
                   1,
                   i);
         l_end_idx :=
            INSTR (p_text,
                   '>',
                   l_start_idx,
                   1);

         l_string := SUBSTR (p_text, l_start_idx, l_end_idx - l_start_idx + 1);

   -- check if l_string contains both < and > signs, and has some text in between (which makes it a potential HTML/JS tag)
         IF (REGEXP_COUNT (l_string, '<') = REGEXP_COUNT (l_string, '>') AND REPLACE (l_string, ' ') != '<>')
   
         THEN
            -- validate l_string against your whitelist

            BEGIN
                  SELECT 1
                    INTO l_is_safe
                    FROM TBL_XSS_WHITELIST
                   WHERE REPLACE (LOWER (l_string), ' ') = REPLACE (LOWER (tag), ' ');

            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN

                  l_return :=
                     l_return || '"' || l_label || '"  ' || l_err_msg;
                  EXIT;
            END;
         END IF;
      END LOOP;
   END IF;

   RETURN l_return;
END validate_text_xss;
/

Now, in order to apply this function to all candidate text items, create a shared PL/SQL process that runs on every page. You can exclude unnecessary pages if needed.

In process source code:

DECLARE
   l_return   VARCHAR2 (4000);
BEGIN
   /* loop through APEX Text items on current APEX page*/
   FOR txt_item
      IN (SELECT item_name, label
            FROM APEX_APPLICATION_PAGE_ITEMS
           WHERE     application_id = :APP_ID         -- current APEX application
                 AND page_id = :APP_PAGE_ID           -- current APEX page
                 AND LOWER (display_as) LIKE '%text%' -- Text Field, TextArea, Rich Text Editor
                 AND NVL (condition_type_code, ' ') <> 'NEVER' -- active items
                 -- Add whatever filters suit your application's XSS requirements 
         )
   LOOP
      l_return := validate_text_xss (v(txt_item.item_name), txt_item.label);

      IF l_return IS NOT NULL
      THEN                      --an error message has been retrieved
         APEX_ERROR.ADD_ERROR (
            p_message            => l_return,
            p_display_location   => apex_error.c_inline_in_notification);
      END IF;
   END LOOP;
END;

You might want to make this process conditional so it only gets executed when user tries to submit input. You can do that by adding a SQL condition based on page request. For example:

lower(:REQUEST) like '%submit%'
OR lower(:REQUEST) like '%apply_changes%'
OR lower(:REQUEST) like '%save%'

And that's it! Now your application will validate user's text input against your preferred XSS whitelist. Any text that contains unwhitelisted JavaScript or HTML tags will be rejected and user will be notified via a user-friendly error message. You may want to create an additional “exclusions” table where you identify APEX items (or even pages) that you want to exclude from XSS validation, and adjust this query to filter out these items or pages.

I will cover Escaping on Output in my next post to avoid being lengthy.

Note 1: If you have text items created through PL/SQL code (i.e. using APEX_ITEM API), the shared process described above won't address these items since they won't be stored in APEX metadata tables. In such case, you need to adjust the code that processes these items, and add calls to the XSS validation function to validate corresponding text before submitting to DB.

Note 2: All code in this post is only sample code to demonstrate my thought process. You will likely need to build on it to come up with a solutions that fits your application. I plan to create an APEX plug-in when time allows to make this more usable and convenient for APEX developers.