inserting variable into complex sql command

Question

I have a set-up with multiple contests and objects. They are tied together with a has_many :through arrangement with contest_objs. contest_objs also has votes so I can have several contests including several objects. I have a complex SQL setup to calculate the current ranking. However, I need to specify the contest in the SQL select statement for the ranking. I am having difficulty doing this. This is what I got so far:

@objects = @contest.objects.select('"contest_objs"."votes" AS v, name, "objects"."id" AS id, 
                                   (SELECT COUNT(DISTINCT "oi"."object_id")
                                           FROM  contest_objs oi
                                           WHERE ("oi"."votes") > ("contest_objs"."votes"))+1 AS vrank')

Is there any way in the selection of vrank to specify that WHERE also includes "oi"."contest_id" = @contest.id ?

mclin · Accepted Answer · 2015-01-09 19:28:06Z

3

Use sanitize_sql_array:

sanitize_sql_array('select ? from foo', 'bar')

If you're outside a model, because the method is protected you have to do this:

ActiveRecord::Base.send(:sanitize_sql_array, ['select ? from foo', 'bar'])

http://apidock.com/rails/ActiveRecord/Sanitization/ClassMethods/sanitize_sql_array

answered Jan 9, 2015 at 19:28

mclin

3,6391 gold badge22 silver badges18 bronze badges

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

Comments

Intrepidd · Accepted Answer · 2012-08-28 22:19:40Z

2

Since @contest.id is an integer and does not present any risk of an SQL Injection, you could do the following using string interpolation :

Model.select("..... WHERE id = #{@contest.id}")

Another possible solution would be to build your subquery using ActiveRecord, and then call .to_sql in order to get the generated SQL, and insert it in your main query.

answered Aug 28, 2012 at 22:19

Intrepidd

21.2k7 gold badges59 silver badges64 bronze badges

5 Comments

Marc Over a year ago

I had tried that and it gives me this error 'ActiveRecord::StatementInvalid (SQLite3::SQLException: unrecognized token: "#"'

Intrepidd Over a year ago

Are you SURE you're using double quotes ? String interpolation does not work with single quotes.

Marc Over a year ago

Huh. It worked. I always thought single and double quotes were interchangeable. It works!

raddrick Over a year ago

@Marc only "" are evaluated. '' is a string literal.

BC. Jun 17 at 23:02

Don't do this. ActiveRecord prepared statement cache will leak memory.

Felix · Accepted Answer · 2012-08-28 22:39:22Z

-1

You can insert variables into sql commands like this:

Model.select("...... WHERE id = ?", @contest.id)

Rails will escape the values for you.

Edit:

This does not work as stated by Intrepidd in the comments, use string interpolation like he suggested in his answer. That is safe for integer parameters.

If you find yourself inserting several strings in a query, you could consider using find_by_sql, which gives you the above mentioned ? replacement, but you can't use it with chaining, so rewriting the whole query would be needed.

edited Aug 28, 2012 at 22:39

answered Aug 28, 2012 at 22:22

Felix

8125 silver badges11 bronze badges

3 Comments

Intrepidd Over a year ago

False. select only accepts one parameter and does not escape parameters. It will work with where but not select. You'll get the following error : ArgumentError: wrong number of arguments (2 for 1)

Felix Over a year ago

thanks for the clarification, but he could use find_by_sql, right?

Intrepidd Over a year ago

Indeed, but he'll have to write the entire SQL query then.

Collectives™ on Stack Overflow

inserting variable into complex sql command

3 Answers 3

Comments

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related