PostgreSQL COPY SQL injection

Question

I am using PostgreSQL 11 COPY command to import large CSVs into the DB with Python, like the following:

COPY "ns"."table" ("col1", "col2") FROM STDIN WITH CSV HEADER DELIMITER AS ','

I didn't find any recent information if this operation is secure in terms of SQL injection attacks or should I manually go over the CSV and escape every value in the file (which is a very heavy operation).

Thanks!

Can you show how this would be used in the context of python? While it should not be inherently dangerous, there are surely going to be dangerous ways to do it. — jjanes
– jjanes, Commented Dec 16, 2020 at 17:57
@jjanes, curs.copy_expert(sql='COPY ...', file=file_obj) (psycopg2 cursor) — Meir Tseitlin
– Meir Tseitlin, Commented Dec 16, 2020 at 18:06
That should be safe. If someone can inject \n\\.\n, into file_obj they can get all data after that to be ignored. But they can't get arbitrary commands to run. — jjanes
– jjanes, Commented Dec 16, 2020 at 21:46

Laurenz Albe · Accepted Answer · 2020-12-16 17:31:33Z

7

There is no danger of SQL injection with this command.

If a user supplies bad data, then you end up with bad data in the table, or at worst you could get an error because the file is not correct CSV or because a constraint was violated.

But there is no way to subvert security to execute statements, because nothing entered by the user will become part of an SQL statement. With COPY, there is a clear distinction between SQL statement and data.

answered Dec 16, 2020 at 17:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adrian Klaver Over a year ago

Except for [CVE-2019-9193 ](rhaas.blogspot.com/2020/12/cve-2019-9193.html).

Laurenz Albe Over a year ago

@AdrianKlaver This is irrelevant, because here we have COPY ... FROM STDIN. And what you quote is not SQL injection, and it is not a security problem to boot.

Adrian Klaver Over a year ago

Yes but you did not specify COPY ... FROM STDIN, you made a blanket statement about COPY. Also it is security problem, it is just a documented one. Basically a superuser is a superuser, so be careful in how you assign them. And since program could be something that runs SQL it could be SQL injection and furthermore you are using SQL to mount an attack. I personally think the whole thing is over blown, but not providing a heads up is not good either.

Laurenz Albe Over a year ago

C'mon. I wrote "with this command", meaning the statement in the question. Besides, this has nothing to do with SQL injection. What comes from the user are the COPY data, not the COPY statement. Or do you see dynamic SQL anywhere in the question?

Collectives™ on Stack Overflow

PostgreSQL COPY SQL injection

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related