Foiling lateral SQL injection threats for Oracle

Oracle Database Tips by Donald BurlesonApril 30, 2015

Any Oracle database that is deployed on the web faces the threat of constant attack. Several years ago, I hung an Oracle "honey pot" to test this, and within 15 minutes, hundreds of automated "attack bots" had visited, looking for vulnerabilities.

From XKCD

Let's look at the new "lateral" SQL injection in the context of general access hardening techniques.

Understanding SQL Injections

SQL injection threats are not unique to Oracle, as all relational databases suffer this exposure, but there are some things that you can do to harden your Internet entry points.

Oracle Forensics author Paul Wright notes that SQL injection threats are everywhere within Oracle (from his book):

"The most common bugs currently found in Oracle products are SQL Injections especially in PLSQL procedures.

I have found approximately 30 such SQL injections in Oracle's databases to date reported directly to Oracle. David Litchfield has found hundreds over the years like the one below. In order to defend from SQL injection issues it is important to know how the vulnerabilities can be exploited."

So, what does a SQL injection "look like"? A picture is worth a thousand words, so to understand a SQL injection, watch this online video, allegedly of an actual criminal SQL injection attack.

Linux SQL Injection - Watch This video shows a SQL Injection attack in Linux, "based on a true story", giving criminals complete directions to aid them in their acts.
SQL injection on the web - The video below is an excellent overview of SQL injection techniques.

Oracle security analyst David Litchfield just published an interesting paper with the provocative title "Lateral SQL Injection: A New Class of Vulnerability in Oracle".

Security guru Pete Finnigan notes that the "lateral" SQL injection threat is quite real:

"there is a real threat, as there are a number of issues:

1) It's a new way to inject via dates and numbers

2) Just because dynamic code appears to not have an attack vector doesn't mean that it cannot be attacked.

3) Any principal that says a concat or double vertical bar is safe because the data is not passed in as a parameter or read from the database is flawed."

Steve Karam notes: "In a traditional SQL injection you replace a string input value with a terminator and your own SQL code. For instance, someone has this code:

select * from table where first_name = '&name';

But what if I replace &name with: '; drop table obj$; --

That will terminate the select, attempt to drop a table, and then comment the final single quote.

The problem with this kind of attack is when you want to use a NUMBER or DATE column. In those cases, you have built-in datatype protection.

With lateral SQL injection, you can trick Oracle into accepting a string as a DATE by changing NLS_DATE_FORMAT, for instance. You will need ALTER SESSION privileges to do this, but DBAs usually hand this out like candy.

This could be bad for thousands of applications that have already been written, because even if the coder tested for string injection attempts, they rarely would be testing for date/number injection attempts.

Steve Callan notes: "A "lateral SQL injection" is the shifting or transformation of the representation of one datatype to another. Note the Litchfield article, where he says he laterally shifted the nls_date_format to a character string."

"Thus we can see it's possible to do two things. Firstly an attacker can pass off arbitrary SQL as a DATE type parameter; secondly an attacker can laterally influence the SYSDATE function using ALTER SESSION and exploit procedures and functions that don't even take direct user input.

An Oracle ACE notes: "A SQL injection (in general) takes advantage of poorly written code to substitute a section of malicious SQL for the dynamic SQL that is supposed to be run.

My understanding of lateral SQL injection is that it is a method of hijacking non-dynamic SQL to accomplish the same objective. I am not an expert on this subject, but it appears that lateral SQL injection requires certain conditions to be met. The first condition is that the user has the ability to modify session level settings, such as the NLS_DATE_FORMAT or the NLS_TIMESTAMP_FORMAT.

I suspect that NLS_SORT or NLS_TIMESTAMP_TZ_FORMAT could also be used. The second requirement is that the called procedure uses a value with a value that can be manipulated by the user changing the session level setting. The third condition of course is that the user calling the procedure know the contents of the procedure, so that the user knows what session level parameter to modify.

So, if the user knows the session parameter that is used in the called procedure, the user can modify the format of the session level parameter to inject malicious SQL into the session parameter that the called procedure is using. In the example given by David Litchfield, the user injects malicious SQL into the NLS_DATE_FORMAT, so that when the stored procedure is called that uses sysdate, it executes the malicious SQL that is part of the user modified NLS_DATE_FORMAT.

It would appear to be fairly simple to avoid lateral SQL injection. All that is necessary is to set any session level parameter that may affect the procedure within the procedure itself. In the case of a procedure that uses a value that is affected by the NLS_DATE_FORMAT, the stored procedure should set the NLS_DATE_FORMAT within the stored procedure itself, or better yet don't use ?EXECUTE IMMEDIATE?."

NOTES: How to Prevent SQL Injection Attacks

Bind Variables - The first one is rather simple - do not use dynamic SQL. If possible, use a construct like:

select field1, field2, field3
into :1, :2, :3
from table
where ?.

and then pass parameters to it. This way, the hacker can't inject an extra string after the predicate of the query.

Special Characters - Filter out the special characters, such as quotation marks.

No Pass-through Queries - Do not pass the SQL as a pass-through, rather, use bind variables. Using concatenation (such as "&" in Visual Basic and "=+" in JavaScript) may be convenient, but allows hackers to inject.

No Sweeping Privileges - Carefully review the privileges granted to the users. Remove sweeping privileges such as ANY. Utilize the principle of least privileges - if a users does not need a privilege, don't grant it. Without required privilege, the hacker can't see much data.

Invoker Rights Model - Using the Invoker Rights model in procedures and functions makes sure the calling user's privileges are used, reducing the chances of the hacker getting into a user with large privileges.

Datatype - Use proper datatypes for the parameters. If the parameter is numeric, define a numeric value for it. Some developers tend to declare everything as character, because it makes coding easier, but this invites hackers.

DBSNMP Password - Change the default passwords for the DBSNMP user, a user with sweeping privileges such as SELECT ANY DICTIONARY. Better yet, use a different username for the Oracle Intelligent Agent user, following the technique described earlier in this chapter under DBSNMP User Security.

Function for Password Management - In the application password management, use a function described in the appropriate section to authenticate users, do not select directly from a table.

Encryption - Finally, encrypt the sensitive data. Even if the hacker gets through via SQL Injection, he or she will not be able to see anything of value.

* Review the chances of SQL Injection and take steps to prevent an attack. As we saw earlier, the technique is rather easy and often results from bad coding practice and poor privilege management.

For a complete treatment of the topic of Oracle security on the web, see these books and resources: