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

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 #

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

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:

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 ?

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

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 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';")

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

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 #

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" == 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/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

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