Pete Finnigan's Oracle Security Weblog

There was a recent posting to the bugtraq mailing list about an issue with MS Access having a flaw in the way unions work. Thanks to Josh Wright of the SANS Institute for pointing this one out to me.

I should make clear first that I don't agree with this as being an issue or bug. It is the way SQL was intended to work in union statements.

The Access and Oracle issue raised is that the poster suggests it is a bug if a select such as:

select login,password from users
union
select password,login from users
/

is allowed to work. He says that the columns should not be allowed to be swopped. He suggests that then SQL Injection can be used for instance to return passwords in a screen that displays only usersnames.

I didn't look for the MS Access posting as I was interested in the Oracle issue. The first post on 30th September 2004 is where the poster talks about a fellow student at UMR that tried the Access issue in Oracle. He found in this post that if one or two columns are mis-ordered in a union select then there is no error, after that there is (in his words). He gave an example like the above and suggested that this is an issue because an SQL Injection attack could return passwords where usernames are expected as i suggested above.

This post is followed by another post on 01 October 2004 by the same poster who now says he is wrong about MS Access and how unions work. He still suggests that if you are doing a select of two different fields via a union that they should not be headed under one column name to prevent SQL Injection.

The final post in the series is by a different poster who sets the story straight and points out that this is in fact how unions work in the SQL standard. He points out that by limiting SQL such as:

"select name, phone from contacts union select name, mobile from contacts;


or even


select name, phone from employees union select name, phone from contacts;"

would break applications. He also points out that even if the changes suggested by the first poster were made then they could be got around for :

"select login, password from foo union select pasword, login from foo; "

by doing


"select login, password from foo union
select password as login, login as password from foo; "

He also shows that mis-matching more that 2 columns in a union does not break Oracle after all with a simple SQL example.

The first poster probably got an error with more than two columns because the data types of his columns were different. If a union returns two columns from two select statements in the same position and they are a different data type then an error will be returned.

OK, so not a bug after all but what lessons can we learn and why am I talking about this issue. The original poster was thinking like a hacker and could see an opportunity to exploit a union statement by using SQL Injection to get passwords from a field that should return usernames. This is a good example of thinking like a hacker to look for security issues in your database or how an employee may think to try and exploit your databases and applications. He might have been wrong in his argument but this is one of the reasons that SQL Injection can work, of course not the real reason. The main problems are due to the fact that dynamic SQL or dynamic PL/SQL is possible in some applications. The issue above also illustrates why it is so difficult to protect against SQL Injection in cases like this as the SQL sent to the server is legal SQL and it quite rightly returns data. Even column data type differences can be got around by casting. How can you protect against these types of attack?

The first way is to not allow any type of dynamic SQL or dynamic PL/SQL, particularly SQL that is created with a concatenated string. If not then parse input. If possible use numeric columns and data types for the dynamic part of an SQL statement to prevent extending or shortening the original statement. I wrote a three part paper on SQL Injection for security focus. Links to the two part paper "SQL Injection in Oracle" and the one part paper "Detecting SQL Injection in Oracle" can be found here. These papers talk at length about the issues of SQL Injection in Oracle and give some simple examples of how it works.