How can I prevent SQL injections during CSV uploads?
No action is required; COPY
never interprets the values as SQL syntax. Malformed CSV will produce an error due to bad quoting / incorrect column count. If you're sending your own data line-by-line you should probably exclude a line containing a single \.
followed by a newline, but otherwise it's rather safe.
PostgreSQL doesn't sanitize the data in any way, it just handles it safely. So if you accept a string ');DROP TABLE customer;--
in your CSV it's quite safe in COPY
. However, if your application reads that out of the database, assumes that "because it came from the database not the user it's safe," and interpolates it into an SQL string you're still just as stuffed.
Similarly, incorrect use of PL/PgSQL functions where EXECUTE
is used with unsafe string concatenation will create problems. You must use of format
and the %I
or %L
specifiers, use quote_literal
/ quote_ident
, or (for literals) use EXECUTE ... USING
.
This is not just true of COPY
, it's the same if you do an INSERT
of the manipulated data then use it unsafely after reading it back from the DB.