APEX

Hi Everyone,

I'm using Oracle APEX 20.1.0.00.13, and I’ve encountered an issue with the Popup LOV component. Here's the scenario:

I created a Page Item P9000_MEM_ITEM of type Popup LOV with the following configurations:
- Manual Entry is disabled.
- The List of Values (LOV) can be defined in one of several ways:

1. A Shared Component.
2. A SQL Query (directly written on the page).
3. A PL/SQL Function Body returning a SQL Query.
4. Static Values.

In my case, the LOV is defined using a SQL Query, as shown below:

SELECT 
    MEM_NAME || ' (' || MEM_ID || ')' AS DISPLAY_VALUE,
    MEM_ID AS RETURN_VALUE
FROM 
    MEMORY_IDS
WHERE 
    MEM_STATUS = 'ENABLE'
ORDER BY 
    MEM_ID;

After rendering the page, Apex generates a hidden input element to store the selected value from the LOV. For example:

<input type="hidden" name="P9000_MEM_ITEM" id="P9000_MEM_ITEM_HIDDENVALUE" value="ABCDEF">

When a user selects a value from the LOV, Apex updates the value attribute of this hidden input element. However, a malicious user can modify the value using browser developer tools and set it to an invalid value (e.g., a MEM_ID with MEM_STATUS = 'DISABLED' that doesn't appear in the LOV query). If this happens, Apex saves the manipulated value to the database without validating it against the LOV.

Key Challenges:
1. This issue affects all Popup LOVs across over 1000 pages in my application.
2. Some of these LOVs are even conditional, depending on other page items (e.g., MEM_STATUS = 'ENABLE' AND MEM_USER = :P9000_ANOTHER_ITEM), making the problem more complex.

I attempted to write a validation query like this to check whether the submitted value exists in the LOV:

SELECT 
   COUNT(*)
FROM 
   DUAL
WHERE
   :P9000_MEM_ITEM IN (/* Popup LOV Query */);

However, I’m not sure how to dynamically substitute the Popup LOV Query in this context to make it reusable. I’d appreciate guidance on how to implement such a validation properly.

I also tracked APEX AJAX requests using browser developer tools but couldn’t figure out how APEX retrieves the Popup LOV query dynamically at runtime or how it replaces any referenced Page Items (e.g., :P9000_ANOTHER_ITEM) with their actual values.

So, How can I prevent users from tampering with the values of Popup LOVs and ensure that only valid, query-compliant values are saved to the database? Any help would be greatly appreciated!