CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
Sayed Alesawy
Sayed Alesawy

Reputation: 455

Passing parameters to raw SQL queries inside ruby on rails

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?

Upvotes: 18

Views: 24063

Answers (3)

Ishtiaque05
Ishtiaque05

Reputation: 451

In your model add this method

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

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

Then run something like this to execute the SQL, returning an array of hashes, one per row returned by your query

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"=>"..."}, ...]

Upvotes: 13

user3033467
user3033467

Reputation: 1188

Outside your model:

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

Upvotes: 15

Spyros
Spyros

Reputation: 247

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

Upvotes: 6

Related Questions