Feedback-ID: i64c147e9:Fastmail
User-Agent: Cyrus-JMAP/3.7.0-alpha0-811-gb808317eab-fm-20220801.001-gb808317e
Mime-Version: 1.0
Message-Id: <a17afe76-bba5-44df-bb32-830e7947b4ce@beta.fastmail.com>
In-Reply-To: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
References: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
Date: Tue, 09 Aug 2022 22:48:56 +0200
From: "Svante Richter" <pgsql-bugs@richter.id>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY
 FROM CSV
Content-Type: multipart/alternative;
 boundary=6d34f7e8cc0f4d3387d61246ae275a52
Archived-At: 
 <https://www.postgresql.org/message-id/a17afe76-bba5-44df-bb32-830e7947b4ce%40beta.fastmail.com>
Precedence: bulk

--6d34f7e8cc0f4d3387d61246ae275a52
Content-Type: text/plain

A gentle bump for this, hopefully that's alright!

On Wed, Jun 15, 2022, at 2:16 PM, Svante Richter wrote:
> Hello!
> 
> The documentation for COPY says "To avoid any misinterpretation, a `\.` data value appearing as a lone entry on a line is automatically quoted on output, and on input, if quoted, is not interpreted as the end-of-data marker".
> 
> The input part only seems to work when using the COPY FROM CSV command, not \COPY FROM CSV. This is mentioned in a previous message here https://www.postgresql.org/message-id/a89f47e1-f716-4ae3-b882-cab5032a5d66%40manitou-mail.org but not documented.
> 
> This means that COPY TO CSV produces data that \COPY FROM CSV cannot read, which I'm assuming should be fixed (or at the very least documented as a serious limitation of \COPY FROM CSV). I found this out by not being able to load a backup of a table that I had exported via COPY TO CSV.
> 
> As the above message also mentioned this can be a security risk if using \COPY FROM STDIN CSV with untrusted data (https://www.postgresql.org/message-id/20190128214448.GH26761%40momjian.us says "I think the question is how many people are using CSV/STDIN for insecure data loads?") but I would absolutely expect data produced with COPY TO CSV to be safe to pipe to a \COPY FROM CSV, but this bug makes that unsafe unless I also explicitly set ON_ERROR_STOP=1.
> 
> SQL to reproduce:
> 
> CREATE TABLE testtable (a TEXT);
> INSERT INTO testtable VALUES ('
> \.
> ');
> COPY testtable TO '/run/postgresql/test.csv' CSV;
> COPY testtable FROM '/run/postgresql/test.csv' CSV; -- This one works
> \COPY testtable FROM '/run/postgresql/test.csv' CSV; -- This one does not work
> 
> 
> Error message:
> 
> ERROR:  unterminated CSV quoted field
> CONTEXT:  COPY testtable, line 1: ""
> "
> 
> Versions tested:
> 
> psql (PostgreSQL) 14.3 (under arch linux)
> psql (PostgreSQL) 13.7 (Ubuntu 13.7-0ubuntu0.21.10.1)
> 

--6d34f7e8cc0f4d3387d61246ae275a52
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">
p.MsoNormal,p.MsoNoSpacing{margin:0}</style></head><body><div>A gentle b=
ump for this, hopefully that's alright!<br></div><div><br></div><div>On =
Wed, Jun 15, 2022, at 2:16 PM, Svante Richter wrote:<br></div><blockquot=
e type=3D"cite" id=3D"qt" style=3D""><div>Hello!<br></div><div><br></div=
><div>The documentation for COPY says "To avoid any misinterpretation, a=
 <code class=3D"qt-literal">\.</code> data=20
value appearing as a lone entry on a line is automatically quoted on=20
output, and on input, if quoted, is not interpreted as the end-of-data=20
marker".<br></div><div><br></div><div>The input part only seems to work =
when using the COPY FROM&nbsp;CSV command, not \COPY FROM CSV. This is m=
entioned in a previous message here <a href=3D"https://www.postgresql.or=
g/message-id/a89f47e1-f716-4ae3-b882-cab5032a5d66%40manitou-mail.org">ht=
tps://www.postgresql.org/message-id/a89f47e1-f716-4ae3-b882-cab5032a5d66=
%40manitou-mail.org</a> but not documented.<br></div><div><br></div><div=
>This means that COPY TO&nbsp;CSV produces data that \COPY FROM&nbsp;CSV=
 cannot read, which I'm assuming should be fixed (or at the very least d=
ocumented as a serious limitation of \COPY FROM CSV). I found this out b=
y not being able to load a backup of a table that I had exported via COP=
Y TO CSV.<br></div><div><br></div><div>As the above message also mention=
ed this can be a security risk if using \COPY FROM STDIN&nbsp;CSV with u=
ntrusted data (<a href=3D"https://www.postgresql.org/message-id/20190128=
214448.GH26761%40momjian.us">https://www.postgresql.org/message-id/20190=
128214448.GH26761%40momjian.us</a> says "I think the question is how man=
y people are using CSV/STDIN for insecure data loads?") but I would abso=
lutely expect data produced with COPY TO&nbsp;CSV to be safe to pipe to =
a \COPY FROM CSV, but this bug makes that unsafe unless I also explicitl=
y set <span style=3D"color:rgb(212, 212, 212);">ON_ERROR_STOP=3D1.</span=
><br></div><div><br></div><div>SQL to reproduce:<br></div><div><br></div=
><div>CREATE TABLE testtable (a TEXT);<br></div><div>INSERT INTO testtab=
le VALUES ('<br></div><div>\.<br></div><div>');<br></div><div>COPY testt=
able TO '/run/postgresql/test.csv' CSV;<br></div><div>COPY testtable FRO=
M '/run/postgresql/test.csv' CSV; -- This one works<br></div><div>\COPY =
testtable FROM '/run/postgresql/test.csv' CSV; -- This one does not work=
<br></div><div><br></div><div><br></div><div>Error message:<br></div><di=
v><br></div><div>ERROR:&nbsp; unterminated CSV quoted field<br></div><di=
v>CONTEXT:&nbsp; COPY testtable, line 1: ""<br></div><div>"<br></div><di=
v><br></div><div>Versions tested:<br></div><div><br></div><div>psql (Pos=
tgreSQL) 14.3 (under arch linux)<br></div><div>psql (PostgreSQL) 13.7 (U=
buntu 13.7-0ubuntu0.21.10.1)<br></div><div><br></div></blockquote><div><=
br></div></body></html>
--6d34f7e8cc0f4d3387d61246ae275a52--