Wednesday 4 January 2017

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

2 comments:

Unknown said...

Hello, i face a problem with lov. I have used at older apex version skillbuilders superlov for showing images. I call the images procedurally with 'apex_util.get_blob_file_src'. Superlov have a option at security section for escape or no special characters. when i choose no, images are showing. But superlov have many issues with 5.1 so i try to disable it from our application. All others solutions(lov plugins) there are not have security option for allow or not special character. So there are a opposite procedure to disable the 'escape special characters'?

Scott Wesley said...

Hi,

I get what you mean, though I haven't attempted the SuperLOV on 5.1 yet.

If you check through the PL/SQL of these plugins, you'll find they will be escaping the data at some point. It's likely you'd be able to figure out how to poke a hole for your scenario.

I found this once where I couldn't substitute in a value for a parameter. It was just a matter of wrapping an obviously named supplied API around the incoming parameter. I may have blogged (or have a draft) on this already.

You may even find yourself adding a parameter to the plugin so you can control this as per use.

And for the trifecta, you could blog about your experience ;p

I already advise moving plugin PL/SQL to a package.
The PL/SQL is never as scary as it first looks, I don't think one should fear potentially improving the code while they're there. Even Uncle Bob says so.