Feedback-ID: ic6394509:Fastmail
MIME-Version: 1.0
Date: Sun, 10 Nov 2024 22:26:20 +0100
From: "Joel Jacobson" <joel@compiler.org>
To: pgsql-hackers@lists.postgresql.org
Message-Id: <5eabc9e4-509c-4f83-92c8-36d298cf930f@app.fastmail.com>
Subject: [BUG] psql: Make \copy from 'text' and 'csv' formats fail on NUL bytes
Content-Type: multipart/mixed;
 boundary=1b8e9d2340e44b4ab63e7b25b237d35d
Archived-At: <https://www.postgresql.org/message-id/5eabc9e4-509c-4f83-92c8-36d298cf930f%40app.fastmail.com>
Precedence: bulk

--1b8e9d2340e44b4ab63e7b25b237d35d
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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
--1b8e9d2340e44b4ab63e7b25b237d35d
Content-Disposition: attachment;
	filename*0="0001-psql-Make-copy-from-text-and-csv-formats-fail-on-NUL.pat";
	filename*1="ch"
Content-Type: application/octet-stream;
	name="=?UTF-8?Q?0001-psql-Make-copy-from-text-and-csv-formats-fail-on-NUL.patc?=
 =?UTF-8?Q?h?="
Content-Transfer-Encoding: base64

RnJvbSBiNjgyNGZhZjAzZDRkNGJkYjkwOWIzMzIzMGZhYjAwMTFjYmNmYzY5IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBKb2VsIEphY29ic29uIDxqb2VsQGNvbXBpbGVyLm9y
Zz4KRGF0ZTogU3VuLCAxMCBOb3YgMjAyNCAyMjoxODo0OCArMDEwMApTdWJqZWN0OiBbUEFU
Q0hdIHBzcWw6IE1ha2UgXGNvcHkgZnJvbSAndGV4dCcgYW5kICdjc3YnIGZvcm1hdHMgZmFp
bCBvbiBOVUwKIGJ5dGVzCgpXaGVuIHVzaW5nIFxjb3B5IGZyb20gaW4gcHNxbCB0byBpbXBv
cnQgZmlsZXMgY29udGFpbmluZyBOVUwgYnl0ZXMgKFwwKSBpbgondGV4dCcgb3IgJ2Nzdicg
Zm9ybWF0LCB0aGUgTlVMIGJ5dGVzIHdlcmUgbm90IGRldGVjdGVkIGFuZCBkaWQgbm90IHJl
c3VsdCBpbgphbiBlcnJvciwgbGVhZGluZyB0byBzaWxlbnQgZGF0YSBjb3JydXB0aW9uLgoK
VGhpcyBiZWhhdmlvciBpcyBpbmNvbnNpc3RlbnQgd2l0aCBzZXJ2ZXItc2lkZSBDT1BZIEZS
T00sIHdoaWNoIHJlcG9ydHMgYW4gZXJyb3IKdXBvbiBlbmNvdW50ZXJpbmcgTlVMIGJ5dGVz
IGluIHRoZSAndGV4dCcgYW5kICdjc3YnIGZvcm1hdHMuCgpGaXggYnkgYWRqdXN0aW5nIGhh
bmRsZUNvcHlJbigpIHRvIHVzZSB0aGUgYmluYXJ5IGNvZGUgcGF0aCBhbHNvIHdoZW4gdGhl
IGNvcHkKc291cmNlIGlzIGEgZmlsZSAoaS5lLiwgY29weXN0cmVhbSAhPSBwc2V0LmN1cl9j
bWRfc291cmNlKSwgZXZlbiBpbiB0ZXh0dWFsCmNvcGllcy4gVGhpcyBlbnN1cmVzIHRoYXQg
TlVMIGJ5dGVzIGFyZSBkZXRlY3RlZCBhbmQgcmVwb3J0ZWQgaW4gdGhlIHNhbWUgd2F5CmFz
IHNlcnZlci1zaWRlIENPUFkuCi0tLQogc3JjL2Jpbi9wc3FsL2NvcHkuYyB8IDIgKy0KIDEg
ZmlsZSBjaGFuZ2VkLCAxIGluc2VydGlvbigrKSwgMSBkZWxldGlvbigtKQoKZGlmZiAtLWdp
dCBhL3NyYy9iaW4vcHNxbC9jb3B5LmMgYi9zcmMvYmluL3BzcWwvY29weS5jCmluZGV4IGUw
MjBlNGQ2NjVkLi4zMDZmNTE2MWUxZCAxMDA2NDQKLS0tIGEvc3JjL2Jpbi9wc3FsL2NvcHku
YworKysgYi9zcmMvYmluL3BzcWwvY29weS5jCkBAIC01NDQsNyArNTQ0LDcgQEAgaGFuZGxl
Q29weUluKFBHY29ubiAqY29ubiwgRklMRSAqY29weXN0cmVhbSwgYm9vbCBpc2JpbmFyeSwg
UEdyZXN1bHQgKipyZXMpCiAKIAlPSyA9IHRydWU7CiAKLQlpZiAoaXNiaW5hcnkpCisJaWYg
KGlzYmluYXJ5IHx8IGNvcHlzdHJlYW0gIT0gcHNldC5jdXJfY21kX3NvdXJjZSkKIAl7CiAJ
CS8qIGludGVyYWN0aXZlIGlucHV0IHByb2JhYmx5IHNpbGx5LCBidXQgZ2l2ZSBvbmUgcHJv
bXB0IGFueXdheSAqLwogCQlpZiAoc2hvd3Byb21wdCkKLS0gCjIuNDUuMQoK

--1b8e9d2340e44b4ab63e7b25b237d35d--