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.

score 4 · Accepted Answer · edited May 23 '17 at 11:43

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 '17 at 11:43

Community

1
1

answered Apr 20 '15 at 14:45

MrTheWalrus

9,670
2
42
66

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. – VincentBZ Apr 22 '15 at 17:05
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.) – MrTheWalrus Apr 22 '15 at 17:08
Thanks MrTheWalrus. I'll give it a go as well. – VincentBZ Apr 23 '15 at 12:17
This is the better way. – Drenmi Apr 23 '15 at 12:36

score -3 · Answer 2 · answered Apr 20 '15 at 14:39

-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 '15 at 14:39

Drenmi

8,492
4
42
51

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. – MrTheWalrus Apr 20 '15 at 14:58
Thanks. Parameters are sequenced ids generated by the database so this probably minimise the risk of sql injection. – VincentBZ Apr 22 '15 at 17:03
@MrTheWalrus: I definitely agree. I updated my answer to put emphasis on this. If the `id`s 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.) – Drenmi Apr 23 '15 at 04:40

Non model SQL query with parameters in Rails

2 Answers2