Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY FROM CSV

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)
>