Re: Fwd: Bug#249036: postgresql: zero bytes cannot be entered

Hi PostgreSQL hackers!

We recently got the Debian bug report below. Does anybody have an idea about that?

Thanks and have a nice day!

Martin

----- Forwarded message from Peter Schueller <peter(dot)schueller(at)rise(dot)tuwien(dot)ac(dot)at> -----

Subject: Bug#249036: postgresql: zero bytes cannot be entered in string
literal, not even with \000 notation
Reply-To: Peter Schueller <peter(dot)schueller(at)rise(dot)tuwien(dot)ac(dot)at>,
249036(at)bugs(dot)debian(dot)org
From: Peter Schueller <peter(dot)schueller(at)rise(dot)tuwien(dot)ac(dot)at>
To: submit(at)bugs(dot)debian(dot)org
Date: Fri, 14 May 2004 17:26:33 +0200
X-Spam-Status: No, hits=0.0 required=4.0 tests=none autolearn=no version=2.61

Package: postgresql
Severity: normal

if i issue the following query:
$ select decode(encode('\001\000\001'::bytea,'hex'),'hex');
the result will always be '\001' and not '\001\000\001' as assumable.

i think the string is handled zero-delimited and not with its given
size,
and i think in this case this is not the wanted behaviour because to
enter any bytea with zero bytes one will have to use
decode('my string in hex', 'hex'), only then the zero bytes will be
stored
correctly.

versions:
ii postgresql 7.4.2-4 Object-relational SQL database, ...
ii postgresql-cli 7.4.2-4 Front-end programs for PostgreSQL

thx,
Peter Schueller

-- System Information:
Debian Release: testing/unstable
APT prefers unstable
APT policy: (500, 'unstable')
Architecture: i386 (i686)
Kernel: Linux 2.6.6
Locale: LANG=C, LC_CTYPE=C

----- End forwarded message -----

--
Martin Pitt Debian GNU/Linux Developer
martin(at)piware(dot)de mpitt(at)debian(dot)org
http://www.piware.de http://www.debian.org

Martin Pitt wrote:
> Hi PostgreSQL hackers!
>
> We recently got the Debian bug report below. Does anybody have an idea about that?
>
> Thanks and have a nice day!
>
> if i issue the following query:
> $ select decode(encode('\001\000\001'::bytea,'hex'),'hex');
> the result will always be '\001' and not '\001\000\001' as assumable.
>
> i think the string is handled zero-delimited and not with its given
> size,
> and i think in this case this is not the wanted behaviour because to
> enter any bytea with zero bytes one will have to use
> decode('my string in hex', 'hex'), only then the zero bytes will be
> stored
> correctly.

The issue is that bytea needs double-backslashes because single
backslashes are processed by the parser into strings:

test=> select decode(encode('\001\000\001'::bytea,'hex'),'hex');
decode
--------
\001
(1 row)

test=> select decode(encode('\\001\\000\\001'::bytea,'hex'),'hex');
decode
--------------
\001\000\001
(1 row)

This is all in the documentation.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hi!

On 2004-05-17 8:37 -0400, Bruce Momjian wrote:
> The issue is that bytea needs double-backslashes because single
> backslashes are processed by the parser into strings:

Indeed. Thank you for this!

Martin

--
Martin Pitt Debian GNU/Linux Developer
martin(at)piware(dot)de mpitt(at)debian(dot)org
http://www.piware.de http://www.debian.org