Using PL/SQL code in your system has many advantages. However, it can be insecure if it isn’t done right. In this article, I’ve outlined a few practices you can follow to improve the security of your Oracle PL/SQL code.
- Use Packages Instead of Procedures or Functions
The easiest way to implement PL/SQL code is to write a function or a procedure. This often takes only a few lines of code to compile. Functions and procedures are easy to call as well. However, it’s much better to store your PL/SQL code within packages rather than within procedures or functions. There are many reasons for this, and even Steve Feuerstein, PL/SQL expert, recommends that packages should always be used (http://stevenfeuersteinonplsql.blogspot.com.au/2016/05/why-you-should-always-use-packages.html).
For security, it’s better to use packages. It’s harder for attackers to get your code if it’s in a package. If the code is in a procedure or function, attackers can query the database and get your source code. If it’s in a package, however, they can only see the package specification, not the package code.
- Move Your Comments to the Package Body
If you’re already using PL/SQL packages, that’s a big step to improving security.
Common best practices for developers include commenting on your code. This helps improve maintainability and understanding. However, if an attacker reads the comments, they can understand much more about your code and your system. They may find other systems’ names and details, interface structures, and even employee names. So, to make your comments harder for attackers to see, move them into the package body rather than the specification. Don’t leave all of the comments for the functions within the package specification, as they are easier for attackers to find.
- Use Bind Variables When Possible
When writing PL/SQL code, it’s better to use bind variables instead of literal statements.
A bind variable is a placeholder in an SQL statement that must be replaced with a valid value when the code is run. Bind variables not only improve performance, they improve security. They make it harder for people who get access to the code to understand what it does and how to make it work without the bind variable set.
Consider this code:
SELECT first_name, last_name FROM employee WHERE employee_id = 100;
There may be a specific reason to use employee ID 100. However, turning this into a bind variable could improve performance and improve security by reducing the vulnerability to SQL injection attacks, as seen below.
SELECT first_name, last_name FROM employee WHERE employee_id = :emp_id;
- Don’t Hardcode Data
In many simple PL/SQL code samples, data is hardcoded. This makes it easy to understand, but doesn’t make it secure.
For example, passwords or other important database information are often shown with code examples. Storing this kind of information in your PL/SQL source code is not a good idea. If the code gets into the wrong hands, it becomes easier for attackers to get into your system. In other words, it’s good practice to avoid hardcoding important values into the source code and storing them elsewhere. It’s the same for PL/SQL packages. Store them in configuration files or other, more secure areas.
- Use DBMS_ASSERT Instead of || or CONCAT
An easy way to use input parameters in your SQL statements is to use concatenation, either using the double pipe character || or the CONCAT() function.
For example:
sqlQuery := ‘SELECT department_id, department_name, department_manager_id ‘ || ‘FROM department ‘ || ‘WHERE department_name LIKE ‘‘%’ || pSearchString || ‘%’’’;
This query finds department records that match a search string parameter. This is open to SQL injection attacks, however.
If you can’t use bind variables, then use the DBMS_ASSERT package to handle input values (such as the pSearchString above).
The DBMS_ASSERT package includes several functions:
* NOOP: performs no error checking and returns the string exactly as provided.
* SIMPLE_SQL_NAME: checks the string to see if it matches an SQL name format.
* QUALIFIED_SQL_NAME: checks the string to see if it matches a qualified SQL name format.
* SCHEMA_NAME: checks the input matches an existing schema.
* SQL_OBJECT_NAME: checks the input matches an existing object.
* ENQUOTE_NAME: encloses the value in double quotes and checks for existing quotes.
* ENQUOTE_LITERAL: encloses the value in single quotes and checks for existing quotes
You can use many of these functions to check the input values before using them in your code.
- Use Definer Rights or Invoker Rights Correctly (in Oracle 12c)
If you use Oracle 12c, you can take advantage of a new feature related to rights. Packages can be run as either the invoker (who calls the package) or the definer (who owns the package).
These are often two different users with two different sets of privileges. You can specify which option to use (either invoker or definer) when creating your package to ensure it is called the way you want it to be called. This can improve security. For example, if the package is declared with invoker rights, it runs with the limited privileges of the user who calls it and not the extended privileges of the developer who created it. Usually, definer rights should be avoided, as they have more open privileges than those calling the package. However, check which type you should use for each case.
Those are our recommendations for improving PL/SQL package security. They aren’t hard to do, but they will make a big difference in the security of your application and your system. To shield your sensitive data you can also try this How-to: Check Failed Login Attempts in Oracle Database.
Check out my previous article to get the ultimate list of things you should never store in your Oracle database.
