Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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:

Also, see my web pages on SQL Injection attacks:

 

 

 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.