Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group