Non model SQL query with parameters in Rails

Question

Using rails 4, I am using a custom SQL statement that I don't think I can relate to a Model specifically so I am thinking of using a RAW SQL:

  sql = "select categ_a.processu_id, 
                categ_a.category_id as category_id_a,
                categ_a.group_id as group_id_a,
                categ_b.category_id as category_id_b,
                categ_b.group_id as group_id_b
        from
        (select processu_id, category_id, group_id from categorizations where group_id = #{group_id_a}) 
          categ_a INNER JOIN
        (select processu_id, category_id, group_id from categorizations where group_id = #{group_id_b}) 
          categ_b ON categ_a.processu_id = categ_b.processu_id"

  processus_array = ActiveRecord::Base.connection.execute(sql, {:group_id_a => group_id_a, :group_id_b => group_id_b})

The table "categorisations" has - in this case - two entries for each processu_id one for each of the group_id.

My question really is about how to pass parameters to ActiveRecord::Base.connection.execute so that it will take the two group_ids. The statement above gives me an error.

Community · Accepted Answer · 2017-05-23 11:43:36Z

4

Being mindful of SQL injection, you should probably use one of Rails' provided sanitization methods, rather than normal interpolation.

See the first answer here for a snippet that will let you sanitize and execute arbitrary SQL in an AR model: Ruby on Rails: How to sanitize a string for SQL when not using find?

If you include that, you should be able to do ModelName.execute_sql('select stuff where attribute = ? and other_attribute = ?', first_value, second_value'), just as you would in a normal ActiveRecord .where() method.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Apr 20, 2015 at 14:45

MrTheWalrus

9,7102 gold badges44 silver badges67 bronze badges

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

4 Comments

VincentBZ Over a year ago

Thanks. I am not sure I can actually call a Model because my query mixes 2 rows and combine them in a new - non-model - output.

MrTheWalrus Over a year ago

You can actually put any SQL into the execute_sql method from that answer - it doesn't have to deal with a specific model. The point is to make sure to pass your query through ActiveRecord::Base's .sanitize_sql_array method. (It is, for some reason, a protected method, so you have to use the send syntax in the linked answer.)

VincentBZ Over a year ago

Thanks MrTheWalrus. I'll give it a go as well.

Drenmi Over a year ago

This is the better way.

Drenmi · Accepted Answer · 2015-04-20 14:39:48Z

-3

You can use Kernel#sprintf to format your string in place. For example, if your IDs are integers:

sql = "SELECT
         categ_a.processu_id, 
         categ_a.category_id as category_id_a,
         categ_a.group_id as group_id_a,
         categ_b.category_id as category_id_b,
         categ_b.group_id as group_id_b
       FROM
         (SELECT processu_id, category_id, group_id FROM categorizations WHERE group_id = %d) categ_a
       INNER JOIN
         (SELECT processu_id, category_id, group_id FROM categorizations WHERE group_id = %d) categ_b
       ON
         categ_a.processu_id = categ_b.processu_id"

processus_array = ActiveRecord::Base.connection.execute(sprintf(sql, group_id_a, group_id_b))

Be careful, though, that you don't create any opportunities for SQL injection.

answered Apr 20, 2015 at 14:39

Drenmi

8,8064 gold badges46 silver badges52 bronze badges

3 Comments

MrTheWalrus Over a year ago

While you mention the risks of SQL injection, I think it's a sufficiently serious issue that this is not a good idea. I strongly recommend getting in the habit of always sanitizing SQL, even when you think there's no way that user-entered data could end up in the query.

VincentBZ Over a year ago

Thanks. Parameters are sequenced ids generated by the database so this probably minimise the risk of sql injection.

Drenmi Over a year ago

@MrTheWalrus: I definitely agree. I updated my answer to put emphasis on this. If the ids are integers, sprintf is effectively sanitization though, as any non-integer value will throw an exception (which is probably another downside of using this approach.)

Collectives™ on Stack Overflow

Non model SQL query with parameters in Rails

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related