Ruby / Rails array of strings to PostgreSQL insert

Question

Currently, I have a rails project where I am trying to go around active record to straight postgres in order to do a large batch create using an array of strings, where the values to not already exist (to avoid duplication). The problem I am facing is trying to escape the string characters that contain ' or (, ect.. in said ruby array to be acceptable to postgres. Example as follows (obviously its not working):

Ruby array:

array_of_strings = ["example one's value", "example (2) value"]

sql = "INSERT INTO TABLE (column) SELECT column FROM TABLE UNION VALUES #{array_of_strings} EXCEPT SELECT column FROM TABLE;"

ActiveRecord::Base.connection.execute(ActiveRecord::Base.send(:sanitize_sql_array, sql))

Answer 1

For something like this I'd ignore the ActiveRecord quoting and escaping stuff and go straight to ActiveRecord::Base.connection.quote.

The SQL you're trying to end up with is something like this:

INSERT INTO TABLE (column)
SELECT column FROM TABLE
UNION VALUES ('example one''s value'), ('example (2) value')
EXCEPT SELECT column FROM TABLE

You can use quote to convert ' to '' (note that SQL doubles single quotes to escape them) but you'll have to add the appropriate parentheses in the VALUES yourself. The connection's quote method will also add the outer single quotes.

Something like this:

values = array_of_strings.map { |s| "(#{ActiveRecord::Base.connection.quote(s)})" }.join(',')

followed by:

sql = %Q{
    INSERT INTO TABLE (column)
    SELECT column FROM TABLE
    UNION VALUES #{values}
    EXCEPT SELECT column FROM TABLE
}

should do the trick and be safe.