Re: update phenomenon

On Fri, 6 Jun 2003, Henrik Steffen wrote:

> (I sent this before, but it somehow didn't get on the list)
>
>
>
> 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.
>
>
> 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.
>
> 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.
>
>
> Could there be any postgresql-server-side explanation for this phenomenom ?
> Perhaps
> anything about corrupted indexes, or anything?
>
>
> Or could it possibly be that someone entered something like
>
> $daten="miano='071002';";
>
> note the ';'
>
> Trying this as a test, I get an error and no update done....
>
> Any ideas / comments ?

1) log the queries before executing them in the perl program: have
it build the query into a variable (say, $q) and log before
executing it.

2) build the query string in a SQL-safe way: I mean, don't trust
user input to be SQL correct. For example I'd say that

$daten = "miano='071002'; select * ";

will produce the effect you saw. You don't say where the input
comes from, I assume a HTML form. Check the input for any weird
character (; being only one of them).

3) I'd suggest to post to another list (perl? cgi?): the way you
put it makes it appear unrelated to PostgreSQL. First, identify
the offending query (point 1) then, if it looks sane, but leads
to unexpected results, post again here.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it