Sanitize MySQL data in Ruby on Rails 2

Question

am reading data from a user-supplied CSV file of unknown number of rows. I iterate through the rows and rather than individually insert each row individually into the database (potentially a performance hurting large number of inserts), I concatenate the values into a single string:

insert_values += ", (#{params[:quantity]}, #{'#{params[:name]}', '#{Time.now.to_s(:db)}')"

I then build the full sql query:

sql = "INSERT INTO `my_table` (`quantity`, `name`, `created_at`) VALUES"+insert_values

Finally, I execute it:

ActiveRecord::Base.connection.execute(sql)

I want to make the data safe from malicious injection. What is the best way to do this?

Answer 1

Thanks for all the helpful advice which led me down my final path. I ended up changing my string to an array of values to take advantage of Rails built-in method, sanitize_sql_array after reading another post.

placeholders = []
insert_values = []

csvrow.each do |row|
  placeholders << (?,?,?)
  insert_values << params[:quantity] << #{params[:name]} << {Time.now.to_s(:db)
end

query_string = "INSERT INTO `my_table` (`quantity`, `name`, `created_at`) VALUES #{placeholders.join(", ")}"] + insert_values
sql = ActiveRecord::Base.send(:sanitize_sql_array, query_string)
ActiveRecord::Base.connection.execute(sql)