Oracle 9i Union Flaw

[Brandon Petty]

A fellow student, here at UMR, has tested the MSAccess 2K/XP Union Flaw on Oracle 9i.
His name is Muthukumar Narayanan: mnmr6@umr.edu .

He found that Oracle 9i has the same problem as Access... but it is a little different.
He found that you can select up to 2 miss ordered columns for the results to be mixed.

An example:

select login, password from flaw union select password, login from flaw;
select login from flaw union select password from flaw;

It seems that if you do more than 2, it will return an error. Also, in the last case... this is a prime example of something that an SQL Injection attack could really take advantage of. Again, you are asking for the login name... but are returned passwo
rd data. What happens when a Server Side Script tries to print out all of the login names since it is pointed at the login field? hmmm...

You can contact him for more information on the Oracle 9i issue.

I would also suspect MySQL and Postgresql to have something similar since this seems
to be an issue with more than one database server. This has not been tested.

Note: Oracle has not been contacted. This was brought to my attention 15 minutes ago after we had discussed the flaw I found in Access.

Thanks,
Brandon P.

[Brandon Petty]

In-Reply-To: <20040930224011.21783.qmail@www.securityfocus.com>

>A fellow student, here at UMR, has tested the MSAccess 2K/XP Union Flaw

If you are wondering about the Access Union Flaw... I posted something that was, for the most part, incorrect about Access and how it handles Unions. There are a few quirks... but nothing that should have been posted. Mainly, my bad.

I still think that if you are going to union two fields... that the results should not be stored under one of those fields headings if they are different. Like doing a union on Login and Password. It would be best to return the results under something l
ike LoginKey instead of Login. That way if I do an SQL Injection by using the ever popular Union operator... I know that I am not going to return other data if I print out the contents of the Login results. This of course would have to be done by the db
s.

The issue with Oracle 9i not allowing you to miss match more than two fields is still strange. I don't remember what the exact errors where. This could be a flaw in Oracle... but I have not looked into this. I wouldn't think it would matter how many di
ffering fields you union on. But then again... I really haven't look into Oracle to say too much.

[Peter J. Holzer]

--+HP7ph2BbKc20aGI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2004-10-01 17:55:30 -0000, Brandon Petty wrote:
> I still think that if you are going to union two fields... that the
> results should not be stored under one of those fields headings if
> they are different.=20

That would be a major change in how union works in SQL - I think if this
was included in the next SQL standard, the committee would be lynched.=20

That would break code like

select name, phone from contacts union select name, mobile from contact=
s;

or even=20

select name, phone from employees union select name, phone from contact=
s;

if you check wether the full name (schema.table.field) is different.

It also doesn't help against SQL injections as the attacker can always
rewrite his query from=20

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

to

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

> The issue with Oracle 9i not allowing you to miss match more than two
> fields is still strange.

I can't reproduce that:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> create table foo (a int, b int, c int, d int);

Table created.

SQL> insert into foo values(1, 11, 111, 1111);

1 row created.

SQL> insert into foo values(2, 22, 222, 2222);

1 row created.

SQL> insert into foo values(3, 33, 333, 3333);

1 row created.

SQL> select * from foo;

A B C D
---------- ---------- ---------- ----------
1 11 111 1111
2 22 222 2222
3 33 333 3333

SQL> select a, b, c, d from foo union select d, c, b, a from foo;

A B C D
---------- ---------- ---------- ----------
1 11 111 1111
2 22 222 2222
3 33 333 3333
1111 111 11 1
2222 222 22 2
3333 333 33 3

6 rows selected.

Works as expected.

hp

--=20
_ | Peter J. Holzer | Shooting the users in the foot is bad.=20
|_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't.
| | | hjp@wsr.ac.at | -- Gordon Schumacher,
__/ | http://www.hjp.at/ | mozilla bug #84128

--+HP7ph2BbKc20aGI
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQWFIj1LjemazOuKpAQHzSwXTB0hhziHGOh5nIkI4yb L3l7IJ6Srk2wlg
1X98r4Q1CoyevM+pbRZQ7+IRI6lJOdCaubhFmFWeOXLuZ0N8ep kIyWAj+MrTah3i
is2ASiYSb3hrleVNuG2/fEtBQxNFNWNwMQSJ5vGafJMmJpERcxpdcdbBZlhVAZSx
uj79RkPEnsQfnvUFYIyNCrYYjhy6Gf89aWlOZdk13g21AOm42l iggRQZQyebnro7
+zuUaOwg5oXb2Z9NgvqtQsiD6w==
=ZH+c
-----END PGP SIGNATURE-----

--+HP7ph2BbKc20aGI--