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
Post a Comment