How to Prevent SQL Injection Attacks in APEX (Beyond the Basics)

SQL injection remains one of the most exploited vulnerabilities in web applications, and Oracle APEX is no exception. Despite APEX’s built-in protections, poor use of dynamic PL/SQL and configuration mistakes can expose your application to attacks. In this article, we explore advanced techniques to prevent SQL injection attacks in APEX, going beyond basic measures like using bind parameters.

Apply the Principle of Least Privilege

One of the best ways to mitigate SQL injection risks is by enforcing the least privilege principle at all levels.

Best Practices:

Create database roles with minimum privileges and assign only the required permissions.
Restrict service account privileges to minimize impact if compromised.

Disable Direct Access to APEX Pages

Users should not be able to access sensitive APEX pages or functions that execute SQL queries without proper authentication.

How to Prevent Unauthorized Access:

Enforce authentication and authorization to restrict access.
Properly configure user roles and permissions to ensure that only authorized users can access critical pages.

Perform Penetration Testing & Vulnerability Analysis

Regular penetration testing and vulnerability analysis are essential to identify potential weaknesses before attackers can exploit them.

Recommended Security Tools:

APEX Security Advisor –Evaluates security and detects vulnerabilities within your APEX application.

Burp Suite or OWASP ZAP – Advanced security analysis tools for identifying SQL injection and other web security threats.

Disable Code Evaluation in Page Elements

By default, APEX evaluates dynamic expressions in some UI components, which can be a vulnerability if not properly configured.

How to Disable Evaluation:

Text Fields: Use Escape Special Characters instead of No Escape.
Report Columns: Use Escape Special Characters in the SQL query attributes.
Dynamic Actions: Use PL/SQL Function Body instead of Execute JavaScript Code for handling dynamic data.

Restrict APEX Database Account Privileges

The database user that APEX uses (APEX_PUBLIC_USER or ORDS_PUBLIC_USER) should have the least privileges possible.

Best Practices:

Avoid granting DBA, DROP, ALTER, DELETE on critical tables.
Use GRANT SELECT, INSERT, UPDATE only for necessary tables.
For Web Source Modules, define specific API access permissions.

Check Current Permissions:

Copy to Clipboard

Secure Oracle REST Data Services (ORDS)

If you use Oracle REST Data Services (ORDS) to expose data through APEX, ensure your endpoints are secured.

Best Practices for ORDS Security:

Enable OAuth2 or JWT instead of Basic Authentication.
Disable unused HTTP methods (e.g., PUT, DELETE).
Use firewall rules to restrict IP access to ORDS.

Restricting Methods in ORDS:

Copy to Clipboard
Syntax HighlighterBEGIN
  ORDS.DEFINE_MODULE (
    p_module_name    => 'my_api',
    p_base_path      => '/api/',
    p_items_per_page => 25
  );
  
  ORDS.DEFINE_TEMPLATE (
    p_module_name => 'my_api',
    p_pattern     => 'customers/'
  );
  
  ORDS.DEFINE_HANDLER (
    p_module_name => 'my_api',
    p_pattern     => 'customers/',
    p_method      => 'GET',
    p_source_type => ORDS.source_type_query,
    p_source      => 'SELECT * FROM customers'
  );

  COMMIT;
END;

This prevents unauthorized users from executing unsafe HTTP methods.

Use Stored Procedures Instead of Dynamic SQL

Rather than building SQL queries directly within the application, encapsulate SQL logic inside stored procedures and functions. This ensures that database access is handled through a controlled security layer, preventing direct exposure to SQL injection attacks.

Benefits:

SQL Logic Isolation: The SQL logic remains encapsulated within stored procedures, making it harder for attackers to manipulate queries.

Injection Protection: Stored procedures do not allow direct execution of dynamic SQL queries, reducing the risk of SQL injection.

Code Reusability: Stored procedures enable reuse of SQL logic across different frontend technologies, ensuring consistency and reducing duplication in multi-platform applications.

Avoid Using Dynamic SQL

Dynamic SQL is one of the biggest security risks, as it allows user input to alter the structure of queries. Instead of using dynamic SQL, always prefer parameterized queries or bind variables to ensure security.

What If Dynamic SQL Is Necessary?

1. Don’t Rely Solely on Bind Variables (But Use Them Correctly)

Bind Variables (:P1_PARAM) are a critical first layer of defense against SQL injection, preventing user input from being executed as code. However, improper use or combining them with string concatenation can introduce vulnerabilities.

Vulnerable Code Example (String Concatenation):

Copy to Clipboard
Syntax HighlighterEXECUTE IMMEDIATE 'SELECT name FROM customers WHERE id = ' || :P1_ID;

If :P1_ID is set to 1 OR 1=1, the query becomes:

Copy to Clipboard
Syntax HighlighterSELECT name FROM customers WHERE id = 1 OR 1=1;

This allows an attacker to retrieve all customer records.

Correct Use of Bind Variables in Dynamic SQL:

Copy to Clipboard

Using :1 as a placeholder ensures that user input is treated as data, not as executable SQL.

When Bind Variables Are Not Enough

Bind Variables with USING work only for literal values in WHERE, SET, and VALUES clauses. They cannot be used for:

Table or column names

Operators (ORDER BY, GROUP BY, HAVING, etc.)

For dynamic elements, use whitelisting or DBMS_ASSERT to validate input before execution.

2. Use Whitelisting in Dynamic SQL

When using dynamic SQL, whitelist allowed values to ensure only predefined values are used in clauses like ORDER BY or WHERE.

Vulnerable Code Example:

Copy to Clipboard

If :P1_ORDER is set to '1; DROP TABLE employees'the attacker can delete the table.

Solution Using Whitelisting:

Copy to Clipboard
Syntax HighlighterDECLARE
  v_sql VARCHAR2(200);
BEGIN
  IF :P1_ORDER IN ('name', 'salary', 'hire_date') THEN
    v_sql := 'SELECT * FROM employees ORDER BY ' || :P1_ORDER;
    EXECUTE IMMEDIATE v_sql;
  ELSE
    RAISE_APPLICATION_ERROR(-20001, 'Invalid sorting column');
  END IF;
END;

This ensures only permitted values are used, preventing malicious execution.

3. Use SYS.DBMS_ASSERT for Input Validation

Oracle provides DBMS_ASSERT to validate strings before execution, preventing SQL injection. This is especially useful for validating table or object names.

Example Using DBMS_ASSERT for Table Names:

Copy to Clipboard
Syntax HighlighterDECLARE
  v_table_name VARCHAR2(50);
BEGIN
  v_table_name := DBMS_ASSERT.SQL_OBJECT_NAME(:P1_TABLE);
  EXECUTE IMMEDIATE 'SELECT * FROM ' || v_table_name;
END;

DBMS_ASSERT.SQL_OBJECT_NAME ensures the input is a valid Oracle object name, preventing malicious injections.

4. Enable SQL Auditing to Detect Injection Attempts

If someone attempts to inject malicious code, you need real-time detection.

Enable SQL Auditing in Oracle:

Copy to Clipboard

Identify Suspicious Activity:

Copy to Clipboard

Preventing SQL injection in Web Applications requires more than just Bind Variables. A multi-layered security approach is necessary.

By implementing these strategies, your APEX application will be significantly more secure against SQL injection attacks, protecting your data and users from one of the most dangerous attack vectors.