Escaping of special characters in ARRAYs is broken

From: Gerhard Häring <haering_linux(at)gmx(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Escaping of special characters in ARRAYs is broken
Date: 2002-04-21 09:38:15
Message-ID: 20020421093815.GA8239@lilith.my-fqdn.de
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Gerhard Häring
Your email address : gerhard(at)bigfoot(dot)de

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium

Operating System (example: Linux 2.0.26 ELF) : FreeBSD 4.5-STABLE

PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.2.1

Compiler used (example: gcc 2.95.2) : gcc 2.95.3

Please enter a FULL description of your problem:
------------------------------------------------

Escaping of special characters in ARRAYs is broken.

I'm not sure if the problem occurs when inserting these special characters or
when fetching them from the database, but I realized inconsistent bahaviour.
You can see the inconsistency yourself when you try to insert a special
character like \005 into a varchar array. IMO This should work with inserting
'{"\\005"}', but this just inserts the three letters 005.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Use this dump:

----- SNIP -----
drop table arraytable;

CREATE TABLE "arraytable" (
"v" character varying[],
"i" integer[],
"m" money[],
"f" double precision[]
);

COPY "arraytable" FROM stdin;
{@,"\\\\005"} \N \N \N
\.
----- SNIP -----

Then look at what happens - this is a typescript from me:

----- SNIP -----
Script started on Sun Apr 21 10:31:53 2002
bash-2.05$ psql < escape_bug_testcase.sql
DROP
CREATE
bash-2.05$ psql
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

gerhard=# select v from arraytable;
v
-------------
{@,"\\005"}
(1 row)

gerhard=# select v[1] from arraytable where v[1] = '\100';
v
---
@
(1 row)

gerhard=# select v[2] from arraytable where v[2] = '\005';
v
---
(0 rows)

gerhard=# select v[2] from arraytable where v[2] = '\\005';
v
------
\005
(1 row)

gerhard=# \q
bash-2.05$ exit

Script done on Sun Apr 21 10:32:48 2002
----- SNIP -----

The finding the second array entry with '\005' does not work, but it does work
for the first entry with '\100'. Thi shows that special chars are not escaped
properly. It may also be that the syntax of the dump is wrong, but just try to
insert a special character like \005 into PostgreSQL and get it back again with
a SELECT. You'll see that this will not work consistently.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

--
mail: gerhard <at> bigfoot <dot> de registered Linux user #64239
web: http://www.cs.fhm.edu/~ifw00065/ OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9 3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2002-04-21 15:12:42 Re: Escaping of special characters in ARRAYs is broken
Previous Message Gerhard Häring 2002-04-21 08:53:13 Escaping of special characters in ARRAYs is broken