Thread: update phenomenom
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?
--
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
--------------------------------------------------------
Anything is possible but I have never seen this come up as a known PostgreSQL
problem on these boards.
Without knowing the specifics of your code (are your using the DBD::Pg
interface or a system call to psql or ??) I recommend reading the articles
that Google returns for "sql injection attack" and then double-checking _all_
of your error verification code (an "attack" could in some cases be a user
typo that causes undesirable results).
Here's an example (I'm not claiming that your code works at all like this):
Suppose you carefully check $kundennummer but $daten is created by taking the
user's input and prepending "miano=". Then suppose you feed that to psql
using a system call. An unfortunately placed ; could ruin your day.
You might expect to generate:
update foo set minao=12345 where kundennummer = '071002883';
(should update one record)
but if someone types 12345; you will end up with:
update foo set minao=12345; where kundennummer = '071002883';
which will first update all records (first statement) and then generate a
parsing error on "where kundennummer = '071002883';"
Also, are you sure that the update came from the web app? Is it possible that
someone (who now wants to remain anonymous) screwed up a manual update in
psql?
Cheers,
Steve
On Friday 06 June 2003 1:58 am, 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.
>
>
> 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?
>
>
> --
>
> 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
> --------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
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.
>
>
> 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';");
You expect that $daten contains precisely
miano='071002'
I guess the 071002 is coming from an input field in a form, no? What if
someone managed to get
miano='071002';'
into $daten by entering 071002'; into the field?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Friday 06 June 2003 10:58, Henrik Steffen wrote:
> Hello all,
(...)
> 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.
What exactly does the function SQL() do? Is it possible that the script could
receive input along these lines?
SQL("UPDATE table SET manio='071002'; WHERE kundennummer='071002883';")
> Could there be any postgresql-server-side explanation for this phenomenom ?
> Perhaps
> anything about corrupted indexes, or anything?
Any idea what version the server is running?
Schöne Grüße
Ian Barwick
barwick@gmx.net
ok, you are right. I checked it once more, and now it worked.... thanks for your help, -- 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 -------------------------------------------------------- ----- Original Message ----- From: "Jan Wieck" <JanWieck@Yahoo.com> To: "Henrik Steffen" <steffen@city-map.de> Cc: "pgsql" <pgsql-general@postgresql.org> Sent: Saturday, June 07, 2003 10:03 PM Subject: Re: [GENERAL] update phenomenom > Henrik Steffen wrote: > > hi, > > > > I tried both: > > > > $command="UPDATE table SET miano='12345'; commit; ' WHERE ... ;"; > > > > > > and > > > > $command="UPDATE table SET miano='12345'; -- ' WHERE ... ;"; > > > > > > neither worked. > > What do you mean "neither worked"? > > the second form works just like a charm. I checked it with PG 7.3.3, DBI > 1.14 and DBD::Pg 0.95. > > > > > it just gives an error and dies. > > no update done. > > What error and where is that coming from? If I use the comment hack it > doesn't show any message in the postmaster log. It is totally happy with > it and shows exactly the symptom you described. > > > I looked at man DBI, but couldn't find any restriction > > for $dbh->prepare() .... I am not sure, if it's possible > > to pass multiple statements to the prepare-method. > > I'm not too familiar with Perl, but if DBD blindly forwards the query > given in the string argument of prepare() into libpq's pg_exec() ... and > it is supposed to do it that way ... then it is possible to pass > multiple statements. This is a long standing functionality of the > frontend/backend protocol. > > > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # >
Henrik Steffen wrote: > hi, > > I tried both: > > $command="UPDATE table SET miano='12345'; commit; ' WHERE ... ;"; > > > and > > $command="UPDATE table SET miano='12345'; -- ' WHERE ... ;"; > > > neither worked. What do you mean "neither worked"? the second form works just like a charm. I checked it with PG 7.3.3, DBI 1.14 and DBD::Pg 0.95. > > it just gives an error and dies. > no update done. What error and where is that coming from? If I use the comment hack it doesn't show any message in the postmaster log. It is totally happy with it and shows exactly the symptom you described. > I looked at man DBI, but couldn't find any restriction > for $dbh->prepare() .... I am not sure, if it's possible > to pass multiple statements to the prepare-method. I'm not too familiar with Perl, but if DBD blindly forwards the query given in the string argument of prepare() into libpq's pg_exec() ... and it is supposed to do it that way ... then it is possible to pass multiple statements. This is a long standing functionality of the frontend/backend protocol. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Saturday 07 June 2003 20:18, Henrik Steffen wrote: > Hi Ian, > > well, I by now believe that it has got to be a human error > (hum, well actually MY error) > > However, I would like to reproduce the error, so I can > understand what I can do against it. > > So, even if it's slightly off topic for pgsql-general, maybe > someone knows , how it was possible to trick out the > DBD::Pg using > > $sth=$db->prepare($command); > $sth->execute(); > > I did not succeed in passing two statements to the > prepare-command. Neither using "commit;" nor using > "--" as a seperator. "--" is a comment not a seperator > But from the result I got, there must have been > a way to do it. > > Any hints? Given the interpolated string used to create your SQL statement: UPDATE $table SET $daten WHERE kundennummer='$kundennummer'; I could imagine the following scenarios (not tested) causing the update to succeed silently: a) $table = "table"; $daten = "miano='071002'; SELECT 1 FROM table "; $kundennummer = "071002883"; b) $table = "table"; $daten = "miano='071002'"; $kundennummer = "071002883' OR 1='1"; Whether that is what actually happened is another question; there may be other possibilities, possibily also depending on how the parameters get from the web interface into the SQL statement. If you used place holders / bind variables (recommended practice) this kind of thing should not happen; doing just this for example: $command = qq/UPDATE $table SET $daten WHERE kundennummer=?/; $sth=$db->prepare($command); $sth->execute($kundennummer); should prevent the second example from executing. Motto: never trust user input, even if it is your own ;-) Ian Barwick barwick@gmx.net
Hi Ian,
well, I by now believe that it has got to be a human error
(hum, well actually MY error)
However, I would like to reproduce the error, so I can
understand what I can do against it.
So, even if it's slightly off topic for pgsql-general, maybe
someone knows , how it was possible to trick out the
DBD::Pg using
$sth=$db->prepare($command);
$sth->execute();
I did not succeed in passing two statements to the
prepare-command. Neither using "commit;" nor using
"--" as a seperator.
But from the result I got, there must have been
a way to do it.
Any hints?
--
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
--------------------------------------------------------
----- Original Message -----
From: "Ian Barwick" <barwick@gmx.net>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Saturday, June 07, 2003 7:34 PM
Subject: Re: [GENERAL] update phenomenom
> On Saturday 07 June 2003 17:46, Henrik Steffen wrote:
>
> (in answer to my queries):
> > > What exactly does the function SQL() do? Is it possible that the
script
> > > could receive input along these lines?
> > > SQL("UPDATE table SET manio='071002'; WHERE
kundennummer='071002883';")
>
> > sub SQL {
> > my $command=shift;
> > ...
> > $sth=$db->prepare($command);
> > $sth->execute();
> > ...
> > }
>
> > > Any idea what version the server is running?
>
> > running latest postgresql 7.3.3
>
> Well, SQL() looks fine to me [*]; I would look at the path the query takes
> from the web interface to the backend and whether the possibility of human
> error (e.g. a semicolon in the wrong place not being detected) can be
> definitively ruled out before looking for bugs in the server.
>
> [*] but you might want to consider using placeholders and bind values.
>
>
> Ian Barwick
> barwick@gmx.net
>
On Saturday 07 June 2003 17:46, Henrik Steffen wrote:
(in answer to my queries):
> > What exactly does the function SQL() do? Is it possible that the script
> > could receive input along these lines?
> > SQL("UPDATE table SET manio='071002'; WHERE kundennummer='071002883';")
> sub SQL {
> my $command=shift;
> ...
> $sth=$db->prepare($command);
> $sth->execute();
> ...
> }
> > Any idea what version the server is running?
> running latest postgresql 7.3.3
Well, SQL() looks fine to me [*]; I would look at the path the query takes
from the web interface to the backend and whether the possibility of human
error (e.g. a semicolon in the wrong place not being detected) can be
definitively ruled out before looking for bugs in the server.
[*] but you might want to consider using placeholders and bind values.
Ian Barwick
barwick@gmx.net
hi, I tried both: $command="UPDATE table SET miano='12345'; commit; ' WHERE ... ;"; and $command="UPDATE table SET miano='12345'; -- ' WHERE ... ;"; neither worked. it just gives an error and dies. no update done. you are right: I should do more input-checking. however, I would like to first see, how exactly this attack was done... otherwise I won't be able to defend it. I looked at man DBI, but couldn't find any restriction for $dbh->prepare() .... I am not sure, if it's possible to pass multiple statements to the prepare-method. -- 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 -------------------------------------------------------- ----- Original Message ----- From: "Jan Wieck" <JanWieck@Yahoo.com> To: "Henrik Steffen" <steffen@city-map.de> Cc: "pgsql" <pgsql-general@postgresql.org> Sent: Saturday, June 07, 2003 5:34 PM Subject: Re: [GENERAL] update phenomenom > Henrik Steffen wrote: > > yes, input is coming from a web form. > > > > my SQL() function uses DBD::Pg in Perl > > > > and it does the following: > > > > sub SQL { > > my $command=shift; > > ... > > $sth=$db->prepare($command); > > $sth->execute(); > > ... > > } > > > > > > Now I tried the following: > > > > $command="UPDATE table SET miano='12345';' WHERE kundennummer='12345';"; > > > > note the inner ; ! so I am trying an SQL injection attack > > > > But this just delivers an Error from DBD::Pg. And no update at all is done. > > > > Autocommit off? Try this injection attack: > > $command="UPDATE table SET miano='12345'; commit; ' WHERE ... ;"; > > It should lead to an error message as well, but this time doing the update. > > > > > However, I believe it still has got to be a kind of attack, though the user > > certainly didn't know this... so, probably something that was entered > > unconsciously into the web-form. > > > > It was certainly no psql user who did this, because I can trace it back > > to a certain perl-script 100% sure. furthermore, I am the only one who > > actually has got psql-access....*lol* > > > > I looked at the perl-script more detailed now: > > > > Not only the field 'miano' should be updated but also 6 other fields. > > > > Four of these columns have been updated in all 450.000 rows, two others > > have NOT been updated. This leads me to the conclusion, that > > there has probably been a '; entered into form-fields four, so the > > where-clause was not executed - this would explain, why the other > > 2 columns were not update at all. > > > > I wonder, if there might have been a kind of comment-character after > > the ';. Because trying it manually, I keep getting an error. But > > is there a way to send a ';# to comment out the rest of the line? > > That would be '; -- > > > > > Or a ';\n or ';\0 or any special character? > > > > Very strange, very strange. > > > > BTW: I remember that I have had the same error once before, a few > > months ago. What strikes me, is , that the same function is triggered > > 50 times every day, and only every 5th month this strange error occurs. > > > > This led me to the thought, that maybe there could be some database > > corruption or something.... but it looks very much like an attack, don't > > you believe? > > Absolutely. This possiblity of SQL injection is one of the biggest > security holes. Some programmers do check input but fail to realize that > they do it on the wrong system, on the client side. So even if you have > JavaScript of whatever checking the content of input fields, someone can > handcraft a POST HTTP request. The best way to avoid this is to put > EVERY input from forms into properly quoted string literals "in the > middleware" and cast them from there to their correct data type. > > > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # >
> sub SQL {
> my $command=shift;
> ...
> $sth=$db->prepare($command);
> $sth->execute();
> ...
> }
running latest postgresql 7.3.3
--
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
--------------------------------------------------------
----- Original Message -----
From: "Ian Barwick" <barwick@gmx.net>
To: "Henrik Steffen" <steffen@city-map.de>; "pgsql"
<pgsql-general@postgresql.org>
Sent: Friday, June 06, 2003 9:03 PM
Subject: Re: [GENERAL] update phenomenom
> On Friday 06 June 2003 10:58, Henrik Steffen wrote:
> > Hello all,
> (...)
> > 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.
>
> What exactly does the function SQL() do? Is it possible that the script
could
> receive input along these lines?
> SQL("UPDATE table SET manio='071002'; WHERE kundennummer='071002883';")
>
> > Could there be any postgresql-server-side explanation for this
phenomenom ?
> > Perhaps
> > anything about corrupted indexes, or anything?
>
> Any idea what version the server is running?
>
>
> Schöne Grüße
>
> Ian Barwick
> barwick@gmx.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Henrik Steffen wrote:
> yes, input is coming from a web form.
>
> my SQL() function uses DBD::Pg in Perl
>
> and it does the following:
>
> sub SQL {
> my $command=shift;
> ...
> $sth=$db->prepare($command);
> $sth->execute();
> ...
> }
>
>
> Now I tried the following:
>
> $command="UPDATE table SET miano='12345';' WHERE kundennummer='12345';";
>
> note the inner ; ! so I am trying an SQL injection attack
>
> But this just delivers an Error from DBD::Pg. And no update at all is done.
>
Autocommit off? Try this injection attack:
$command="UPDATE table SET miano='12345'; commit; ' WHERE ... ;";
It should lead to an error message as well, but this time doing the update.
>
> However, I believe it still has got to be a kind of attack, though the user
> certainly didn't know this... so, probably something that was entered
> unconsciously into the web-form.
>
> It was certainly no psql user who did this, because I can trace it back
> to a certain perl-script 100% sure. furthermore, I am the only one who
> actually has got psql-access....*lol*
>
> I looked at the perl-script more detailed now:
>
> Not only the field 'miano' should be updated but also 6 other fields.
>
> Four of these columns have been updated in all 450.000 rows, two others
> have NOT been updated. This leads me to the conclusion, that
> there has probably been a '; entered into form-fields four, so the
> where-clause was not executed - this would explain, why the other
> 2 columns were not update at all.
>
> I wonder, if there might have been a kind of comment-character after
> the ';. Because trying it manually, I keep getting an error. But
> is there a way to send a ';# to comment out the rest of the line?
That would be '; --
>
> Or a ';\n or ';\0 or any special character?
>
> Very strange, very strange.
>
> BTW: I remember that I have had the same error once before, a few
> months ago. What strikes me, is , that the same function is triggered
> 50 times every day, and only every 5th month this strange error occurs.
>
> This led me to the thought, that maybe there could be some database
> corruption or something.... but it looks very much like an attack, don't
> you believe?
Absolutely. This possiblity of SQL injection is one of the biggest
security holes. Some programmers do check input but fail to realize that
they do it on the wrong system, on the client side. So even if you have
JavaScript of whatever checking the content of input fields, someone can
handcraft a POST HTTP request. The best way to avoid this is to put
EVERY input from forms into properly quoted string literals "in the
middleware" and cast them from there to their correct data type.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
yes, input is coming from a web form.
my SQL() function uses DBD::Pg in Perl
and it does the following:
sub SQL {
my $command=shift;
...
$sth=$db->prepare($command);
$sth->execute();
...
}
Now I tried the following:
$command="UPDATE table SET miano='12345';' WHERE kundennummer='12345';";
note the inner ; ! so I am trying an SQL injection attack
But this just delivers an Error from DBD::Pg. And no update at all is done.
However, I believe it still has got to be a kind of attack, though the user
certainly didn't know this... so, probably something that was entered
unconsciously into the web-form.
It was certainly no psql user who did this, because I can trace it back
to a certain perl-script 100% sure. furthermore, I am the only one who
actually has got psql-access....*lol*
I looked at the perl-script more detailed now:
Not only the field 'miano' should be updated but also 6 other fields.
Four of these columns have been updated in all 450.000 rows, two others
have NOT been updated. This leads me to the conclusion, that
there has probably been a '; entered into form-fields four, so the
where-clause was not executed - this would explain, why the other
2 columns were not update at all.
I wonder, if there might have been a kind of comment-character after
the ';. Because trying it manually, I keep getting an error. But
is there a way to send a ';# to comment out the rest of the line?
Or a ';\n or ';\0 or any special character?
Very strange, very strange.
BTW: I remember that I have had the same error once before, a few
months ago. What strikes me, is , that the same function is triggered
50 times every day, and only every 5th month this strange error occurs.
This led me to the thought, that maybe there could be some database
corruption or something.... but it looks very much like an attack, don't
you believe?
Anyone who knows more about DBD::Pg and how it's possible to enter
two commands within one statement?
--
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
--------------------------------------------------------
----- Original Message -----
From: "Jan Wieck" <JanWieck@Yahoo.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Friday, June 06, 2003 9:30 PM
Subject: Re: [GENERAL] update phenomenom
> 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.
> >
> >
> > 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';");
>
> You expect that $daten contains precisely
>
> miano='071002'
>
> I guess the 071002 is coming from an input field in a form, no? What if
> someone managed to get
>
> miano='071002';'
>
> into $daten by entering 071002'; into the field?
>
>
> Jan
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck@Yahoo.com #
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
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
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
>>>>> "Henrik" == Henrik Steffen <steffen@city-map.de> writes:
Henrik> yes, input is coming from a web form.
Henrik> my SQL() function uses DBD::Pg in Perl
Henrik> and it does the following:
Henrik> sub SQL {
Henrik> my $command=shift;
Henrik> ...
Henrik> $sth=$db->prepare($command);
Henrik> $sth->execute();
Henrik> ...
Henrik> }
Which is not the way to do it if there are any values. You should
be using placeholders, which properly escape the data so the calamity
and security whole you described would never have happened.
my $sth = $dbh->prepare("UPDATE atable SET col1 = ? WHERE col2 = ?");
$dbh->execute($col1_value, $col2_value);
That's the Right Way.
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!