IR Checkbox Plugin

IR Checkbox Plugin

Introduction

An important feature is still missing in APEX – IR Checkbox. It often happens that we need to search for data in an IR to narrow it down, and then do something with this data or a part of it. Currently, APEX does not offer a standard feature for these purposes. At Kubicek Consulting GmbH (@Kubicek_CG), we developed a plugin years ago that enables this. We designed the plugin to provide immediate access to the selected IDs after selecting the rows. We used the APEX Collection to store the selected entries. This plugin has been running since version 18.2 and has never needed to be adjusted or changed. It also runs under 24.1. The idea behind it is very simple, as is the use and implementation of the plugin. Here we show how the implementation can be done.

Install the Plugin

You can download our plugin here  and import it into your workspace or the relevant application. No special setup of the plugin is necessary.

Solution Overview

Interactive Report

Create or select an existing Interactive Report and navigate to the page. Choose the ID column and assign a Static ID to it + disable all the options for the user (Hide, Sort, etc.):

Create a Dynamic Action and name it. Select the IR Checkbox [Plug-In] for the Action.

Name your Collection or leave the default value.

Type in the Static ID of the column you want to use.

Dynamic Action

Create a Dynamic Action container and name it.

Use Click-Event for the Selection Type Region > Your Region

IR Display

Your report should now be displayed like this:

You can create a button, which will be displayed if there is a row selection and hidden, if not. Create a new Action within your Dynamic Action using the following code:

				
					apex.server.process ( "checkSelected", {}, 
   { dataType: 'text', success: function( pData ) 
     { 
       if (pData == '0')
       {$('#SHOW_SELECTION').hide();}
       else {$('#SHOW_SELECTION').show();}
     }  
   } 
)

Create an Ajax Call on your page and name it “checkSelected”. Use the following code for it:

				
					DECLARE
    v_count NUMBER := 0;
BEGIN
    SELECT
        COUNT(*)
    INTO v_count
    FROM
        apex_collections
    WHERE
        collection_name = 'SELECTED_COLLECTION';

    htp.prn(v_count);
    
EXCEPTION
    WHEN OTHERS THEN
        htp.prn(v_count); 
END;

Now, if you select any of the rows, your button should appear:

It is quite easy to select, show or process the selected ID’s by using this SQL

				
					SELECT to_number (c001) FROM apex_collections WHERE collection_name = 'SELECTED_COLLECTION'

Conclusion

We have already seen and had to debug many different, more or less effective solutions for this problem. This was also one of the reasons why we decided to create our own plugin. Most of the solutions ran well for a while and then suddenly stopped working after the next update. There were also situations where the solutions only worked for a limited amount of data and encountered performance issues with large amounts of data. Our solution has been running in many applications for many years, and no significant problems are known to us.You can view the example in our Blog-Application here:

VIEW DEMO
DALL·E 2024-03-22 15.37.39 - Design a header image that encapsulates PL_SQL coding and Oracle Database themes. Include visual representations of PL_SQL code, such as script lines

SQL Macros – Creating parameterised views

Issue Statement

As you may know, invoking a function in a query results in a context switch between the SQL engine and the PL/SQL engine, which negatively impacts performance. For example, the two statements below produce the same output but have significantly different execution times.

A Common Case

Execution without using a function:

				
					SELECT view_name
  	FROM all_views
WHERE text_length = (SELECT MAX (text_length)
                        FROM all_views
                       WHERE owner = 'HR');
                       
VIEW_NAME
---------
EMP_DETAILS_VIEW
Elapsed: 00:00:0.568

Execution using a function:

Execution using a function:

				
					SELECT view_name
  FROM all_views
WHERE text_length = fnc_get_max_length ('HR');  
VIEW_NAME
---------
EMP_DETAILS_VIEW
Elapsed: 00:00:03.84

Note: The function get_max_length is defined as follows:

				
					CREATE OR REPLACE FUNCTION fnc_get_max_length (pi_owner IN VARCHAR2)
    RETURN NUMBER
IS
    v_length   NUMBER;
BEGIN
    SELECT MAX (text_length)
      INTO v_length
      FROM all_views
     WHERE owner = pi_owner;



    RETURN v_length;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        RETURN NULL;
END;
/

Issue Statement/ Solution

As observed, the first query executes in less than a second, while the second query takes nearly 4 seconds. This comparison suggests avoiding functions in queries, but in some cases, not using functions can be costly, requiring changes to various parts of the program for a single formula change. Therefore, avoiding functions in queries isn’t always the best solution.

Oracle introduced SQL Macros in version 21c to address the mentioned performance issue. Using this feature, the query containing the function is rewritten in a simple form without using the function.

Solution

According to Oracle’s classification, SQL Macros is a new feature in version 21c, although it was introduced in Oracle 20c documentation and has been usable since version 19.6.

To use SQL Macros in a function, the SQL_MACRO keyword must be used in the function’s creation statement, and the return type can only be a string type (string, varchar2, clob, etc.). The return value must also be enclosed in the {}.

For example, the function get_max_length_sql_macro is rewritten using SQL Macros as follows:

				
					CREATE OR REPLACE FUNCTION fnc_get_max_length_sql_macro (
    pi_owner   IN VARCHAR2)
    RETURN VARCHAR2
    SQL_MACRO(SCALAR)
IS
    v_length   NUMBER;
BEGIN
    RETURN q'{
    SELECT
        MAX(text_length)
    FROM
        all_views
    WHERE
        owner = pi_owner
    }';
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        RETURN NULL;
END;

With the above changes, the execution time of the query used at the beginning of the text changes from about 8 seconds to less than 1 second, approximately 0.7 seconds:

				
					 SELECT view_name
  FROM all_views
WHERE text_length = fnc_get_max_length_sql_macro('HR');
  VIEW_NAME
---------
EMP_DETAILS_VIEW
Elapsed: 00:00:0.74

Type of SQL Macros

SQL Macros are of two types: Table macros and Scalar macros. The Scalar macro function is placed in the SELECT list, WHERE clause, HAVING clause of an SQL statement, whereas Table SQL macros are used in the FROM clause.

Therefore, the function fnc_get_max_length_sql_macro is considered a Scalar SQL macro.

Type of SQL Macros-Table Macro

Additionally, the following function, with two input parameters (cnt(number) and pi_owner(varchar2)), is a table macro designed to return the list of views related to the called schema in descending order of the largest query:

				
					CREATE OR REPLACE FUNCTION fnc_table_macro_all_views (cnt        NUMBER,
                                                      pi_owner   VARCHAR2)
    RETURN VARCHAR2
    SQL_MACRO(TABLE)
IS
BEGIN
    RETURN q'{
    select owner,view_name,text_length from  all_views
    where owner = table_macro_all_views.pi_owner
    order by text_length desc fetch first table_macro_all_views.cnt rows only

   }';
END;

Table Macros

Using the function in a query:

				
					SELECT * FROM fnc_table_macro_all_views (5, ‘SYS‘);

OWNER      VIEW_NAME                 TEXT_LENGTH
--------- ------------------------- -----------
SYS        _user_stat                51594
SYS        ALL_CUBE_DEPENDENCIES     29829
SYS        ALL_IND_STATISTICS        24463
SYS        ALL_TAB_PARTITIONS        23892
SYS        USER_OBJ_AUDIT_OPTS       23772

SQL Macros

By leveraging SQL Macros, you can significantly improve the performance of your SQL queries while maintaining the flexibility and maintainability of using functions.

IR Checkbox Plugin

Implementing Checksum-Based Interactive Report Row Deletion

Introduction

In Oracle Application Express (APEX), managing data integrity and security is paramount, especially when it comes to operations like deleting or updating records. In this blog post, we’ll explore a robust method to enhance security by leveraging checksums for validating row deletions, addressing common pitfalls such as unprotected items and vulnerabilities associated with them.

We present a typical scenario encountered in APEX applications: users are permitted to delete or modify records by clicking an icon, initiating a dynamic action that prompts for confirmation. Upon user affirmation, the record ID is transmitted to a concealed (and unprotected) item on the backend, subsequently leading to the alteration or deletion of the record, followed by a confirmation message. This sequence, while standard, exposes significant security vulnerabilities that we aim to rectify.

Understanding the Security Risks

In standard Oracle APEX applications, developers might allow record deletions through clickable icons in interactive reports. Typically, a dynamic action requests confirmation before passing the record’s ID to a backend item, which is often hidden and unprotected. This method, while functional, exposes potential security risks. The visible, editable record ID can be manipulated, leading to unauthorized data alterations or deletions.

Common Solution

Now we will show the faulty common solution for this problem that we see being implemented in a lot of applications that need a functionality like this. The following implementation has a few more issues than just missing the checksum, but we will not get in details here about the them.

Interactive Report

In the interactive report, we use the following query to generate a span element for the column ID, assigning it the class “fa fa-trash-o”.

				
					SELECT 
    '<span id="' || id || '" class="fa fa-trash-o delete" style="cursor:pointer"></span>' id,
    val,
    val_attribute
FROM
    test_delete_unprotected

Creating the Dynamic Action

Now that we have our report with the icon for deletion. We have created a Dynamic Action that will be triggered when we click the delete icon inside the report, defined in the query above and identified by its “delete” class.

Now we have the following true actions defined for our Dynamic Action.

Confirm Message Action

Javascript Code Action

Here we will take the value of the row id from the span element and set it to a hidden page item (P401_ID). 

PL/SQL Code Action

Here we have an action to submit the hidden item (P401_ID) into session. Yes we still find actions like this when we shouldn’t, but this is another topic for some other time.

PL/SQL Code Action

In this action we delete the record using the ID from our hidden item (P401_ID).

Javascript Code Action

In this action we will show a success message using apex.message.showPageSuccess.

Refresh Action

Finally we have a refresh action to refresh our report.

What is the problem?

Now some of you may ask why is this approach causing a security issue and how can this be exploited.

Here’s why this method can lead to vulnerabilities:

Firstly, the page contains a hidden item that is not secured, making it susceptible to unauthorized manipulation.

Secondly, the ID, which is crucial for identifying the specific record in question, is openly visible and modifiable, opening the door for potential exploitation.

How does this happen? If we take a closer look, we’ll notice that the ID of the record is set as the id of our span element. When you click on this icon, our defined Dynamic Action will be triggered, which then moves the ID value to the hidden item. 

This ID, can be anytime changed to any other value and if it exists, it will successfully delete (modify) the corresponding record.

Especially, in the selective view, where application users have a limited set of data to view and edit, this is quite dangerous and can be easily abused.

If we now change the ID manually, to a value which doesn’t even exist in the rendered report, the process will successfully delete the record

As we can see we get a success message that the record with ID 20 is deleted.

Ok, so now you may ask how can we fix this issue, how can we prevent this from happening to us. Now we will describe how can you implement a solution for deleting a record the right way.

Solution with Checksum

We can address this issue by removing the hidden item and altering our approach. Crucially, we should ensure safe record deletion by using a checksum-based ID in the span element, which prevents malicious users from altering it to delete records they shouldn’t access.

Checksum Function

We will start by creating a function that will generate a unique value as a combination of our secret + session + user + value. This checksum is impossible to guess and therefor can’t be manipulated.
				
					CREATE OR REPLACE FUNCTION 
checksum_fn (
    p_value IN VARCHAR2, 
    p_app_user IN VARCHAR2,
    p_app_session IN VARCHAR2
) 
RETURN VARCHAR2 IS
    -- something hard to guess
    v_key_value VARCHAR2(4000) := 'P21ec)J9CVQXKVR';
    v_checksum  VARCHAR2(4000);
BEGIN
    -- combine the value with usernmame, session and our key value
    SELECT
        standard_hash(p_value || p_app_user || p_app_session || v_key_value)
    INTO v_checksum
    FROM
        dual;

    RETURN v_checksum;
END checksum_fn;

Interactive Report Query

Now we will change our previous report query to make use of our “checksum_fn” to generate the checksum for our records. 
				
					SELECT
    '<span id="' || id || '" name="'
    || checksum_fn (id, :app_user, :app_session)
    || '" class="fa fa-trash-o delete" style="cursor:pointer"></span>'
    id,
    val,
    val_attribute
FROM
    test_delete_unprotected

Ajax Process

Now we will create an AJAX process that will be called when we trigger our dynamic action. This process will safely delete our record, but only if the checksum verification is successful.
				
					DECLARE
    v_checksum        VARCHAR2(400) := apex_application.g_x01;
    v_id              NUMBER := to_number(apex_application.g_x02);
    v_checksum_check  VARCHAR2(400);
    invalid_checksum  EXCEPTION;
BEGIN 
    SELECT
        checksum_fn (v_id, :app_user, :app_session)
    INTO v_checksum_check FROM dual;

    IF v_checksum = v_checksum_check THEN
        DELETE FROM test_delete_unprotected WHERE id = v_id;
    ELSE RAISE invalid_checksum;
    END IF;
    htp.prn('Success');
EXCEPTION
    WHEN invalid_checksum THEN
        htp.prn('Can''t delete the record with the provided ID. Invalid Checksum.');
    WHEN OTHERS THEN
        htp.prn(sqlerrm);
END;

Dynamic Action

Next we will change our dynamic action to call our AJAX process to delete the record. We will eliminate the 4 actions after confirmation message that we saw implemented before and we will do everything in a single Javascript code Action.  This is how our dynamic action looks now:

Confirm Message Action

We will keep the same confirm message action as we saw before.

Javascript Code Action

Here we will implement the call to our AJAX process to delete the record. If all goes well we will render a success message and refresh the report. If it fails we will render an error message.

Here is our full code used inside the Action.

				
					var thisID = this.triggeringElement.id;
var thisCS = $('#' + this.triggeringElement.id).attr('name');

apex.server.process (
    "deleteRecord",
    { x01: thisCS,
      x02: thisID },
    { dataType: 'text',
      success: function( pData ) {
        if(pData == 'Success')
        {apex.message.showPageSuccess( 'Record with ID: ' + thisID + ' deleted.' );
         apex.region('RECORD_REGION').refresh();}
        else
        {
        apex.message.showErrors([
         {
           type: apex.message.TYPE.ERROR,
           location: ["page"],
           message:  pData,
           unsafe:   false
         }])};}}
);

This is it we now have successfully implemented a solution for our problem that will be secure from user manipulation. If we now try to manually change the ID for any of the available records, we will receive an error message:

Conclusion

What have we achieved now? Firstly, we have made our application more secure. Secondly, we have closed potential vulnerabilities and removed the hidden item from the page without Protection. Thirdly, we have significantly reduced the number of dynamic actions on the page and only have one Ajax process, which, however, is reusable and can be standardized for many such cases. Application security is an important but often underestimated topic. We hope that with our example, we can contribute a little to making our application fundamentally more secure.

You can access our examples by clicking the button below:

VIEW DEMO
DALL·E 2024-03-22 15.34.43 - Design a header image tailored for content about Oracle REST Data Services (ORDS), clearly featuring the ORDS icon central in the design. This icon sh

Rest API Basic Authentication

Introduction

ORDS does not support Basic Authentication in the sense of calling a web service with a non-static username and password. However, this requirement exists and must be implemented in practice. Authentication methods that rely on a database schema or Basic Authentication with hardcoded usernames/passwords at the Webserver level are eventually not an option. In this example, we explain how this can still be set up in practice using a small workaround.

Understanding the Security Risks

In our case, the service is not strictly protected because it can be called at any time. In an authenticated service, the credentials are first checked, and only if they are valid, the actual service is called. In the example below, this is not the case – the service is called, and within the process, the provided information about the credentials is checked. If they are correct, the actual web service is invoked. If they are not correct, an exception is thrown, and the corresponding HTTP error code is returned.

Description of our Scenario

We want to provide a POST service that gives us a JSON file of the employees from the EMP table. We expect the caller to provide a username and a password. In our example, we check this information hardcoded in the package. However, it is easy to imagine that the provided function could be more extensive and, for example, perform a dynamic check of the credentials – possibly against Active Directory or another authentication source.

Solution Overview

Package

Let us create a package to handle our POST-Request.

				
					create or replace PACKAGE rest_basic_auth_pkg AS
  

    PROCEDURE get_emp_json (
        p_json          OUT VARCHAR2,
        p_status_code   OUT VARCHAR2,
        p_error_message OUT VARCHAR2
    );

END rest_basic_auth_pkg;
/
				
			
				
					CREATE OR REPLACE PACKAGE BODY rest_basic_auth_pkg AS
    -- This function checks the hardcoded username / password
    -- it can be any function to any authentication service (LDAP / Active Directory)
    FUNCTION check_username_password (
        p_username IN VARCHAR2,
        p_password IN VARCHAR2
    ) RETURN VARCHAR2 IS
    BEGIN
        IF
            upper(p_username) = 'REST_API_TEST'
            AND p_password = 'P04\wUQzYEAVQWC'
        THEN
            RETURN NULL;
        ELSE
            RETURN 'Invalid Username / Password.';
        END IF;
    END check_username_password;
    
   -- This procedure exctracts the information about the authentication method
   -- If the Authentication Method is Bacis, it will try to extract the information
   -- about the username and password
   -- If the Authentication Method is Basic, it will return TRUE, FALSE if not
    PROCEDURE authenticate_basic (
        p_username OUT VARCHAR2,
        p_password OUT VARCHAR2,
        p_method   OUT BOOLEAN
    ) IS

        v_authorization VARCHAR2(400);
        v_raw           RAW(32767);
        v_base64        VARCHAR2(4000);
        v_token         VARCHAR2(400);
    BEGIN
        v_authorization := owa_util.get_cgi_env('Authorization');
        IF v_authorization LIKE 'Basic%' THEN
            v_authorization := ltrim(
                                    v_authorization,
                                    'Basic '
                               );
            v_raw := utl_raw.cast_to_raw(v_authorization);
            v_raw := utl_encode.base64_decode(v_raw);
            v_base64 := utl_raw.cast_to_varchar2(v_raw);
            v_token := v_base64;
            p_username := substr(
                                v_token,
                                1,
                                instr(
                                     v_token,
                                     ':'
                                ) - 1
                          );

            p_password := substr(
                                v_token,
                                instr(
                                     v_token,
                                     ':'
                                ) + 1
                          );
            p_method := TRUE;
        ELSE
            p_method := FALSE;
        END IF;

    END authenticate_basic;
    
    -- This procedure returns a Json-Output for the EMP Table
    PROCEDURE create_json (
        p_json OUT VARCHAR2
    ) IS
        v_cursor SYS_REFCURSOR;
    BEGIN
        OPEN v_cursor FOR SELECT
                              e.empno  AS "employee_number",
                              e.ename  AS "employee_name",
                              e.deptno AS "department_number"
                          FROM
                              emp e;

        apex_json.initialize_clob_output;
        apex_json.open_object;
        apex_json.write(
                       'employees',
                       v_cursor
        );
        apex_json.close_object;
        p_json := JSON_QUERY(apex_json.get_clob_output,
           '$' RETURNING VARCHAR2 ( 4000 ) pretty);
        apex_json.free_output;
    END create_json;
    
    -- This procedure is used in the POST-Service call
    PROCEDURE get_emp_json (
        p_json          OUT VARCHAR2,
        p_status_code   OUT VARCHAR2,
        p_error_message OUT VARCHAR2
    ) IS

        v_username VARCHAR2(400);
        v_password VARCHAR2(400);
        v_method   BOOLEAN;
        invalid_authentication_method EXCEPTION;
        invalid_credentials EXCEPTION;
    BEGIN
        authenticate_basic(
                          v_username,
                          v_password,
                          v_method
        );
        IF NOT v_method THEN
            RAISE invalid_authentication_method;
        ELSE
            p_error_message := check_username_password(
                                                      v_username,
                                                      v_password
                               );
            IF p_error_message IS NOT NULL THEN
                RAISE invalid_credentials;
            END IF;
        END IF;

        create_json(p_json);
    EXCEPTION
        WHEN invalid_authentication_method THEN
            p_status_code := 405;
            p_error_message := 'Invalid Authentication Method.';
        WHEN invalid_credentials THEN
            p_status_code := 401;
        WHEN OTHERS THEN
            p_status_code := 500;
            p_error_message := 'Internal Server Error. Please contact your administrator.';
    END get_emp_json;
    
END rest_basic_auth_pkg;
/

Webservice

Let us create a POST-Webservice for downloading Json-File.

				
					BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'WKSP_BLOG',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'trn_apex23',
      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(
      p_module_name    => 'basic_auth',
      p_base_path      => '/kubicek-consulting-blog/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => 'This is a basic authentication example.');      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'basic_auth',
      p_pattern        => 'emp_json',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'basic_auth',
      p_pattern        => 'emp_json',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
    v_json          VARCHAR2(32767);
    v_status_code   VARCHAR2(400);
    v_error_message VARCHAR2(400);
    v_exception EXCEPTION;
BEGIN
    rest_basic_auth_pkg.get_emp_json(v_json, v_status_code, v_error_message);
    IF v_status_code IS NOT NULL THEN
        RAISE v_exception;
    ELSE
        htp.prn(v_json);
    END IF;

EXCEPTION
    WHEN v_exception THEN
        :string_out := v_error_message;
        :status_code := v_status_code;
END;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'basic_auth',
      p_pattern            => 'emp_json',
      p_method             => 'POST',
      p_name               => 'message',
      p_bind_variable_name => 'string_out',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'basic_auth',
      p_pattern            => 'emp_json',
      p_method             => 'POST',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status_code',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
      
  COMMIT; 
END;
/

Please note: There are two parameters in our method. The first one is used to display the message at the specific position and the other one to show the status code.

Oracle APEX Rest API Basic Authentication

Testing the Webservice

If we now use for example Postman, we can test our web service. We use the following URL:

https://blog.kubicek-consulting.de/ords/kc/kubicek-consulting-blog/emp_json

For the testing purposes, we have set up the following credentials:

  – Username: REST_API_TEST

  – Password: P04\wUQzYEAVQWC

Kubicek-consulting_blog_Rest_API_Basic_Authentication_Bild3

If we try to run the request using the specified username and password, we should receive the following output

Kubicek-consulting_blog_Rest_API_Basic_Authentication_Bild4

If we try to use a different username or password and we run the request, the request should fail, and we should receive the following output

Kubicek-consulting_blog_Rest_API_Basic_Authentication_Bild5

This example does not adhere to recommended practices. However, it is a way to still provide solutions when no other alternative is available. It is always necessary to weigh to what extent this solution aligns with the security requirements in the company.

You can view the example in our Blog-Application here:

VIEW DEMO