PL/SQL Attacks
Understand SQL injection attacks against PL/SQL
PL/SQL, like stored procedures, can be vulnerable to SQL injection attacks. When PL/SQL code integrates user input into a query and executes it, we encounter exactly the same problem we have when we build a classic dynamic query. In most cases, the attack is pretty similar to those presented in the tutorial. This article presents the different ways and situations where PL/SQL produres can be attacked. If you are interested to learn how to secure PL/SQL, take a look at the article about preventing PL/SQL against SQL injections.
Execute Immediate
This statement allows to execute a dynamic SQL query or an anonymous PL/SQL block. Let’s take a look at the code of a vulnerable PL/SQL procedure using EXECUTE IMMEDIATE statement. Keep in mind that this example was built for demonstration purposes only and you are not likely to find similar code in real situations.
This PL/SQL code returns a product's description (second parameter).
CREATE OR REPLACE PROCEDURE prodDescr(vname IN VARCHAR2, vresult OUT VARCHAR2) AS
vsql VARCHAR2(4000);
BEGIN
vsql := 'SELECT description FROM products WHERE name=''' || vname || '''';
EXECUTE IMMEDIATE vsql INTO vresult;
END;
The parameter is integrated in the query without being sanitized and a SQL injection vulnerability is created. Here is how it could be attacked.
Malicious user input.
A' AND 1=2 UNION SELECT password FROM members WHERE username='admin
Generated Query.
SELECT description FROM products WHERE name='A' OR 1=2 UNION SELECT password FROM members WHERE username='admin'
When the query is executed, the attacker gets the administrator’s password. It is important to mention that Oracle does not allow executing multiple SQL statements in a single dynamic SQL call. The attacker will therefore be mostly limited to UNION attacks and WHERE clause manipulations. Nevertheless, it is possible to batch queries in the same call to EXECUTE IMMEDIATE when anonymous PL/SQL blocks are used.
Anonymous PL/SQL blocks
Using anonymous PL/SQL blocks in an application is a fast way to get a query executed, however it can become extremely dangerous when using it with dynamic queries. It is especially interesting from the attacker perspective since it will be possible to inject multiple SQL statements. Here is an example of vulnerable anonymous block.
PL/SQL procedure to increase the price of a product by $1.
CREATE OR REPLACE PROCEDURE increasePrice ( vname IN VARCHAR2 ) AS
BEGIN
EXECUTE IMMEDIATE 'BEGIN UPDATE products SET price = price+1 WHERE name=''' || vname || '''; END;';
END increasePrice;
If you are wondering what an attack might look like for this particular PL/SQL here is an example:
Malicious user input.
zzzz'; DELETE FROM products WHERE 'a'='a
Generated query.
UPDATE products SET price = price+1 WHERE name'zzzz'; DELETE FROM products WHERE 'a'='a'
With this crafted input, the anonymous PL/SQL block will increase the price of all products named zzzz and it will then delete all products in the database.
Dynamic Cursors
PL/SQL allows dynamic cursors. They can be vulnerable to SQL injection too since they are dynamically generated just as execute immediate. Here is an example of vulnerable code.
This PL/SQL does nothing - Simply using DBMS_OUTPUT for demonstration purpose.
CREATE OR REPLACE PROCEDURE putlineDesc ( vname IN VARCHAR2 ) AS
TYPE rcursor IS REF CURSOR;
cur rcursor;
vdesc VARCHAR2(1000);
vsql VARCHAR2(4000);
BEGIN
vsql := 'SELECT description FROM products WHERE name=''' || vname || '''';
OPEN cur FOR vsql;
LOOP
FETCH cur INTO vdesc
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(vdesc);
END LOOP;
CLOSE cur;
END;
The attack is pretty classic for this case. In fact, it could be identic to the first attack example shown in this article. Let's take a look at it.
Malicious input
A' AND 1=2 UNION SELECT password FROM members WHERE username='admin
Generated query.
SELECT description FROM products WHERE name=A' AND 1=2 UNION SELECT password FROM members WHERE username='admin
Here again, all passwords would be returned to the attacker.
Limitations
When attacking vulnerable PL/SQL code, you need to be aware that only string parameters can be exploited. Because the procedure defines parameter's data type, you will not be able to insert SQL segments in numeric values. Another common limitation with PL/SQL attacks is that it will be impossible to use DDL (Data Definition Language) except if function or procedure is idenfied as a PRAGMA_TRANSACTION.
