Re: Oracle 9i Union Flaw

["Peter J. Holzer" <hjp () wsr ac at>]

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. 

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. 

That would break code like

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

or even 

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

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 

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

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

Attachment: _bin
Description: