How can I prevent SQL injections during CSV uploads?
postgresql, ruby-on-rails, security
Solution
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.
Problem
I've just started learning about Rails security, and I'm wondering how I can avoid security issues while allowing users to upload CSV files into our database. We're using Postgres' "copy from stdin" functionality to upload the data from the CSV into a temp table, which is then used for upserts into another table. This is the basic code (thanks to this post): ``` conn = ActiveRecord::Base.connection_pool.checkout raw = conn.raw_connection raw.exec("COPY temp_table (col1, col2) FROM STDIN DELIMITER '|'") # read column values from the CSV line by line in the following format: # attributes = {column_1: 'column 1 data', column_2: 'column 2 data'} # line = "#{attributes.values.join('|')}\n" rc.put_copy_data line # wrap up copy process & insert into & update primary table ``` I am wondering what I can or should do to sanitize the column values. We're using Rails 3.2 and Postgres 9.2.