Re: update phenomenom

On Fri, 6 Jun 2003, Henrik Steffen wrote:

>
> Hello all,
>
> I have a table consisting of about 450.000 rows
> with a unique primary key char(9)
>
> kundennummer CHAR(9) unique primary key
> ... some fields...
> miano CHAR(6)
>
> Today someone issued an
>
> UPDATE table SET miano='071002' WHERE kundennummer='071002883';
>
> and managed to UPDATE all the 450.000 rows, updating
> the miano to the value '071002' by issuing this command.

Urgh, nasty.

>
>
> The update is generated through a web-based intranet-solution,
> unfortunately I didn't have a postgresql-logfile for this, but
> I can see from the webserver logfile, which scripts was run
> at the particular time.

That's bad news. Can you rule out operator error, i.e. running the update
without a where clause from a psql session?

>
> For me it's almost 99.9 % sure, that it's no error in the
> perl-program. There is only one command issuing exactly
>
> SQL("UPDATE $table SET $daten WHERE kundennummer='$kundennummer';");
>
> where $table is the table-variable
> $daten is what is to be set
> $kundennummer is the client-number, which is checked before to match exactly
> 9 digits.

How is $daten generated. Is there chance of a SQL injection attack? For example
are you checking the data value you are assigning? Can you rule out $daten
being assigned a value of: miano='071002'; (i.e. the equivalent of: $daten =
"miano='071002';"; ) ?

> Could there be any postgresql-server-side explanation for this phenomenom ?
> Perhaps
> anything about corrupted indexes, or anything?

Anything is possible I suppose.

--
Nigel Andrews