Обсуждение: update phenomenon

(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 ?

thanks,

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

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@ESI.it