Interactive Report Column Custom Formatting

The question was posed, how to achieve custom formatting of a column if the available Format Models don't suffice? In standard SQL reports, it's not such a problem as you can simply use the "HTML Expression" field.

Well here's one way to do it, use some jQuery in a dynamic action. While this may seem really counter-intuitive, it's actually both beautifully simple and reliable after you finish considering all the other available options. Plus, that's what this blog is all about remember (when it's not being abandoned for years).

Create the dynamic action called something like "Format Report" triggered "After Refresh" on the report region. Also ensure "Fire On Page Load" is checked. The action is to execute Javascript like the following (this example will give you a trailing "%"):

$('td[headers="MY_COLUMN"]').append('%');

UPDATE: Or better yet...

$('td[headers^="MY_COLUMN"]').text(function(index,text){return text+'%'});

After teething problems with the first use case, we figured out you must have a Region Template with id="#REGION_STATIC_ID#". Also we adapted the jQuery to better support several other IR features (as seen in the second option above). Leave comments if you can build on this any further.

The advantage here is you don't have to modify the query and you can put all sorts of additional formatting in the one place without interfering with the existing functionality of the interactive report.

Here's another possible solution. The iFormat™ deluxe model. While both effective and platform independent, mounting and unmounting required to apply the formatting may prove somewhat arduous for the users after a while.

XSS and Apex

XSS = X(cross) Site Scripting. This is where user entered data contains some javascript that will be later inadvertently rendered and executed by another users browser. The javascript then sends session information back to the attackers external server. This information could contain cookies that can be used to steal the user's session.

Atlassian and the Apache team (who use Atlassian services) recently got hacked big time thanks in part to XSS vulnerabilities in their site. Details can be found here... https://blogs.apache.org/infra/entry/apache_org_04_09_2010 (it's a great read).

There is no simple solution to protect against XSS, but here's an article which demonstrates a generic method that web applications can use to avoid XSS from the client side. It's known as HTTPOnly cookies. This is very good news, it mentions that Firefox has a bug that doesn't implement HttpOnly correctly, but this has since been fixed... http://www.codinghorror.com/blog/2008/08/protecting-your-cookies-httponly.html

HTTPOnly is sufficiently supported by 99% of mainstream browsers (and most importantly, those in our SOE). Opera and Safari support is unconfirmed, but I'd assume they would have addressed this by now. I'd expect to see more support for HTTPOnly tighten as it's such a valuable tool to protect cookies.

I've been aware of XSS dangers for years, but only since this Atlassian incident was I curious to test the extent of the risk on Apex applications. Apex does not currently use HTTPOnly cookies, I'll investigate adding it. Apex does use the most standard method of setting cookies, which is promising as it's best supported by the browsers. We just need to figure out how to flip the HTTPOnly flag on the server somehow to provide another layer of protection for our users/applications. Apex is also wide open to session stealing based on cookie information, so please keep this in mind. Don't allow unsanitised user data to be rendered in the browser. This is absolutely critical for any application which is going to be made available to both the public and privileged users.

Subversion Autodeploy post-commit Hook

Since the apache user is non-interactive, it's difficult to get a post-commit hook to trigger an svn update elsewhere on the system. You can either temporarily make the apache user interactive, or perform the checkout in the hook one time. For the latter option, empty the deployment directory and update the post-commit hook with this command...
svn checkout file:///path/to/svn/project/ /path/to/project/dev/deployment --non-interactive
Now make a trivial change and perform a commit. Check that the project has been deployed. Change the post-commit hook to this...
svn update /path/to/project/dev/deployment --non-interactive

It's All Text!

Every APEX developer needs to know about a handy firefox extension called It's All Text!. Install this and it will allow you to seamlessly edit any textarea in an external editor. The default file extension applied to such files can changed to ".sql". So if your editor handles syntax highlighting based on file type, you're in business. I use SciTE and it all works very well.

It's almost like having a real IDE. In fact, with some tweaking this extension could do a whole lot more for APEX developers. I'm thinking meaningful file naming/directory structure based on the structure of the application and SVN integration at least. APEX page save error messages sent back to the text editor for finding line numbers (SciTE can handle this very easily, if the APEX page save error message was dumped into a file, then SciTE can be configured to pick that text up when a hotkey is pressed and errors with line numbers will be linked to the source code). This might have to be a little side project.

To change the default file type right click the "edit" button, select "Edit with new extension...", type ".sql" and check "Save for future use".

I don't have to explain how simple and effective this little tool is. Just install it and wonder how you ever lived without it!

On a side note, there is a similar extension good for editing HTML regions called Write Area.

Haunted by Headers

Had an interesting problem today tracing the origin of some HTTP headers. An application which uses mod_plsql (non-APEX) was constantly returning some troublesome header information which we needed to remove, particularly when a certain procedure was called. I searched every where in the OAS interface, but none of the settings seemed to be applicable. Eventually I found the culprit was an apache declaration in dads.conf. Why it was there I can only speculate, not having built the application.

On the up side, I can now clarify some more of the behaviour of the undocumented htp.init procedure. During the process of tracing the problem, I attempted to use htp.init to suppress the headers from showing up when calling a particular procedure. Apparently this isn't how it works. The application server still amends header information to the response before it is sent.

Building a Scraper Using UTL_HTTP

Someone set me the task of embedding pages from another one of our online applications into regions of an APEX app for the purpose of maintaining a single navigation and look and feel. My first thought was that it can't be done. As far as I was aware you couldn't perform a GET request from PL/SQL (I'd breifly checked in the past and that was the conclusion I'd arrived at). So instead I quickly threw something together in PHP that scraped the application and then used str_replace to embed additional style/navigation and achieve a pretty good compromise. It was quick and dirty, and getting it to work on the PHP4 version that comes with APEX was a bit of a challenge. It wasn't really an ideal solution, so I kept digging and found the UTL_HTTP package.

It's not quite as simple as calling a function that returns the page content, but it's possible. I followed the documentation as well as an O'Reilly example. The simple package I built doesn't handle proxies or authentication, but it does do a better job at handling large pages as CLOB data. I've pulled out the exception handling to make it simpler to understand. Refer to O'Reilly's example when you are ready for that.

CREATE OR REPLACE PACKAGE WEB_SCRAPER AS
 FUNCTION HTTP_GET_PAGE(p_url in VARCHAR2) RETURN CLOB;

 FUNCTION DOCUMENT_BODY(p_document in CLOB) RETURN CLOB;

 FUNCTION HTTP_GET_PAGE_BODY(p_url in VARCHAR2) RETURN CLOB;

 PROCEDURE PRINT_CLOB(p_content in CLOB);
END WEB_SCRAPER;
/

CREATE OR REPLACE PACKAGE BODY WEB_SCRAPER AS
 FUNCTION HTTP_GET_PAGE(p_url in VARCHAR2) RETURN CLOB AS
   /* Performs a simple HTTP GET request and returns the content */
   l_request           Utl_Http.req;
   l_response          Utl_Http.resp;
   l_content_buffer    VARCHAR2 (32767);
   l_page              CLOB;
 BEGIN
   DBMS_LOB.createtemporary(l_page, FALSE);

   l_request := Utl_Http.begin_request (url => p_url, method => 'GET');

   Utl_Http.set_header (r => l_request, NAME => 'User-Agent', VALUE => 'APEX-Scraper/0.1');
   l_response := Utl_Http.get_response (r => l_request);

   BEGIN
     LOOP
       UTL_HTTP.read_text(l_response, l_content_buffer, 32767);
       DBMS_LOB.writeappend (l_page, LENGTH(l_content_buffer), l_content_buffer);
     END LOOP;
   EXCEPTION WHEN Utl_Http.end_of_body THEN
     NULL;
   END;

   Utl_Http.end_response (r => l_response);
   RETURN l_page;
 END HTTP_GET_PAGE;

 FUNCTION DOCUMENT_BODY(p_document in CLOB) RETURN CLOB AS
   /* Return everything between the body tags */
 BEGIN
   RETURN REGEXP_REPLACE(p_document,'^.*<[[:space:]]*BODY[^>]*>(.*)<[[:space:]]*/[[:space:]]*BODY[[:space:]]*>.*$','\1',1,0,'imn');
 END DOCUMENT_BODY;

 FUNCTION HTTP_GET_PAGE_BODY(p_url in VARCHAR2) RETURN CLOB AS
   /* Convienience function combining DOCUMENT_BODY and HTTP_GET_PAGE */
 BEGIN
   RETURN DOCUMENT_BODY(HTTP_GET_PAGE(p_url));
 END HTTP_GET_PAGE_BODY;

 PROCEDURE PRINT_CLOB(p_content in CLOB) AS
   /* Use this instead of htp.p which only supports Varchar2 */
   l_buffer_size Number := 8191; --Can't always use 32767. See bug #5896994
   l_clob CLOB := p_content;
   l_chunks Number;
   l_cloblen Number;
 BEGIN
   l_cloblen := dbms_lob.getlength(l_clob);
   l_chunks := floor(l_cloblen / l_buffer_size) + 1;
   FOR i IN 0 .. l_chunks LOOP
       htp.p(dbms_lob.substr(l_clob, l_buffer_size, (i * l_buffer_size) + 1));
   END LOOP;
 END PRINT_CLOB;

END WEB_SCRAPER;
/

Eliminating Bugs

Fantastic article...
http://duartes.org/gustavo/blog/post/of-aviation-crashes-and-software-bugs

Gustavo talks about eliminating software bugs by eliminating the possibility to create them.

I think Apex does a pretty good job on this front, as long as you don't start putting "execute immediate" statements in PL/SQL blocks. Not so good at preventing XSS though. Although it does have the "Strip HTML" option set by default in reports, it's over zealous and you often have to switch it off because it can't handle "<" and ">" symbols in legitimate text (really it should just convert them to harmless &lt; and &gt;). Normal display as text items have the "escape special characters" option, but this has the same problem.

In fact, Oracle already has the function required to render user data safely... HTF.escape_sc. It's probably a good idea to wrap every item in the report select statement with this, or create a view that serves this purpose and only use the view when displaying user data. I've toyed with using the function to sanitise user input, but the problem there is each time you save the same form, the escape sequences get longer and longer (ie. & becomes &amp; becomes &amp;amp;).

They should smarten that function up and include a default input sanitisation process for every form, then permit you to downgrade it's paranoia level for exceptional situations. Allowing for exceptional circumstances goes against what the article is saying. But the author doesn't discuss the downsides and tradeoffs of a Nazi development environment. I don't think it's possible to have an environment that completely guards against writing buggy code. It's just another impossible ideal. But there's nothing wrong with making steps towards it.