Re: Should CSV parsing be stricter about mid-field quotes? - Mailing list pgsql-hackers

On Sun, May 14, 2023, at 16:58, Andrew Dunstan wrote:

> And if people do follow the method you describe then their input with

> unescaped quotes will be rejected 999 times out of 1000. It's only cases where

> the field happens to have an even number of embedded quotes, like Joel's

> somewhat contrived example, that the input will be accepted.

I concur with Andrew that my previous example might've been somewhat

contrived, as it deliberately included two instances of the term "inches".

It's a matter of time before someone submits a review featuring an odd number of

"inches", leading to an error.

Having done some additional digging, I stumbled upon three instances [1] [2] [3]

where users have misidentified their TSV/TEXT files as CSV. In these situations,

users have been shielded from failure due to an imbalance in quotes.

However, in cases where a field is utilized to store text in which the double

quotation mark is rarely used to denote inches, but instead, for quotation

and/or HTML attributes, it's quite feasible that a large amount of

user-generated text could contain balanced quotes. Even more concerning is the

potential for cases where the vast majority of inputs may not even contain

double quotation marks at all. This would effectively render the issue invisible,

even upon manual data inspection.

Here's a problem scenario that I believe is plausible:

1. The user wishes to import a .TXT file into PostgreSQL.

2. The user examines the .TXT file and observes column headers separated by a

delimiter like TAB or semicolon, with subsequent rows of data also separated by

the same delimiter.

3. The user is familiar with "CSV" (412M hits on Google) but not "TSV" (48M hits

on Google), leading to a false assumption that their file is in CSV format.

4. A Google search for "import csv into postgresql" leads the user to a tutorial

titled "Import CSV File Into PostgreSQL Table". An example found therein:

COPY persons(first_name, last_name, dob, email)

FROM 'C:\sampledb\persons.csv'

DELIMITER ','

CSV HEADER;

5. The user, now confident, believes they understand how to import their "CSV"

file.

6. In contrast to the "ERROR: unterminated CSV quoted field" examples below,

this user's .TXT file contains fields with balanced midfield quote-marks:

blog_posts.txt:

id message

1 This is a <b>bold</b> statement

7. The user copies the COPY command from the tutorial and modifies the file path

and delimiter accordingly. The user then concludes that the code is functioning

as expected and proceeds to deploy it.

8, Weeks later, users complain about broken links in their blog posts. Upon

inspection of the blog_posts table, the user identifies an issue:

SELECT * FROM blog_posts;

id | message

----+------------------------------------------------------------------------

1 | This is a <b>bold</b> statement

2 | Check <a href=http://example.com/?param1=Midfield quoting>this</a> out

(2 rows)

One of the users has used balanced quotes for the href attribute, which was

imported successfully but the quotes were stripped, contrary to the intention of

preserving them.

Content of blog_posts.txt:

id message

1 This is a <b>bold</b> statement

2 Check <a href="http://example.com/?param1=Midfield quoting">this</a> out

If we made midfield quoting a CSV error, those users who are currently mistaken

about their TSV/TEXT files being CSV while also having balanced quotes in their

data, would encounter an error rather than a silent failure, which I believe

would be an enhancement.

/Joel

[1] https://www.postgresql.org/message-id/1upfg19cru2jigbm553fugj5k6iebtd4ps@4ax.com

[2] https://stackoverflow.com/questions/44108286/unterminated-csv-quoted-field-in-postgres

[3] https://dba.stackexchange.com/questions/306662/unterminated-csv-quoted-field-when-to-import-csv-data-file-into-postgresql