Subject: [BUG] psql: Make \copy from 'text' and 'csv' formats fail on NUL bytes

From: "Joel Jacobson" <[email-masked]>
Date: Sun, 10 Nov 2024 22:26:20 +0100

Hi hackers,

When using \copy from in psql to import files containing NUL bytes (\0) in
'text' or 'csv' format, the NUL bytes were not detected and did not result in
an error, leading to silent data corruption.

This behavior is inconsistent with server-side COPY FROM, which reports an error
upon encountering NUL bytes in the 'text' and 'csv' formats.

Fix by adjusting handleCopyIn() to use the binary code path also when the copy
source is a file (i.e., copystream != pset.cur_cmd_source), even in textual
copies. This ensures that NUL bytes are detected and reported in the same way
as server-side COPY.

Example:

% printf 'test_col\nfoo\n\x00\nbar\nbaz\x00aar\nbizarre\n' > nul_bytes.data
% cat -v nul_bytes.data
test_col
foo
^@
bar
baz^@aar
bizarre

% psql
create table nul_bytes_test (test_col text);

--
-- HEAD
--
\copy nul_bytes_test (test_col) from 'nul_bytes.data' (format csv, header match);
COPY 3
select * from nul_bytes_test;
  test_col
------------
 foo
 bar
 bazbizarre
(3 rows)

\copy nul_bytes_test (test_col) from 'nul_bytes.data' (format text, header match);
select * from nul_bytes_test;
  test_col
------------
 foo
 bar
 bazbizarre
(3 rows)

--
-- 0001-psql-Make-copy-from-text-and-csv-formats-fail-on-NUL.patch
--
\copy nul_bytes_test (test_col) from 'nul_bytes.data' (format csv, header match);
ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  COPY nul_bytes_test, line 3
\copy nul_bytes_test (test_col) from 'nul_bytes.data' (format text, header match);
ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  COPY nul_bytes_test, line 3

/Joel