Thread: Reg: SQL Query for Postgres 8.4.3

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 7:46 AM, Srinivas Naik <naik.srinu@gmail.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.srinu@gmail.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@xs4all.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@sss.pgh.pa.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
stringsand 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@sss.pgh.pa.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
treatedonly -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
errorfor negative length, but   that takes a pg_proc change which is impractical in the back branches;   and in any
casesomebody 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@gmail.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.Grittner@wicourts.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@gmail.com> writes:
> On Tue, May 4, 2010 at 10:29 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.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@gmail.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.

Srinivas Naik <naik.srinu@gmail.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: <blockquote cite="mid:4BE0C6AE.90000@catalyst.net.nz" type="cite"><br />
Pleaselog into postgres do:<br /><br /> SELECT version();<br /><br /> (and Robert suggested) <br /></blockquote><br />
Shouldread *as* Robert suggested - sorry.<br /><br /> Also you could do this from the os:<br /><br /> $ aptitude show
postgresql-8.3<span><b><br/><br /></b></span>which will display more detail for the version.<br /><br /> Cheers<br
/><br/> Mark<br /><span><b><br /><br /><br /></b></span> 

Hi Mark,<br /><br /> I took the output of the Postgresql. Please find the output:<br /><br />Package: postgresql-8.3<br
/>State:installed<br />Automatically installed: no<br />Version: 8.3.9-0ubuntu8.10<br />Priority: optional<br
/>Section:misc<br /> Maintainer: Martin Pitt <<a
href="mailto:martin.pitt@ubuntu.com">martin.pitt@ubuntu.com</a>><br/>Uncompressed Size: 14.2M<br />Depends: libc6
(>=2.4), libcomerr2 (>= 1.01), libkrb53 (>= 1.6.dfsg.2),<br />         libldap-2.4-2 (>= 2.4.7), libpam0g
(>=0.99.7.1), libpq5 (>=<br />          8.3~beta1), libssl0.9.8 (>= 0.9.8f-5), libxml2 (>= 2.6.27),<br
/>        postgresql-client-8.3, postgresql-common (>= 79), tzdata, ssl-cert,<br />         locales<br />Suggests:
oidentd| ident-server<br />Conflicts: postgresql (< 7.5)<br /> Description: object-relational SQL database, version
8.3server<br /> PostgreSQL is a fully featured object-relational database management system.<br /> It supports a large
partof the SQL standard and is designed to be extensible<br />  by users in many aspects.  Some of the features are:
ACIDtransactions, foreign<br /> keys, views, sequences, subqueries, triggers, user-defined types and functions,<br
/> outerjoins, multiversion concurrency control.  Graphical user interfaces and<br />  bindings for many programming
languagesare available as well. <br /> <br /> This package provides the database server for PostgreSQL 8.3. Servers for
other<br/> major release versions can be installed simultaneously and are coordinated by<br />  the postgresql-common
package.A package providing ident-server is needed if<br /> you want to authenticate remote connections with identd.<br
/><br/><br />Regards,<br />Srinivas Naik<br /><br /><div class="gmail_quote">On Wed, May 5, 2010 at 1:21 AM, Mark
Kirkwood<span dir="ltr"><<a href="mailto:mark.kirkwood@catalyst.net.nz">mark.kirkwood@catalyst.net.nz</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"><div bgcolor="#ffffff" text="#000000"><div class="im"> On 05/05/10 13:15, Mark Kirkwood wrote:
<blockquotetype="cite"><br /> Please log into postgres do:<br /><br /> SELECT version();<br /><br /> (and Robert
suggested)<br /></blockquote><br /></div> Should read *as* Robert suggested - sorry.<br /><br /> Also you could do this
fromthe os:<br /><br /> $ aptitude show postgresql-8.3<span><b><br /><br /></b></span>which will display more detail
forthe version.<br /><br /> Cheers<br /><br /> Mark<br /><span><b><br /><br /><br
/></b></span></div></blockquote></div><br/> 

On 05/05/10 22:13, Srinivas Naik wrote: <blockquote
cite="mid:r2r2e482c091005050313lb4f68a2ajade1eebc0e7a39b1@mail.gmail.com"type="cite"> Hi Mark,<br /><br />  I took the
outputof the Postgresql. Please find the output:<br /><br /> Package: postgresql-8.3<br /> State: installed<br />
Automaticallyinstalled: no<br /> Version: 8.3.9-0ubuntu8.10<br /></blockquote><br /><br /> 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...).<br/><br /> regards<br /><br /> Mark<br /><br /><br /> 

On 06/05/10 09:48, Mark Kirkwood wrote: <blockquote cite="mid:4BE1E7A2.6060309@catalyst.net.nz"
type="cite"></blockquote><br/><br /> Ok - your bug is fixed in 8.3.10. This should make its way to your Ubuntu apt
repositorysoon (provided 8.10 is still getting updates that is...).<br /><br /><br /><br /> Unfortunately it looks like
youmay not get this version - see: <br /><br /><a class="moz-txt-link-freetext"
href="http://ubuntuguide.org/wiki/Ubuntu:Intrepid">http://ubuntuguide.org/wiki/Ubuntu:Intrepid</a><br/><br /> i.e no
longersupported. I would recommend planning an upgrade to a supported version (10.04 is an LTS release - i.e much
longerperiod of support).<br /><br /> Cheers<br /><br /> Mark<br />