Escape Special Characters APEX Demo

A few weeks ago I wrote more detail than expected regarding escaping of special characters.

I thought I'd add a simple demonstration, for reference.

Consider the following query, with variations of escaped column output.

with data as 
  (select q'[G'day,]'||chr(10)
           ||'Scott<strong>loves</strong>'
           ||'<br>APEX<script></script>' as string 
   from dual)
select 
 -- UI default
 string dflt
  -- where no tags expected
 ,apex_escape.html(string) protected
  -- good for most things
 ,apex_escape.html_whitelist(string) whitelisted 
  -- replace line feeds with HTML line break. Could use chr(13)
 ,replace(apex_escape.html_whitelist(string),chr(10),'<br>') protected_custom
 -- UI naughty
 ,string naughty
from data

The major difference being unescaped output looks like
"G'day,
Scott <strong>loves</strong><br>APEX<script></script>"

While escaped output looks like
"G'day,
Scott <strong>loves</strong><br>APEX<script></script>"

It determines how the browser will interpret these tags and display them to the end user.

Note that the first field called "DFLT" is using the default setting, therefore escaping special characters.
All other four columns do no explicitly escape these characters, deferring protection to the SQL.

Colum Attributes across four columns

The output looks like the following. The first line may look similar to any time you attempt to use APEX_ITEM in a classic report without turning this flag off.

Sample output, using template: Value Attribute Pairs - Column

So depending on what you're trying to display, you might need a particular combination of code / settings.

1. Default - default APEX behaviour, no column settings adjusted.
2. Protected - uses apex_escape package to do the same job as declarative attribute
3. Whitelisted - Certain markup tags are allowed, but all others are still escaped
4. Protected custom - often I want to replace line feeds/carriage returns in data with HTML line breaks. This combination facilitates the best of both worlds
5. Naughty - avoid unticking Escape Special Characters attribute without protecting data within SQL. This is enabled Cross Site Scripting (XSS)

The naughtiness can be demonstrated by adding alert("Hello universe") between the <script> tags. The unescaped column will mean the browser will render an alert when the page renders.

This is bad because instead of an alert, it could be some malicious JavaScript.

Further security tips can be found at Recx.
APEX-SERT is also 5.0 ready.

APEX attributes for Escaping Special Characters

A relatively common on the forums is regarding the escaping of special characters in reports, but it seems the developer isn't always sure what is actually happening and how to how to search for it.

It seems I've had this on my "to blog" list since April 2015, but now that 5.1 has been released, it seems more people are coming out to leave 4.x can't work out where the Standard Report Column option is.

APEX 4.x Display As attribute

This was required when HTML was present in the query, either to add tabular items manually using apex_item, or to style data (though you should use HTML Expression instead)

Example of special characters being escaped

For instance, if you've written a query like so

SELECT APEX_ITEM.CHECKBOX2(1, empno, 'CHECKED') chk, ename
FROM   emp
ORDER BY 1

And are only seeing the HTML code in your column output

<input type="checkbox" name="f01" value="7369" CHECKED />

Then you need to Escape Special Characters, now found in the Security section of the column properties as a Yes/No option.

APEX 5.0 Escape Special Characters attribute

This is defaulted to Yes to help protect from cross-site scripting (XSS), a common security concern in the web world where data entered by users is stored in the database, then when rendered it can be interpreted as HTML code.

Set to No to allow your data to be rendered as you may expect. 
Note that in the 5.0 component view this is still referenced as Display As - Standard Report Column.

The change in terminology is documented in the 5.0 release notes

Report column property naming differences

Please note that if setting this attribute to no, you should still make efforts to protect your applications by escaping data where possible. For example, if I wanted to replace all carriage returns with the HTML line break, you can still escape your data then add HTML content.

replace(apex_escape.html(card_title), chr(10), '<br>')

You could probably do a variation of this using apex_escape.html_whitelist

If you're combining two fields, separated by the line break:

apex_escape.html(phone)||'<br>'||apex_escape.html(email)

then you might as well use HTML Expression and keep your data/UI layers separate.

HTML Expression attribute

Check out the open source project APEX-SERT to help find potential security concerns with your Oracle APEX applications.

See escaping examples in APEX reports here
http://www.grassroots-oracle.com/2017/01/escape-special-characters-apex-demo.html

Escaping wildcard searches

What if the character you want to search for is one of Oracle's wildcards?

Today I wanted a list of all Apex schema related synonyms, but I wanted to excluded any APEXLIB stuff.

-- Find actual object names for apex objects
select synonym_name, table_name
from dba_synonyms
where synonym_name like 'APEX/_%' escape '/'
and table_owner like 'APEX/_%' escape '/'
and owner like 'APEX/_%' escape '/'
order by synonym_name;

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
APEX_APPLICATION               WWV_FLOW
APEX_APPLICATION_FILES         WWV_FLOW_FILES
APEX_APPLICATION_GLOBAL        WWV_FLOW_GLOBAL
APEX_COLLECTION                WWV_FLOW_COLLECTION
APEX_COLLECTIONS               WWV_FLOW_COLLECTIONS
APEX_CUSTOM_AUTH               HTMLDB_CUSTOM_AUTH
APEX_INSTANCE_ADMIN            WWV_FLOW_INSTANCE_ADMIN
APEX_UTIL                      HTMLDB_UTIL
...

I know in times passed I've also wanted to search for all database objects that contain _ROLE_

select * from all_objects where object_name like '%\_ROLE\_%' escape '\';

Obviously it need not be a forward or back slash. If you don't want to confuse yourself when you also have '\' characters in your search term. A tilde is a favourite of mine.

select directory_path from all_directories where directory_path like '%\sales~_%' escape '~';

DIRECTORY_PATH
------------------------------------------
E:\oracle\sw10g\demo\schema\sales_history\

In fact, if you don't succeed the escape character with Oracle's wildcards (% or _), then you'll receive the following error:
ORA-01424: missing or illegal character following the escape character

Documentation on this feature can be found here.