Re: Reg: SQL Query for Postgres 8.4.3

Hi,

Can any one tell me whats the effect of the below Query

SELECT substring(B'1111000000000001' from 5 for -2);
SELECT substring(B'1111000000000001' from 4 for -3);

its observed that there's an Error "invalid memory alloc request size
4244635647"

What will actually happen to the Postgresql database.

I am using Postgresql 8.4.3 on Ubuntu 8.04

Thanks in Advance
Regards,
Srinivas Naik

On 04/05/10 18:47, Srinivas Naik wrote:
> Hi,
> Can any one tell me whats the effect of the below Query
> SELECT substring(B'1111000000000001' from 5 for -2);
> SELECT substring(B'1111000000000001' from 4 for -3);
> its observed that there's an Error "invalid memory alloc request size
> 4244635647"
> What will actually happen to the Postgresql database.
> I am using Postgresql 8.4.3 on Ubuntu 8.04

I can reproduce this with 8.4.*2* on Ubuntu 10.04 32-bit (postgres built
from src) but *not* with 8.4.*3*. Can you double check it is 8.4.3 you
are using? and if so tell us whether you are using 32 or 64 bit Ubuntu!

Cheers

Mark

Hi Mark,

Please find the below details:

postgresql-8.3

and UBUNTU-8.10 with linux-image-2.6.27.18-standard_810_i386.deb

and its an 32bit Ubuntu.

On Tue, May 4, 2010 at 9:02 AM, Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz
> wrote:

> On 04/05/10 18:47, Srinivas Naik wrote:
>
> Hi,
>
> Can any one tell me whats the effect of the below Query
>
> SELECT substring(B'1111000000000001' from 5 for -2);
> SELECT substring(B'1111000000000001' from 4 for -3);
>
> its observed that there's an Error "invalid memory alloc request size
> 4244635647"
>
> What will actually happen to the Postgresql database.
>
> I am using Postgresql 8.4.3 on Ubuntu 8.04
>
>
>
>
> I can reproduce this with 8.4.*2* on Ubuntu 10.04 32-bit (postgres built
> from src) but *not* with 8.4.*3*. Can you double check it is 8.4.3 you are
> using? and if so tell us whether you are using 32 or 64 bit Ubuntu!
>
> Cheers
>
> Mark
>
>
>

On Tue, May 4, 2010 at 7:46 AM, Srinivas Naik <naik(dot)srinu(at)gmail(dot)com> wrote:
> Hi Mark,
>
>     Please find the below details:
>
> postgresql-8.3
>
> and UBUNTU-8.10 with linux-image-2.6.27.18-standard_810_i386.deb
>
> and its an 32bit Ubuntu.

Err, before you said 8.4.3. Now you're saying 8.3. Those are totally
different. Can we get the exact identifier of the package you have
installed, plus the output from

SELECT version();

Thanks,

...Robert

On Tue, May 4, 2010 15:40, Robert Haas wrote:
> On Tue, May 4, 2010 at 7:46 AM, Srinivas Naik <naik(dot)srinu(at)gmail(dot)com> wrote:
>> Hi Mark,
>>
>>     Please find the below details:
>>
>> postgresql-8.3
>>
>> and UBUNTU-8.10 with linux-image-2.6.27.18-standard_810_i386.deb
>>
>> and its an 32bit Ubuntu.
>
> Err, before you said 8.4.3. Now you're saying 8.3. Those are totally
> different. Can we get the exact identifier of the package you have
> installed, plus the output from
>
> SELECT version();
>
fwiw, results for all current postgres versions:

-- to be executed:
SELECT substring(B'1111000000000001' from 5 for -2); SELECT substring(B'1111000000000001' from 4
for -3);

-- postgres 9.0beta1
ERROR: negative substring length not allowed
ERROR: negative substring length not allowed
-- postgres 8.4.3
000000000001
1000000000001
-- postgres 8.3.10
000000000001
1000000000001
-- postgres 8.2.16
000000000001
1000000000001
-- postgres 8.1.20
000000000001
1000000000001
-- postgres 8.0.24
000000000001
1000000000001
-- postgres 7.4.28
000000000001
1000000000001

On Tue, May 4, 2010 at 10:08 AM, Erik Rijkers <er(at)xs4all(dot)nl> wrote:
> fwiw, results for all current postgres versions:
> [ only 9.0beta1 is different ]

It looks like the relevant commits are:

commit 822f2ac5a2ec7c6f10634f62a0b2dc6cc9929759
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Mon Jan 25 20:55:32 2010 +0000

Add get_bit/set_bit functions for bit strings, paralleling those for bytea,
and implement OVERLAY() for bit strings and bytea.

In passing also convert text OVERLAY() to a true built-in, instead of
relying on a SQL function.

Leonardo F, reviewed by Kevin Grittner

commit 75dea10196c31d98d98c0bafeeb576ae99c09b12
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Thu Jan 7 19:53:11 2010 +0000

Make bit/varbit substring() treat any negative length as meaning "all the re
of the string". The previous coding treated only -1 that way, and would
produce an invalid result value for other negative values.

We ought to fix it so that 2-parameter bit substring() is a different C
function and the 3-parameter form throws error for negative length, but
that takes a pg_proc change which is impractical in the back branches;
and in any case somebody might be relying on -1 working this way.
So just do this as a back-patchable fix.

I think the OP is probably running a version that doesn't include the
Jan 7 commit, which was effectively undone by the Jan 25 commit for
CVS HEAD. It looks like this was intentional based on spec behavior
of overlay(), but should we consider maintaining the historical
behavior instead?

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I think the OP is probably running a version that doesn't include
> the Jan 7 commit, which was effectively undone by the Jan 25
> commit for CVS HEAD.

It sure looks like it.

> It looks like this was intentional based on spec behavior
> of overlay(), but should we consider maintaining the historical
> behavior instead?

I know I read through the spec (several versions of it) related to
this issue when I reviewed the patch, and if memory serves the 9.0
behavior is what the spec requires. Obviously that's a behavior
change, so it can't be back-patched. I'm inclined to think the
previous behavior was pretty marginal, and there is certainly a
workaround -- omit the third parameter rather than specifying a
negative number:

SELECT substring(B'1111000000000001' from 5);
substring
--------------
000000000001
(1 row)

SELECT substring(B'1111000000000001' from 4);
substring
---------------
1000000000001
(1 row)

We have maintained nonstandard behavior in the past for
compatibility reasons, so it's a fair question; however, I'm
inclined toward the standard on this one.

-Kevin

On Tue, May 4, 2010 at 10:29 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> We have maintained nonstandard behavior in the past for
> compatibility reasons, so it's a fair question; however, I'm
> inclined toward the standard on this one.

In a case like this, it seems unlikely that someone would be counting
on a negative value to throw an error, so I tend to regard doing
something else as an extension of the standard rather than a deviation
from it. But I don't have strong feelings about it.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, May 4, 2010 at 10:29 AM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> We have maintained nonstandard behavior in the past for
>> compatibility reasons, so it's a fair question; however, I'm
>> inclined toward the standard on this one.

> In a case like this, it seems unlikely that someone would be counting
> on a negative value to throw an error, so I tend to regard doing
> something else as an extension of the standard rather than a deviation
> from it. But I don't have strong feelings about it.

The reason we changed it is that our other versions of substring()
already had the spec-required behavior of throwing error for negative
length. Only the bit/varbit implementation was out of step.

The OP did not state that this behavioral change broke his application,
anyway. I suspect the actual subtext is that he's poking into the
vulnerability report that was issued against the unpatched code.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> But I don't have strong feelings about it.

Nor do I. Perhaps this question should be floated on -general?

-Kevin

I am sorry for that, but I made two different installations and I was
messing up with various inputs.

Actually, the installed versions are below
*postgresql-8.3*
*Ubuntu 8.10 with 2.6.27 Kernel*
*and its an 32Bit O/S*

pgsql$ SELECT substring(B'1111000000000001' from 5 for -2);
ERROR:invalid memory alloc request size 4244635647

I just wanted to know how severe it is and how it can effect the database to
result Memory Corruption/DoS.

Please help me in making the point clear.

Thanks again.

On Tue, May 4, 2010 at 8:45 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov>wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> > But I don't have strong feelings about it.
>
> Nor do I. Perhaps this question should be floated on -general?
>
> -Kevin
>

Srinivas Naik <naik(dot)srinu(at)gmail(dot)com> wrote:

> Actually, the installed versions are below
> *postgresql-8.3*

> I just wanted to know how severe it is and how it can effect the
> database to result Memory Corruption/DoS.

Well, you're clearly *not* on 8.3.10, or you would not get the
error. Perhaps you should apply the latest bug fixes?

http://www.postgresql.org/support/versioning

http://www.postgresql.org/docs/8.3/static/release.html

As far as I know it isn't any more conducive to DoS attacks than,
say, your average syntax error; however, if you're trying to keep
that risk low, you should be keeping up with the minor releases
anyway.

-Kevin

On 05/05/10 06:24, Srinivas Naik wrote:
>
>
> I am sorry for that, but I made two different installations and I was
> messing up with various inputs.
>
> Actually, the installed versions are below
> *postgresql-8.3*
> *Ubuntu 8.10 with 2.6.27 Kernel*
> *and its an 32Bit O/S*
>
> pgsql$ SELECT substring(B'1111000000000001' from 5 for -2);
> ERROR:invalid memory alloc request size 4244635647
>
>

Please log into postgres do:

SELECT version();

(and Robert suggested) and show us the output - as we need to know the
3rd number e.g 8.3.x in the postgres version to help you any more.

regards

Mark

On 05/05/10 13:15, Mark Kirkwood wrote:
>
> Please log into postgres do:
>
> SELECT version();
>
> (and Robert suggested)

Should read *as* Robert suggested - sorry.

Also you could do this from the os:

$ aptitude show postgresql-8.3*

*which will display more detail for the version.

Cheers

Mark
*

*

Hi Mark,

I took the output of the Postgresql. Please find the output:

Package: postgresql-8.3
State: installed
Automatically installed: no
Version: 8.3.9-0ubuntu8.10
Priority: optional
Section: misc
Maintainer: Martin Pitt <martin(dot)pitt(at)ubuntu(dot)com>
Uncompressed Size: 14.2M
Depends: libc6 (>= 2.4), libcomerr2 (>= 1.01), libkrb53 (>= 1.6.dfsg.2),
libldap-2.4-2 (>= 2.4.7), libpam0g (>= 0.99.7.1), libpq5 (>=
8.3~beta1), libssl0.9.8 (>= 0.9.8f-5), libxml2 (>= 2.6.27),
postgresql-client-8.3, postgresql-common (>= 79), tzdata, ssl-cert,
locales
Suggests: oidentd | ident-server
Conflicts: postgresql (< 7.5)
Description: object-relational SQL database, version 8.3 server
PostgreSQL is a fully featured object-relational database management
system.
It supports a large part of the SQL standard and is designed to be
extensible
by users in many aspects. Some of the features are: ACID transactions,
foreign
keys, views, sequences, subqueries, triggers, user-defined types and
functions,
outer joins, multiversion concurrency control. Graphical user interfaces
and
bindings for many programming languages are available as well.

This package provides the database server for PostgreSQL 8.3. Servers for
other
major release versions can be installed simultaneously and are coordinated
by
the postgresql-common package. A package providing ident-server is needed
if
you want to authenticate remote connections with identd.

Regards,
Srinivas Naik

On Wed, May 5, 2010 at 1:21 AM, Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz
> wrote:

> On 05/05/10 13:15, Mark Kirkwood wrote:
>
>
> Please log into postgres do:
>
> SELECT version();
>
> (and Robert suggested)
>
>
> Should read *as* Robert suggested - sorry.
>
> Also you could do this from the os:
>
> $ aptitude show postgresql-8.3*
>
> *which will display more detail for the version.
>
> Cheers
>
> Mark
> *
>
>
> *
>

On 05/05/10 22:13, Srinivas Naik wrote:
> Hi Mark,
>
> I took the output of the Postgresql. Please find the output:
>
> Package: postgresql-8.3
> State: installed
> Automatically installed: no
> Version: 8.3.9-0ubuntu8.10

Ok - your bug is fixed in 8.3.10. This should make its way to your
Ubuntu apt repository soon (provided 8.10 is still getting updates that
is...).

regards

Mark

On 06/05/10 09:48, Mark Kirkwood wrote:
>
>
> Ok - your bug is fixed in 8.3.10. This should make its way to your
> Ubuntu apt repository soon (provided 8.10 is still getting updates
> that is...).
>
>

Unfortunately it looks like you may not get this version - see:

http://ubuntuguide.org/wiki/Ubuntu:Intrepid

i.e no longer supported. I would recommend planning an upgrade to a
supported version (10.04 is an LTS release - i.e much longer period of
support).

Cheers

Mark