Re: Oracle 9i Union Flaw

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_at_wsr.ac.at        |	-- Gordon Schumacher,
__/   | http://www.hjp.at/   |     mozilla bug #84128