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
Date: Tue, 09 Aug 2022 22:48:56 +0200
Message-ID: <a17afe76-bba5-44df-bb32-830e7947b4ce@beta.fastmail.com> (raw)
In-Reply-To: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
References: <bfcd57e4-8f23-4c3e-a5db-2571d09208e2@beta.fastmail.com>
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)
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: pgsql-bugs@richter.id, pgsql-bugs@lists.postgresql.org
Subject: Re: COPY TO CSV produces data that is incompatible/unsafe for \COPY FROM CSV
In-Reply-To: <a17afe76-bba5-44df-bb32-830e7947b4ce@beta.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox