APEX: Why can using HTML as part of SQL queries for Interactive Report be dangerous?

Original author: Pavel G
  • Transfer
Why can using HTML as part of SQL queries for Interactive Report be dangerous? Look at the screenshots! Careless use of HTML can lead to problems that are not obvious at first glance:



Problem 1: From the point of view of a business user, the filtered lines do not contain the word “default” (this word is contained inside the HTML tag)
Problem 2: When filtering by status instead of text that is clear to the user he gets an HTML expression that looks like gibberish to him.



Export also looks awful.
What is the best way to fix everything?


First, pay attention to the separation of the business logic layer and the presentation tier. There should be nothing in the business logic layer related to the display of information, especially HTML.

First, take a look at the original query in Interactive Report.



The v_cost_report view returns the status column as HTML. Using HTML in views is bad practice, as it makes it difficult to further use the view in other views and PL / SQL procedures, especially when trying to fetch or link to other tables using this column.
For the correct division of logic into layers, it is necessary to take the expression that returns HTML to the Apex level.

Before
CREATE OR REPLACE FORCE VIEW v_cost_report AS
SELECT  p.id,
        p.project,
        p.task_name,
        ‘’ status,
        p.assigned_to,
        p.status as status_desc,  
        p.cost,
        p.BUDGET
FROM eba_demo_ir_projects p;

After
CREATE OR REPLACE FORCE VIEW v_cost_report AS
SELECT  p.id,
        p.project,
        p.task_name,
        p.status_no status,
        p.assigned_to,
        p.status as status_desc,  
        p.cost,
        p.BUDGET
FROM eba_demo_ir_projects p;



Apex belongs to the presentation tier, so there are no such strict restrictions at this level. HTML in SQL queries can be used to a limited extent, however, it is best to avoid the direct use of such HTML returning columns in Interactive Reports. These columns should be hidden, and the results returned by them should not be shown directly, but using the “HTML Expression” property in other columns.
This means that we need 2 columns instead of one, the first for HTML and the second for text description.



In our example, the “Status” column returns a number (ID) that identifies the corresponding status. We use LOV (list of values) to show the text description instead of ID, which will be used for search, filtering and export.
Another column, “STAT_IMAGE”, will be used for the image corresponding to each status.
We can simply use DECODE or CASE in SQL to select the desired image, but it is more correct to use a static list (Static LOV), especially with a large possible number of values. Using a convenient tabular list editor (Grid Edit), their creation and editing is greatly simplified.



Another advantage when using static lists is the ability to use the Subscribe function, which is an option for inheritance and makes it easy to synchronize changes between web applications.
In our example, the static list STATUS_IMAGES consists of 4 elements.
To use the list of values, we add the function call APEX_ITEM.TEXT_FROM_LOV to the SQL query .



Now we need to set the necessary properties of the “STAT_IMAGE” column.
First you need to change the Display Type property of the column to Hidden. Thus, that column will not be displayed or exported. Search by the value of this column is now also prohibited.



Now let's work on the “Status” column.
This column should be displayed. In order to display a text description instead of a status code, change the Display Type of the column to Display as Text (based on LOV, escape special characters) .
The list of STATUS_DESCRIPTION values ​​contains the following statuses:



Now the column shows text descriptions of statuses, but instead we need pictures. We use the wonderful functionality of HTML Expression, which appeared in Apex 4.1. We can use the template #TITLE_NAME # to display the values ​​of both the current and other columns in the HTML expression.



Here in the example, we use HTML Expression to display the value of another column, “STAT_IMAGE” , in a column . An additional title tag is needed to display a tooltip with a description of the status.
Pure HTML and # COLUMN # syntax looks and reads much better than when used inside SQL, in addition, there is no need for additional quotes escaping.
HTML Expression is only used to output text and HTML to the browser. For search, filtering in drop-down lists, the original column values ​​are used .



Export is also performed correctly.



Bottom line: the correct design of WEB-applications, separation between layers and the correct use of Apex's functionality allows you to improve the life of the developer and avoid many hidden potential errors when searching, exporting, filtering, etc. Do everything right right away, and it will count towards you ....

Also popular now: