ruby on rails - How can I prevent SQL injections during CSV uploads? -

i've started learning rails security, , i'm wondering how can avoid security issues while allowing users upload csv files our database. we're using postgres' "copy stdin" functionality upload data csv temp table, used upserts table. basic code (thanks this post):

conn = activerecord::base.connection_pool.checkout raw  = conn.raw_connection raw.exec("copy temp_table (col1, col2) stdin delimiter '|'")  # read column values csv line line in following format: # attributes = {column_1: 'column 1 data', column_2: 'column 2 data'} # line = "#{attributes.values.join('|')}\n" rc.put_copy_data line # wrap copy process & insert & update primary table 

i wondering can or should sanitize column values. we're using rails 3.2 , postgres 9.2.

no action required; copy never interprets values sql syntax. malformed csv produce error due bad quoting / incorrect column count. if you're sending own data line-by-line should exclude line containing single \. followed newline, otherwise it's rather safe.

postgresql doesn't sanitize data in way, handles safely. if accept string ');drop table customer;-- in csv it's quite safe in copy. however, if application reads out of database, assumes "because came database not user it's safe," , interpolates sql string you're still stuffed.

similarly, incorrect use of pl/pgsql functions execute used unsafe string concatenation create problems. must use of format , %i or %l specifiers, use quote_literal / quote_ident, or (for literals) use execute ... using.

this not true of copy, it's same if insert of manipulated data use unsafely after reading db.