Passing parameters to raw SQL queries inside ruby on rails

Question

I want to execute a raw SQL query using rails active record, but my query takes a parameter, I can't find a proper way to safely pass that parameter into the query string. The query is as follows

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

I would like to pass a parameter named "workspace_name". Any help?

Solution 1

In your model add this method

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

This will let you sanitize and execute arbitrary SQL in an AR model

Then simply do this

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)

Solution 2

In your model you can do this

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

Comments

[Tonči D.]

Why do you need send? The sanitize_sql_array method is protected.