Secure PL/SQL
Prevent SQL injection attacks against PL/SQL
As explained in the article about SQL injection attacks against PL/SQL, it is possible to create procedures vulnerable to SQLIA. This article details how you can secure PL/SQL code against SQL injections by making only a few programming changes.
Bind Variables
Bind parameters are by far the best way to protect your PL/SQL code against SQL injection vulnerabilities. In short, binding the variables of a procedure will provide great performance gain in addition to be totally secure. It is the equivalent of parameterized stored procedures. Here is an example of secure PL/SQL code which uses bind arguments:
Bind variables with EXECUTE IMMEDIATE.
CREATE OR REPLACE PROCEDURE testBind(vname IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'UPDATE products set price = price-1 where name = :1' USING vname;
COMMIT;
END;
The same logic is applied when using bind variables with dynamic cursors. Below is a secured version of an example presented in the article about PL/SQL attacks:
Bind variables with dynamic cusor.
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 CUST_LAST_NAME FROM DEMO_CUSTOMERS WHERE CUST_FIRST_NAME=:1';
OPEN cur FOR vsql USING vname;
LOOP
FETCH cur INTO vdesc;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(vdesc);
END LOOP;
CLOSE cur;
END;
In the two last examples,we explicitly indicate that we want to use bind variables.
Automated Bind Variables
To simplify developers' job but also to improve performance, PL/SQL takes care of using bind variables everywhere it can even if it is not explicitly indicated. Therefore, every reference to a PL/SQL variable is in fact a bind variable. Below is an example of such case.
PL/SQL is protected because variable reference is equivalent to bind variable.
CREATE OR REPLACE PROCEDURE plusTenPrice(vname IN VARCHAR2) AS
BEGIN
UPDATE products
SET price = price + 10
WHERE name = vname;
COMMIT;
END;
It is a great security enhancement but it does not change the fact that programmers will still have to be really careful when building dynamic queries within PL/SQL code.
Sanitize Input
The other way to secure the code would be to sanitize all input like covered in the article about input sanitization. Because all PL/SQL parameters are defined with a fixed data type, only text (VARCHAR) parameters can be vulnerable. Consequently, they are the only parameters that must be validated. The sanitazation can be done in the API code or within the PL/SQL code, but principle is the same.
Escaping Quotes
The first step to secure string parameters is to escape single quotes. It can be done by doubling them as shown below.
Escaping single quotes in PL/SQL.
vsecure := replace(param, '''', '''''');
An equivalent and less confusing version of the last replace.
vsecure := replace(param, CHR(39), CHR(39)||CHR(39));
Most cases will then be secured. However you might need to replace wildcards.
Wildcards
Oracle only uses two wildcards; characters “%” and “_”. When user input gets integrated with the LIKE operator, it is strongly recommended to escape those characters. It can be done by adding a chosen character before those wildcards and by adding an ESCAPE clause at the end of the query. The ESCAPE clause specifies the escape character used in the query (it can be any character). A common example would be as follows:
Escaping wildcards in PL/SQL (given a quote escaped variable "param").
vsecure := replace(param, '\', '\\');
vsecure := replace(vsecure, '_', '\_');
vsecure := replace(vsecure, '%', '\%');
vsql := 'SELECT description FROM product WHERE name LIKE ''' || vsecure || ''' ESCAPE ''\''';
In this case, the escape character is the backslash (\).
DBMS_ASSERT
Since 10g version, Oracle provide the DBMS_ASSERT package. It contains functions that can be used to validate user input. Even if the data source is considered as trusted, it is recommended to always use these functions when building dynamic queries. Here's a quick overview of this package's functions:
- NOOP - Returns string without validation (should never be used to secure against SQL injections).
- ENQUOTE_LITERAL - Enquotes a string literal and makes sure no individual single quote are found.
- ENQUOTE_NAME - Encloses a name in double quotes.
- QUALIFIED_SQL_NAME - Verifies that the input string is a qualified SQL name.
- SCHEMA_NAME - Verifies that the input string is an existing schema name.
- SIMPLE_SQL_NAME - Verifies that the input string is a simple SQL name.
- SQL_OBJECT_NAME - Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.
Examples below present a successful and a failed validation. In reality, validation should be done on variables or parameters but a literal string is used for demonstration purpose. Both examples are similar, but the first one is SQL injection safe. It could represent the value of a correctly sanitized malicious parameter.
Validating correct input.
SELECT description FROM products WHERE name=DBMS_ASSERT.ENQUOTE_LITERAL('abc'''' OR 1=1--');
Value returned.
ORA-06502: PL/SQL: numeric or value error
Now the failed validation (SQL Injection attempt).
Validating incorrect input (Contains an individual single quote).
SELECT description FROM products WHERE name=DBMS_ASSERT.ENQUOTE_LITERAL('abc'' OR 1=1--');
Error returned.
ORA-06502: PL/SQL: numeric or value error
As you can see, in the second example an error is thrown and the PL/SQL script would stop where the ENQUOTE_LITERAL function was called.
Reference
If you want more information about creating secure PL/SQL code, you might be interested by this Oracle's white paper. It is one of the best reference about the topic.
