🇪🇸 Leer en Español

In our previous APEX Insights article, we covered ten key recommendations for building robust and maintainable Oracle APEX applications. Now, it's time to go deeper into the most critical pillar of all: security.

Oracle APEX provides a highly secure architecture by design, but the PL/SQL and JavaScript we write can introduce vulnerabilities. The two most common attacks you must defend against are SQL Injection (server-side) and Cross-Site Scripting (XSS) (client-side).

Mastering these defenses is essential for delivering world-class APEX applications.

---

🚨 The Server Enemy: SQL Injection

SQL Injection occurs when attackers inject malicious SQL through user-controlled input, forcing the database to execute unintended commands.

🔑 The Golden Rule: Always Use Bind Variables

Never concatenate user input into SQL strings. Always use bind variables (:PXX_ITEM). This ensures values are treated as data, not executable code.

If you're still using concatenation in PL/SQL, it's time to refactor.

❌ Vulnerable Example

DECLARE
    l_user_id VARCHAR2(50) := :P1_USER_ID;
BEGIN
    EXECUTE IMMEDIATE 'UPDATE users SET status = ''ACTIVE'' WHERE user_id = ''' || l_user_id || '''';
END;

✅ Safe Example

BEGIN
    UPDATE users SET status = 'ACTIVE' WHERE user_id = :P1_USER_ID;
END;

🛡 Dynamic SQL: Use APEX_EXEC

APEX_EXEC.EXECUTE_SQL(
  p_sql_statement => 'UPDATE users SET status = :P_STATUS WHERE user_id = :P_USER_ID',
  p_bind_vars     => APEX_EXEC.T_BIND_VAR(
    APEX_EXEC.T_BIND_VAR_ROW('P_STATUS', 'ACTIVE'),
    APEX_EXEC.T_BIND_VAR_ROW('P_USER_ID', :P1_USER_ID)
  )
);

---

🕷 The Client Enemy: Cross-Site Scripting (XSS)

XSS happens when malicious JavaScript is injected through user input and executed in another user's browser.

🔒 Declarative Defense: Escape Special Characters

Oracle APEX provides strong declarative protection. Always ensure user-entered values are escaped:

• Page Items: Use safe item types. Avoid HTML-rendering items for untrusted content.
• Classic / Interactive Reports: Enable Escape special characters.

🧹 PL/SQL Defense: APEX_ESCAPE.HTML_OUT

Use APEX_ESCAPE when building HTML in PL/SQL.

❌ Vulnerable

htp.p('Welcome, ' || :P1_USER_NAME);

✅ Safe

htp.p('Welcome, ' || APEX_ESCAPE.HTML_OUT(:P1_USER_NAME));

---

🔄 Secure AJAX Calls with apex.server.process()

Never construct AJAX requests manually. Always use Oracle APEX JavaScript APIs—they automatically pass the session checksum.

const payload = {
    x01: $v('P1_NEW_VALUE'),
    f01: ['value1', 'value2']
};

apex.server.process(
    "PROCESS_SAVE_DATA",
    {
        dataType: "json",
        x01: payload.x01,
        f01: payload.f01
    },
    {
        success: function(pData) {
            if (pData.success) {
                apex.message.showPageSuccess('Data saved successfully.');
            }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            apex.message.showErrors([
                { type: "error", message: `Processing error: ${errorThrown}`, location: ["page"] }
            ]);
        }
    }
);

---

🔗 Secure Redirects with APEX_UTIL.PREPARE_URL

Always include a session checksum when building dynamic URLs.

🔐 Safe PL/SQL Redirect

DECLARE
    l_url VARCHAR2(4000);
BEGIN
    l_url := APEX_UTIL.PREPARE_URL(
        p_url    => 'f?p=' || :APP_ID || ':2:' || :APP_SESSION,
        p_items  => 'P2_DEPARTMENT,P2_RECORD_ID',
        p_values => 'SALES,' || :P1_CURRENT_ID,
        p_checksum_type => 'SESSION'
    );

    APEX_UTIL.REDIRECT_URL(p_url => l_url);
END;

---

🧩 Conclusion

Security in Oracle APEX is a shared responsibility. The platform protects architecture and sessions, but developers must secure PL/SQL and JavaScript.

By consistently applying:

• Bind variables
• Output escaping
• Secure AJAX calls
• Safe redirects

you're building a stronger, safer, more professional Oracle APEX application.

What security challenge have you solved in your APEX projects? Share your insights below.

Don't miss next week's APEX Insights article—where we'll explore how to improve user experience (UX) in Oracle APEX.

---

📘 References

1. Oracle APEX Secure Coding Guidelines https://docs.oracle.com/en/database/oracle/application-express/latest/htmig/securing-an-application.html

2. OWASP Top 10 (A03: Injection, A07: XSS) https://owasp.org/www-project-top-ten/

3. Oracle APEX JavaScript API (apex.server.process) https://docs.oracle.com/en/database/oracle/application-express/latest/aexjs/apex.server.html

---

🚀 Need an APEX Expert?

I help companies facilitate professional Oracle APEX development and DevOps. If you want to build better applications or automate your pipeline, let's talk.

☕ Schedule a Call|💼 Connect on LinkedIn

💖 Support My Work

If you found this article helpful, consider supporting me!

GitHub Sponsors | Buy Me a Coffee

Your support helps me keep creating open-source demos and content for the Oracle APEX community. 🚀

---