SQL Injection Prevention in Oracle APEX

Introduction

SQL Injection is a common security risk in database-driven applications where user input is handled unsafely and can alter the intended SQL statement. In Oracle APEX, this risk can occur when working with dynamic SQL, URL parameters, search fields, filters, or other user-provided values without proper validation and binding. APEX and Oracle provide several security mechanisms to reduce this risk, with the most important principle being: never concatenate untrusted user input directly into SQL; use bind variables and proper server-side validation instead.

Never treat user input as trusted SQL code.

1. What is SQL Injection?

SQL Injection happens when an application combines user input directly with a SQL statement.

For example, imagine a login query constructed like this:


If values are directly concatenated into the SQL statement, specially crafted input may change the meaning of the query.

The problem is not the user's input itself. The problem is mixing data with SQL code.

2. A Simple Unsafe Example

Consider this PL/SQL code:


Here, the value from P10_NAME is directly concatenated into the SQL statement.This approach can make the SQL structure dependent on user input.A safer approach is to keep the SQL structure separate from the data.

3. Use Bind Variables

One of the most important techniques for preventing SQL Injection is using bind variables.

Instead of dynamically inserting a value:


use:


The value is treated as data rather than SQL code.

In APEX, bind variables such as:

:P10_ID

:P10_NAME

:P10_PROPERTY_ID

:P10_TENANT_ID

are commonly used to pass page-item values into SQL and PL/SQL.

4. Static SQL is Usually Safer

Whenever possible, prefer static SQL.

For example:


This is preferable to dynamically constructing the entire statement.Static SQL makes the query easier to understand, maintain, optimize, and secure.A good rule is:

If the SQL structure does not need to change, don't use dynamic SQL.

5. The Problem with Dynamic SQL

Dynamic SQL is sometimes necessary when an application needs to dynamically determine table names, column names, sort expressions, optional conditions, or report logic. However, it becomes a security risk when user input is directly concatenated into the SQL statement. For example, building a query by adding :P10_DEPT_ID directly to the SQL string can allow the input to affect the SQL structure. Whenever possible, use bind variables for values and keep user input separate from the SQL statement.



6. Safe Dynamic SQL with Bind Variables

If dynamic SQL is genuinely required, use bind variables.

For example:


Here, :P10_DEPT_ID is passed separately from the SQL statement.

This keeps the value separate from the SQL structure.

7. Validate User Input

Bind variables help prevent SQL Injection, but input validation provides an additional layer of security. If an APEX item is expected to contain a number, validate it as a number. Similarly, if an item should accept only specific status values such as PENDING, APPROVED, or REJECTED, allow only those predefined values. Avoid accepting arbitrary input when the application expects a controlled set of values, and perform important validation on the server side.

8. Use LOVs for Controlled Values

For fields such as status, department, property, or country, an APEX List of Values (LOV) can provide users with predefined and valid options instead of allowing arbitrary input. For example:

SELECT status_name d,
       status_code r
FROM status_reference; 



This makes data entry more consistent and reduces invalid values. However, an LOV should not be considered a complete security mechanism. The selected value should still be validated on the server side, and appropriate authorization checks should be applied where required.

9. Be Careful with ORDER BY

A common mistake is trying to use a bind variable directly for a column name, such as ORDER BY :P10_SORT_COLUMN. Bind variables represent values, not SQL identifiers such as column names. If users need to choose how the report is sorted, use a controlled mapping—for example, 1 → EMPLOYEE_NAME, 2 → DEPARTMENT, and 3 → JOIN_DATE—and map the selected value to a predefined SQL expression. This prevents arbitrary user input from being inserted into the SQL statement and keeps the sorting logic secure.



10. Be Careful with Table and Column Names

Table and column names are SQL identifiers, not normal data values, so they cannot be safely handled like regular bind variables. For example, dynamically building a query with :P10_TABLE_NAME can be dangerous:


If dynamic table or column names are genuinely required, never trust the user-provided value directly. Instead, use a predefined whitelist of allowed identifiers, such as CUSTOMERS, EMPLOYEES, HOTEL_PROPERTY, and GUEST_PROFILE, and allow only values from that approved list. Where appropriate, Oracle's identifier-validation facilities can also be used to validate dynamic identifiers before they are included in SQL.

11. Avoid Unnecessary Dynamic SQL

Before using:

EXECUTE IMMEDIATE

ask:

Can this be written using normal SQL?

For example, instead of dynamically generating:


Simpler SQL usually means fewer security and maintenance problems.

12. Secure PL/SQL Processes

SQL Injection can also occur inside APEX page processes, application processes, and stored procedures.

For example, avoid:


Prefer bind variables:


Even better, when dynamic SQL is unnecessary, use normal SQL:


13. Don't Trust URL Parameters

Oracle APEX applications often pass values through URLs, such as P10_ID, P10_TENANT_ID, or P10_PROPERTY_ID. These values should not be considered trustworthy simply because they came from an APEX page. For example, a URL containing P10_PROPERTY_ID=101 could potentially be changed to P10_PROPERTY_ID=102 to attempt accessing another property. To prevent unauthorized access, use Session State Protection, authorization checks, input validation, and server-side data-access controls. Remember that SQL Injection prevention and authorization are separate security concerns, and both are necessary to properly protect an APEX application.

14. SQL Injection vs Authorization

SQL Injection and Authorization address two different security concerns. SQL Injection prevention protects the SQL statement from being manipulated by keeping user input separate from the SQL logic, typically through bind variables. Authorization, on the other hand, determines whether the user is actually allowed to access a particular page, operation, or record. For example, a query can be completely protected against SQL Injection but still expose another tenant's data if proper authorization is missing. Therefore, a secure Oracle APEX application needs both secure SQL and proper authorization controls.

15. Multi-Tenant Applications

SQL Injection prevention is especially important in multi-tenant applications, where users from different tenants share the same database but must access only their own data. For example:


Using a trusted server-side tenant context helps prevent users from manipulating the tenant value to access another tenant's records. For stronger centralized protection, database-level mechanisms such as Row-Level Security (RLS) or Virtual Private Database (VPD) can also be used. The goal is to ensure that users cannot bypass security through either SQL manipulation or unauthorized tenant identifiers.

16. Avoid SELECT *

Using SELECT * is not directly an SQL Injection issue, but it is generally better to select only the columns required by the application. For example:


Instead of:


Selecting only the required columns can reduce unnecessary data retrieval, limit accidental exposure of sensitive fields, and make the application's data requirements clearer. It can also make queries easier to maintain as the database structure changes.

17. Secure REST APIs

SQL Injection risks can also occur when an Oracle APEX application exposes or consumes REST APIs. API inputs should be properly validated, passed to SQL using bind variables, and protected with appropriate authentication and authorization. Values should also be restricted to the expected format and allowed options. Never assume that API input is trustworthy simply because it comes from another application; every external request should be treated as untrusted input and validated before it reaches the database.

18. Common Mistakes to Avoid

When developing an Oracle APEX application, small security shortcuts can create bigger problems later. Avoid directly joining user input with SQL, using EXECUTE IMMEDIATE without a real need, and trusting values received through URLs or page items. Do not allow users to provide arbitrary table or column names, and never depend only on JavaScript validation or LOVs for security. Always perform important checks on the server side, apply proper authorization, and prefer static SQL with bind variables whenever possible.

19. Best Practices

For effective SQL Injection prevention in Oracle APEX, follow a few key practices. Prefer static SQL and use bind variables whenever possible, and use dynamic SQL only when it is genuinely required. When using EXECUTE IMMEDIATE, always bind values instead of concatenating them. Validate user input, use controlled LOVs, and whitelist any dynamic table or column names. Protect URL parameters, apply server-side authorization, and secure REST API inputs. Finally, avoid unnecessary SQL concatenation, test with unexpected inputs, and review dynamic SQL carefully during code reviews.

20. Simple Secure Flow

Final Takeaway

SQL Injection prevention in Oracle APEX is mainly about keeping user input separate from SQL code. Whenever possible, use static SQL and bind variables, and when dynamic SQL is necessary, bind values and carefully control dynamic identifiers such as table and column names. A secure APEX application combines input validation, bind variables, secure dynamic SQL, authorization, and server-side controls to protect data and application logic. The key rule to remember is: never concatenate user input directly into SQL when a bind variable can safely handle the value.

Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Mastering Shared Components in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications