How to use sql IN query in SQL heredoc

Question

I want to use constant of an array in SQL heredoc like:

ROLES = ['admin', 'staff', 'guest']

query = <<-SQL
  SELECT * FROM users
  WHERE role IN (#{ROLES})
SQL

ActiveRecord::Base.connection.execute(query)

but getting syntax error

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "["
LINE 2: WHERE role IN (["admin", "staff", "guest"])
                       ^

Any reason why you're using ActiveRecord::Base.connection.execute for such a query? — Sebastián Palma
– Sebastián Palma, Commented Mar 30, 2020 at 11:54
@SebastianPalma here I am using ActiveRecord::Base.connection.execute(query) just to demonstrate the situation. — Rakesh Patidar
– Rakesh Patidar, Commented Mar 30, 2020 at 12:04

3limin4t0r · Accepted Answer · 2020-03-30 12:23:19Z

2

Instead of building your own Ruby to SQL converter you could also make use of the arel gem which already does the necessary escaping and quoting.

users = User.arel_table

ApplicationRecord.connection.execute(<<~SQL.squish)
  SELECT *
  FROM users
  WHERE #{users[:role].in(ROLES).to_sql}
SQL

Or if you'd like to go full arel.

ApplicationRecord.connection.execute(
  users.project(users[Arel.star])
       .where(users[:role].in(ROLES))
       .to_sql
)

edited Mar 30, 2020 at 12:23

answered Mar 30, 2020 at 12:13

3limin4t0r

21.4k3 gold badges38 silver badges61 bronze badges

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

2 Comments

mu is too short Over a year ago

Or, if you need/want to go with straight SQL, use connection.quote to properly quote the strings.

3limin4t0r Over a year ago

@muistooshort I didn't know about that helper. Seems pretty handy, but might be better suited for Marek Lipka's answer, since that answer maps over the ROLES and quotes them.

Marek Lipka · Accepted Answer · 2020-03-30 12:03:18Z

0

Instead of passing the result of ROLES.inspect, you should make the string with roles yourself, I would use map and join for that purpose:

query = <<-SQL
  SELECT * FROM users
  WHERE role IN (#{ROLES.map { |role| "'#{role}'" }.join(',')})
SQL
ApplicationRecord.connection.execute(query)

edited Mar 30, 2020 at 12:03

answered Mar 30, 2020 at 11:08

Marek Lipka

51.3k7 gold badges90 silver badges93 bronze badges

3 Comments

Rakesh Patidar Over a year ago

ROLES.join(',') will reproduce like WHERE role IN ('admin, staff, guest') but it must be like WHERE role IN ('admin', 'staff', 'guest')

Marek Lipka Over a year ago

Right, how about now?

3limin4t0r Over a year ago

Note that this can still fail if the any of the ROLES include a ' character, and is susceptible to SQL injection if the dynamic data comes from an external user. Both shouldn't be an issue when looking at the question. But since OP says "here I am using ... just to demonstrate the situation" I thought it's worth mentioning.

Collectives™ on Stack Overflow

How to use sql IN query in SQL heredoc

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related