Feedback-ID: i64c147e9:Fastmail
User-Agent: Cyrus-JMAP/3.7.0-alpha0-712-gb9e94258b0-fm-20220610.001-gb9e94258
Mime-Version: 1.0
Message-Id: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
Date: Wed, 15 Jun 2022 14:16:14 +0200
From: "Svante Richter" <pgsql-bugs@richter.id>
To: pgsql-bugs@lists.postgresql.org
Subject: COPY TO CSV produces data that is incompatible/unsafe for \COPY FROM
 CSV
Content-Type: multipart/alternative;
 boundary=880944bc5c2b44158326a735838ce2cf
Archived-At: 
 <https://www.postgresql.org/message-id/bfcd57e4-8f23-4c3e-a5db-2571d09208e2%40beta.fastmail.com>
Precedence: bulk

--880944bc5c2b44158326a735838ce2cf
Content-Type: text/plain

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)

--880944bc5c2b44158326a735838ce2cf
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>Hello!<br></div=
><div><br></div><div>The documentation for COPY says "To avoid any misin=
terpretation, a <code class=3D"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.</div><div><br></div><div>Thi=
s means that COPY TO&nbsp;CSV produces data that \COPY FROM&nbsp;CSV can=
not read, which I'm assuming should be fixed (or at the very least docum=
ented as a serious limitation of \COPY FROM CSV). I found this out by no=
t being able to load a backup of a table that I had exported via COPY TO=
 CSV.</div><div><br></div><div>As the above message also mentioned this =
can be a security risk if using \COPY FROM STDIN&nbsp;CSV with untrusted=
 data (<a href=3D"https://www.postgresql.org/message-id/20190128214448.G=
H26761%40momjian.us">https://www.postgresql.org/message-id/2019012821444=
8.GH26761%40momjian.us</a> says "I think the question is how many people=
 are using CSV/STDIN for insecure data loads?") but I would absolutely e=
xpect 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 explicitly set <s=
pan style=3D"color:rgb(212, 212, 212);">ON_ERROR_STOP=3D1.</span><br></d=
iv><div><br></div><div>SQL to reproduce:<br></div><div><br></div><div>CR=
EATE TABLE testtable (a TEXT);<br></div><div>INSERT INTO testtable VALUE=
S ('<br></div><div>\.<br></div><div>');<br></div><div>COPY testtable TO =
'/run/postgresql/test.csv' CSV;<br></div><div>COPY testtable FROM '/run/=
postgresql/test.csv' CSV; -- This one works<br></div><div>\COPY testtabl=
e FROM '/run/postgresql/test.csv' CSV; -- This one does not work<br></di=
v><div><br></div><div><br></div><div>Error message:<br></div><div><br></=
div><div>ERROR:&nbsp; unterminated CSV quoted field<br></div><div>CONTEX=
T:&nbsp; COPY testtable, line 1: ""<br></div><div>"<br></div><div><br></=
div><div>Versions tested:<br></div><div><br></div><div>psql (PostgreSQL)=
 14.3 (under arch linux)<br></div><div>psql (PostgreSQL) 13.7 (Ubuntu 13=
.7-0ubuntu0.21.10.1)<br></div><div><br></div></body></html>
--880944bc5c2b44158326a735838ce2cf--