在ruby on rails中向原始SQL查询传递参数

x6h2sr28 于 2023-10-18 发布在 Ruby

关注(0)|答案(3)|浏览(90)

我想使用rails活动记录执行一个原始SQL查询，但我的查询需要一个参数，我找不到一个合适的方法来安全地将该参数传递到查询字符串中。查询如下

def self.get_branches_by_workspace_name(workspace_name)
  branches = ActiveRecord::Base.connection.execute("
    select
      address,
      phone,
      email,
      services
    from branches as b, workspaces as w
    where b.workspace_id = w.id and w.name= :workspace_name", workspace_name).to_a
  return branches
end

我想传递一个名为“workspace_name”的参数。有什么需要帮忙的吗？

ruby

来源：https://stackoverflow.com/questions/47639790/passing-parameters-to-raw-sql-queries-inside-ruby-on-rails

3条答案

按热度按时间

bwntbbo31#

在您的模型中添加此方法

def self.execute_sql(*sql_array)     
   connection.execute(sanitize_sql_array(sql_array))
  end

这将允许您在AR模型中清理和执行任意SQL
然后运行类似于下面的代码来执行SQL，返回一个哈希数组，查询返回的每行一个哈希

ModelName.execute_sql("select address,phone,email,services from branches as b, workspaces as w 
    where b.workspace_id = w.id and w.name= ?", workspace_name)

# => [{"address"=>"...","phone"=>"...","email"=>"...","services"=>"..."}, ...]

赞(0）回复(0）举报 2023-10-18

hts6caw32#

在您的模型之外：

ActiveRecord::Base.connection.execute(
  ApplicationRecord.sanitize_sql([query, { param_1: param_1, param_2: param_2 }])
)

赞(0）回复(0）举报 2023-10-18

mf98qq943#

在你的模型中你可以这样做

sql_command = <<-SQL
    SELECT address, phone, email, services
    FROM branches as b, workspaces as w
    WHERE b.workspace_id = w.id and w.name = :workspace_name
  SQL

  connection.execute(
    sanitize_sql_for_assignment([sql_command, workspace_name: "whatever"])
  )

赞(0）回复(0）举报 2023-10-18

我来回答

在ruby on rails中向原始SQL查询传递参数

3条答案

相关问题

热门标签

最新问答