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

thanks to everyone who posted in this topic,

the problem has been solved

my perl-DBI programmig was too dangerous
and could easily be exploited by webusers... my misstake!

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