Facing issues while executing SQL command using ruby on rails

Question

In my controller, I have this method:

def show
    @final =final_params
    allparams=''
    ActiveRecord::Base.connection.execute("USE database")
    ActiveRecord::Base.connection.execute("declare @p3 dbo.Params")
    @final.each do |key, value|
      allparams= "insert into @p3 values(N'""#{key}"+"'"++",N'"+"#{value}"+"')\n"
      ActiveRecord::Base.connection.execute(allparams)
    end
  end

But, I am facing the below error:

TinyTds::Error: Must declare the table variable "@p3".: insert into @p3 values(N'a',N'aa')

In my above code @p3 is a table name parameter.

I am using SQL Server for database.

Possible duplicate of [how to call stored procedure in ruby on rails?](https://stackoverflow.com/questions/12496919/how-to-call-stored-procedure-in-ruby-on-rails) — Gerry, Apr 28 '18 at 14:12
I have changed my method so that I can execute SQL statements as below: def show @final =final_params allparams='' ActiveRecord::Base.connection.execute("USE database") ActiveRecord::Base.connection.execute("declare @p3 dbo.FormParams") @final.each do |key, value| allparams= "insert into @p3 values(N'""#{key}"+"'"++",N'"+"#{value}"+"')\n" ActiveRecord::Base.connection.execute(allparams) end end But now, I am facing the below error: `TinyTds::Error: Must declare the table variable "@p3".: insert into @p3 values(N'a',N'aa')` — mallela prakash, Apr 28 '18 at 15:09
Please [update your question](https://stackoverflow.com/posts/50077058/edit) instead of including (or changing) details in the comments, it will be easier for everyone to understand your problem and more likely to receive a helpful answer. — Gerry, Apr 28 '18 at 15:14

score 1 · Answer 1 · answered Apr 28 '18 at 14:58

1

#{@p3}, but this code looks like vulnerable to SQL-injection.

answered Apr 28 '18 at 14:58

Stanislav Kr.

504
4
12

score 0 · Answer 2 · answered Apr 28 '18 at 16:48

You need to interpolate the @p3 variable into the sql string assigned to allparams, like this:

@final.each do |key, value|
  allparams = "insert into #{@p3} values(N'""#{key}"+"'"++",N'"+"#{value}"+"')\n"
  ActiveRecord::Base.connection.execute(allparams)
end

Facing issues while executing SQL command using ruby on rails

2 Answers2