Re: update phenomenom

On Fri, 2003-06-06 at 04:58, Henrik Steffen wrote:
[snip]
>
> 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.

As many others have already mentioned, this is probably an interpolation
problems where someone maliciously (or unwittingly) entered something
that broke the code.

>
> For me it's almost 99.9 % sure, that it's no error in the
> perl-program. There is only one command issuing exactly

Are you sure you clean everything properly before interpollation?

>
> SQL("UPDATE $table SET $daten WHERE kundennummer='$kundennummer';");

This is dangerous. Even following proper quoting conventions, using
placeholders for data binding is much more secure. As well as having
many other benefits.

    #  See DBI docs for more methods relating to this.

    my $sth = $dbh->prepare(qq{
        UPDATE "$table" SET "$daten" = ? WHERE kundennummer = ?
    });
    $sth->execute( $daten_value, $kundennummer );

Unfortunately object identifiers will still need to be interpolated
directly. Make sure to 'clean' them with something like tr/a-zA-Z0-9_
//cd to remove all non-valid character before interpolating them. Just
be aware of case folding when quoting object identifiers :).

Another option is querying the schema to identify valid names and only
allowing those.

>
> 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.

Is $daten checked in the same way? To make sure no illegal chars are
passed (as mentioned in other posts).

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

Very doubtful.


HTH