IR Checkbox Plugin

Implementing Checksum-Based Interactive Report Row Deletion

by
Oracle APEXORDS

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

Comments are closed.