SQL query IN using Ruby array

Question

I have an array in ruby 2.0.0, @temp = [3, 4] which I want to use in SQL IN statement. So I want the brackets ([, ]) to be removed.

My SQL query:

SELECT E.id,E.name, CEU.attempt, E.total_mark, CEU.has_attended as attendance, CE.id as categoryexamId, CE.examtype_id as examType, CU.id as categoryuserId, U.name as username

      FROM exams E
      Inner Join categoryexams CE on E.id = CE.exam_id
      Inner Join categoryexamusers CEU on CE.id = CEU.categoryexam_id
      Inner Join categoryusers CU on CEU.categoryuser_id = CU.id
      Inner Join categories C on CE.category_id = C.id
      Inner Join users U on CU.user_id = U.id
      Inner Join examtypes ET on CE.examtype_id = ET.id
      WHERE CE.category_id = #{category_id} AND CEU.has_attended = 1 AND U.id = #{user} AND CE.currentyear = #{academicYear} AND CE.examtype_id = #{examtype}" + (@temp.blank? ? "" : " AND CEU.categoryexam_id NOT IN (#{@temp})") +
      " Group By CE.id;

Your code is insecure and has several SQL injections!! See also the Rails Guides section on SQL injection — Andrew Marshall
– Andrew Marshall, Commented Aug 18, 2013 at 17:37
I found it very difficult to convert this query in Rails Active record querying format. Can you please help? — Rajesh Omanakuttan
– Rajesh Omanakuttan, Commented Aug 19, 2013 at 1:53

Peter Brown · Accepted Answer · 2013-08-18 17:17:38Z

8

With Active Record, you can just use the array directly when using the "hash conditions" format, and it will automatically convert it to the right SQL:

MyModel.where(column: [3, 4])

See the Rails Guide on Active Record querying "Subset Conditions" 2.3.3

answered Aug 18, 2013 at 17:17

Peter Brown

51.8k18 gold badges116 silver badges147 bronze badges

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

1 Comment

Kevin Over a year ago

Use Rails Active Record to create your queries as often as possible. This protects you from SQL injection attacks.

Aman Garg · Accepted Answer · 2013-08-18 16:48:05Z

8

If you want to use this as a string in where query. You can write it as:

@temp.join(',')

hopes, it will work.

answered Aug 18, 2013 at 16:48

Aman Garg

4,2282 gold badges24 silver badges29 bronze badges

2 Comments

Andrew Marshall Over a year ago

-1 This allows adding further potential SQL injections to the query.

Pak Over a year ago

How do you even know the query will face user inputs ?

Collectives™ on Stack Overflow

SQL query IN using Ruby array

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related