current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
2

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))
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.