July 2007

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

July 2007
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: SQL Injection

You’re invited to submit your Oracle-related questions to us at askdbspecialists07@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Carson in Atlanta: I keep hearing about a security threat called “SQL injection.” What is it? What risks does it present, and how can I protect my Oracle system?

Gary Sadler of the Database Specialists team responds: SQL injection is a way of using dynamic SQL to gain access to restricted information stored in a database. SQL injection can also be used to circumvent security mechanisms in order to cause the database software to malfunction. In some cases the server on which the database runs can be impacted as well. The effects can be disastrous to system uptime and the confidentiality of the data stored in the database.

SQL injection exploits are a dime-a-dozen. Even the Oracle database itself is not immune. For example, in many Oracle 9i and even Oracle 10gR1 databases without the current Critical Patch Update, a user with just the CREATE SESSION and CREATE PROCEDURE privileges can gain DBA access very easily using SQL injection. First, the user (we’ll call him Noah) creates a function:

authid current_user as
pragma autonomous_transaction;

Next, Noah performs the SQL injection using a commonly available Oracle PL/SQL supplied package (the name is obfuscated here):

select sys.dbms_something('''||noah.sql_inject()||''','') from dual;

Bingo! Noah is now a DBA. Pretty scary, huh? Fortunately this security hole was closed in later releases of Oracle 9i and Oracle 10g. But this is just one example of SQL injection. There are many others, and they are pretty easy to find. Some involve a particular third-party tool or Oracle functionality. Others employ a technique that can be used with a wide variety of systems including your own.

Let’s look at a more commonplace example. Suppose you application runs the simple SQL statement:

SELECT name,dept FROM employee WHERE name='&Name';

Here “&Name” is a value input by the user. Instead of playing by the rules and merely inputting a name and getting back a department, the user inputs this:

x' union select name,ssn from employee where 'x'='x 

This transforms your SQL statement into this:

SELECT name,dept FROM employee WHERE name='x' 
SELECT name,ssn FROM employee WHERE 'x'='x'

Now instead of getting back a department for one employee as you intended, the user gets back a Social Security Number for every employee. The private information in your database has just been compromised.

How can SQL injection be prevented? There’s no magic bullet, but here is a list of action items that can help limit your risk:

  • Those developing software for your company must be kept aware of the risk and held accountable for producing code that does not meet standards set by the organization’s information security policy.
  • Software should use bind variables instead of insertion of literal values wherever possible.
  • Software must validate all input from users, especially when the input data will be inserted into a query as a literal and not a bind variable value. Certain Oracle reserved words and character combinations cannot be allowed. Good candidates are ||, –, ;, SELECT, UNION, FROM, WHERE, GRANT, REVOKE, CREATE, ALTER, EXEC, INSERT, UPDATE, DELETE, DROP, and TRUNCATE.
  • When it comes to access, your mantra should be “least privilege”. It won’t earn you a lot of friends and will undoubtedly cause some stress while getting access tuned to the correct level, but if you set the right tone and expectations, people will adjust.
  • Never rely solely on application-level security. Assume that a user will find their way to the database outside of the customary channels.
  • While hackers tend to get the most press, your greatest concern should be with authorized users – employees, consultants, partners, and the like. Anyone requesting authorization to access the database should be vetted as much as possible.
  • Keep the database software patched. As exploits are discovered, Oracle releases interim software patches (a.k.a. Critical Patch Updates) and all-inclusive patchsets. Regardless of your version, take it up to the highest patch level supported for your platform. The security landscape was enhanced a great deal in Oracle 10g, so that would be the version of choice for the most security conscious.
  • Disable accounts that are not currently in use. When a new Oracle 10g database is created, all of those built-in accounts are disabled by default. That’s not the case in earlier versions, however.

The risks imposed by SQL injection attacks are greater now than ever. Take the time to discover where the vulnerabilities are in your systems. Employing basic security strategies, along with maintaining the proper level of awareness, will go a long way toward prevention.

Tracing Individual Users in Connection-pooled Environments

Terry Sutton from Database Specialists will be giving a presentation on “Tracing Individual Users in Connection-pooled Environments with Oracle 10g” at the summer conference of the Northern California Oracle Users Group. This meeting will be held in San Ramon on August 16, 2007.

This presentation will focus on how the SQL trace facility helps DBAs diagnose performance problems and make applications run faster as well as how this facility has been enhanced in Oracle 10g. Terry will walk you through real-life examples so you can apply the concepts to your own environment.

Don’t miss the accompanying white paper on this topic at http://dbspecialists.wpengine.com/presentations/tracing_ind_sessions.html.

Information on the NoCOUG conference, along with a full agenda, can be found at http://www.nocoug.org/next.html.

Linux Adoption Continues to Grow

As noted in some recent issues of The Specialist, Linux is finding its way into more data centers not only as a web server, but as the database and application server. The Wall Street Journal featured an article on June 19, 2007 on this subject titled: Linux Shoots for the Big League of Servers—Low-Cost Operating System Pushes Into Microsoft, Sun Strongholds. The article discusses how Linux has had a “great run” but that to continue growing, it needs to move beyond hosting email and web pages and make further inroads into serving the ERP systems and databases that have traditionally been hosted on Unix servers.

By one measure—server revenue—Linux has grown from 6.4% of total revenue in 2003 to 12.7% today. That is especially impressive as the cost of Linux servers is significantly lower than the cost of servers that run legacy operating systems. Other signs that Linux is becoming more mainstream are the support Linux is getting on the application side from companies like Oracle and on the hardware side from HP and IBM. The bottom line is—as Judy Chavis, director of enterprise marketing at Dell said—“Linux is growing up.”

If you’re not familiar with Linux yet, don’t miss our guide to “Installing and Configuring Oracle Database 10g on the Linux Platform” at http://dbspecialists.wpengine.com/presentations/oracle10glinux.html.

Oracle Data Guard Overview

We have implemented Oracle’s Data Guard for many of our customers who use standby databases for high availability. According to Oracle Corporation’s official description, “Oracle Data Guard is the management, monitoring, and automation software infrastructure that creates, maintains, and monitors one or more standby databases to protect enterprise data from failures, disasters, errors, and corruptions.”

Data Guard manages both physical and logical standby databases, and can be used along with other high availability solutions such as RAC and RMAN. There are three levels of protection that can be used, allowing companies to weigh the importance of performance and affordability against tolerance for data loss.

Data Guard in Oracle Database 11g, according to Oracle Corporation, has been enhanced in a number of ways, including:

  • Easier to implement and manage using either the Data Guard broker command line interface or Enterprise Manager Grid Control.
  • Adds new ways to detect corruptions that avoid data loss and extended down time.
  • Flexible configuration options for fast, automatic failover in both Maximum Availability and Maximum Performance protection modes.

For a good general overview of Data Guard and enhancements in Oracle 11g, check out http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardOverview.html. For a lot more detail, there is also a 33-page white paper at http://www.oracle.com/technology/deploy/availability/pdf/twp_dataguard_11gr1.pdf.

Leave a Reply

Your email address will not be published. Required fields are marked *