字符串的Ruby / Rails的阵列到PostgreSQL插入 [英] Ruby / Rails array of strings to PostgreSQL insert

问题描述

目前，我有一个轨道项目中，我试图创建使用字符串数组，其中的值不存在（避免重复）去走一走活动记录，以直线的Postgres为了做一个大批量。我现在面临的问题是试图逃跑包含字符串的字符'或（，等..在说红宝石阵列可以接受的Postgres举例如下（显然它不工作）：

红宝石阵：

  array_of_strings = [比如一个人的价值，例如（2）值]

SQL =insert into表名（列）由表UNION SELECT列值＃{array_of_strings}除外表格中选择列;

的ActiveRecord :: Base.connection.execute（的ActiveRecord :: Base.send（：sanitize_sql_array，SQL））
 

解决方案

这样的事情我会忽略ActiveRecord的报价和逃避的东西，直奔的ActiveRecord :: Base.connection.quote 。

你想结束与SQL是这样的：

  insert into表名（列）
从表格中选择列
UNION VALUES（'例如一个人的价值），（'例如（2）价值）
除外表中选择列
 

您可以使用报价转换到（注意，SQL双打单引号逃逸它们），但你必须添加相应的括号自己的值。连接的报价方法也将增加外单引号。

事情是这样的：

 值= array_of_strings.map {| S | （＃{的ActiveRecord :: Base.connection.quote（多个）}）}。加入（，）
 

依次为：

  SQL =％Q {
    INSERT INTO表（列）
    从表格中选择列
    UNION价值观＃{}值
    除外表中选择列
}
 

应该做的伎俩和安全。

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))

解决方案

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.

这篇关于字符串的Ruby / Rails的阵列到PostgreSQL插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！