how to insert an array of strings to sql query in ruby

Question

I have this query in ruby:

  sql = "SELECT variants.id,
                      code,
                      regular_price,
                      price_before_sale
                FROM  variants
                WHERE variants.code IN (#{context.codes.join(",")})"

where context.codes = ['PRDCT-1','PRDCT-2']

now context.codes becomes (PRDCT1,PRDCT2) inside the sql query because of the .join but what I want to happen is ('PRDCT1','PRDCT2') what am I missing?

EDI: I have tried to do (#{context.codes.join("','")}) but it returns (PRDCT1','PRDCT2)

Did you consider using an ORM like ActiveRecord or Sequel? There are extremely helpful when it comes to build SQL queries and to sanitize them against SQL injection. — spickermann
– spickermann, Commented Sep 4, 2019 at 7:09

Amadan · Accepted Answer · 2019-09-04 07:10:46Z

8

Don't do that. Bobby Tables is watching. Instead, provide the adequate number of placeholders:

sql = "SELECT variants.id,
                      code,
                      regular_price,
                      price_before_sale
                FROM  variants
                WHERE variants.code IN (#{context.codes.map { "?" }.join(",")})"

and then provide *context.codes in statement parameters.

answered Sep 4, 2019 at 7:10

Amadan

200k23 gold badges252 silver badges321 bronze badges

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

1 Comment

Marek Lipka Over a year ago

I guess in this case, where the programmer has full control of what's stored in context.codes, it isn't that crucial. But it's a good practice nevertheless.

eibersji · Accepted Answer · 2019-09-04 07:09:41Z

-1

I got it. I added single quotes to ('#{context.codes.join("','")}')

answered Sep 4, 2019 at 7:09

eibersji

1,2164 gold badges31 silver badges54 bronze badges

1 Comment

Stefan Over a year ago

Adding the quotes to the separator seems a bit dirty. You should escape each value separately (according to the query syntax) and then join the results.

Collectives™ on Stack Overflow

how to insert an array of strings to sql query in ruby

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related