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? — Gerry
– Gerry, Commented Apr 28, 2018 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
– mallela prakash, Commented Apr 28, 2018 at 15:09
Please update your question 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
– Gerry, Commented Apr 28, 2018 at 15:14

Stanislav Kr. · Accepted Answer · 2018-04-28 14:58:42Z

1

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

answered Apr 28, 2018 at 14:58

Stanislav Kr.

5644 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gerry · Accepted Answer · 2018-04-28 16:48:32Z

0

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

answered Apr 28, 2018 at 16:48

Gerry

10.5k3 gold badges33 silver badges40 bronze badges

Collectives™ on Stack Overflow

Facing issues while executing SQL command using ruby on rails

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related