0

This morning I switched to postgresql and now my where selects dont work anymore.

What I am trying to do is super simple:

shirt_ids = "1,5,6" # String generated by javascript
Shirt.where("id in (?)", shirt_ids)

This gives me :

PG::Error: ERROR:  invalid input syntax for integer: "1,5,6"
LINE 1: SELECT "shirts".* FROM "shirts"  WHERE (id in ('1,5,6'))

This works though:

Shirt.where("id in (#{shirt_ids})")

But as everybody knows is unsafe.

Im using:

pg (0.13.2 x86-mingw32)
rails (3.2.2)

Postgresql database is the newest version, I installed it this morning.

Thank you for your help. ^

Stephan1990
  • 445
  • 4
  • 19

1 Answers1

6

I believe Postgres is expecting an array, rather than a string for the IN function. If you convert your string to an array, it should work:

shirt_ids = "1,5,6"
Shirt.where('id in (?)', shirt_ids.split(','))

Also, you can do this, which looks a bit cleaner:

Shirt.where(:id => shirt_ids.split(','))
MrTheWalrus
  • 9,670
  • 2
  • 42
  • 66
  • Maybe a little too quick... I realized that my original answer 'works' but actually only returns one object. Edited accordingly, this one should be fine. – MrTheWalrus May 07 '12 at 04:54
  • 2
    More accurately, ActiveRecord sees a `?` in the SQL snippet and a String as the value, then it converts that Ruby String into the SQL string literal `'1,2,3'`. I think `shirt_ids.split(',').map(&:to_i)` would be safer. – mu is too short May 07 '12 at 05:02
  • Either way quite ugly compared to the simple `Shirt.where("id in (?)", shirt_ids)` or even simpler (I wish this would work) `Shirt.where(id: in(shirt_ids))`. I guess I will go with `Shirt.where(id: shirt_ids.split(","))`. @muistooshort Why would `.map(&:to_i)` make it safer? Doesnt postgresql automatically throw an error if some evil hacker would try to send in a string? (Which is very easy since this comes from a form) – Stephan1990 May 07 '12 at 05:21
  • 1
    @Stefano: The `id` values are integers and should be supplied as such, you can usually get away with using `'1'` in place of `1` but not always. If you use an array of Strings, you get `id in ('1','2','3')` when you want `id in (1,2,3)`. – mu is too short May 07 '12 at 05:33