PL/SQL Injection

PL/SQL injection is a security issue where attackers use harmful code to exploit weak spots in Oracle's PL/SQL applications. If user input isn’t properly checked, attackers can access or change sensitive data and even take control of the system.

In this article, we will explain what PL/SQL injection is with its types, preventive measures, and examples to secure our applications.

What is PL/SQL Injection?

PL/SQL injection occurs when an attacker inputs malicious data to manipulate the execution of a PL/SQL program, often found in Oracle-based web applications. This happens when user inputs are not properly validated or sanitized, allowing attackers to alter the logic of the SQL queries embedded in PL/SQL code.

Attackers use PL/SQL injection to bypass authentication, retrieve sensitive data, modify or delete records, and even take full control of the database.

For instance, if an application directly embeds user input into a query without proper validation, an attacker can inject malicious SQL code to manipulate the query’s behavior, often extracting or tampering with confidential data.

Types of PL/SQL Injection

1. In-band SQL Injection

• In an in-band SQL injection attack, the attacker uses the same communication channel to inject malicious SQL code and retrieve the results.
  
• This is the most common and straightforward form of PL/SQL injection because the attacker can directly see the outcome of their actions, such as retrieved data or error messages.
  
• An example would be injecting SQL code through a web form, where the results (like data leakage) are immediately shown on the same web page.

2. Out-of-band SQL Injection

• In an out-of-band SQL injection, the attacker uses one channel to send the malicious SQL query but retrieves the results through a different communication channel.
  
• This type of attack is less common but useful when direct feedback from the database is not available or if the database doesn’t respond directly to in-band queries.
  
• For example, the attacker might inject code that causes the database to send the retrieved data to an external server or email address they control. This is often used in situations where in-band attacks are blocked or ineffective.

3. Blind SQL Injection

• Blind SQL injection occurs when the attacker cannot directly view the results of their injected SQL code. Instead, they infer information by observing indirect clues, such as how long it takes a page to load or if certain actions cause errors.
  
• For instance, if an attacker injects a query that causes a delay in loading the page, they can guess that their injection was successful.
  
• This method is slower and more difficult, but still very dangerous because it can be used to uncover sensitive information even when direct responses are hidden.

How PL/SQL Injection Happens?

PL/SQL injection typically occurs due to:

• Improper Input Validation
  
  When user inputs are not properly validated or sanitized, attackers can introduce malicious SQL code. If an application accepts input directly from users without checking for harmful content, it becomes easy for an attacker to insert code that alters the intended SQL query.
  
• Dynamic SQL Usage
  
  PL/SQL injection is more likely when applications use dynamic SQL, where SQL queries are built by directly concatenating user inputs into the query string. This approach is risky because if the input isn't properly handled, attackers can insert malicious code into the query, leading to unintended actions, such as data leakage or unauthorized modifications.
  
• Overly Permissive Database Privileges
  
  Allowing users or applications to execute operations with excessive privileges can open the door to PL/SQL injection attacks. If a user has more access rights than needed, an attacker can exploit this to perform unauthorized operations like deleting records, changing data, or even taking control of the entire database system.

Example 1: Vulnerable PL/SQL Code

Step 1: Create the Sample Table

In this step, we create an employees table with two columns: emp_id (for employee ID) and emp_name (for employee name). Then, we insert three sample records for employees Alice, Bob, and Carol. The COMMIT; statement ensures the data is saved permanently to the database.

Query:

CREATE TABLE employees (
   emp_id VARCHAR2(10),
   emp_name VARCHAR2(100)
);

INSERT INTO employees (emp_id, emp_name) VALUES ('123', 'Alice Johnson');
INSERT INTO employees (emp_id, emp_name) VALUES ('456', 'Bob Smith');
INSERT INTO employees (emp_id, emp_name) VALUES ('789', 'Carol Davis');

Commit the changes

COMMIT;

Step 2: Create the Vulnerable Procedure

Here, we define a PL/SQL procedure called get_employee_info_vulnerable. This procedure takes an emp_id as input, builds a dynamic SQL query to fetch the employee’s name from the table, and outputs the name.

The problem arises from the dynamic SQL query (query := 'SELECT emp_name FROM employees WHERE emp_id = ' || emp_id;) where the user input (emp_id) is directly concatenated into the SQL query without validation, making it vulnerable to SQL injection.

Query:

CREATE OR REPLACE PROCEDURE get_employee_info_vulnerable (emp_id IN VARCHAR2) IS
   query VARCHAR2(500);
   emp_name VARCHAR2(100);
BEGIN
   query := 'SELECT emp_name FROM employees WHERE emp_id = ' || emp_id;
   EXECUTE IMMEDIATE query INTO emp_name;
   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END;
/

Step 3: Test the Vulnerable Procedure

When the procedure is executed with the input 123' OR '1' = '1', the dynamic SQL query becomes:
SELECT emp_name FROM employees WHERE emp_id = '123' OR '1' = '1';

Since the condition '1' = '1' is always true, this query returns all rows from the employees table. The procedure will display the first employee’s name, which is Alice Johnson, but the attacker could retrieve or manipulate more data if desired.

Query:

-- Enable output
SET SERVEROUTPUT ON;
-- Execute the vulnerable procedure with malicious input
BEGIN
   get_employee_info_vulnerable('123'' OR ''1'' = ''1');
END;
/

Output:

Explanation:

The procedure returns the first record in the employees table, which is "Alice Johnson," regardless of the emp_id provided. However, due to the SQL injection, the query fetched all records, not just the one where emp_id equals 123.

Conclusion

The PL/SQL injection is a critical security concern for the any Oracle-based application. Developers should prioritize input validation, use bind variables and apply best practices to the protect their applications from this type of attack.

By following preventive measures such as using the DBMS_ASSERT, limiting database privileges and conducting regular code reviews PL/SQL injection vulnerabilities can be minimized and mitigated.