ActiveRecord - #where method when field is a serialized empty array

Question

I have an ActiveRecord query:

Post.where(users: [])

In Rails 5.1.1, this results in:

SELECT `posts`.* FROM `posts` WHERE (users = '[]')

but in Rails 5.2.2, this results in:

SELECT `posts`.* FROM `posts` WHERE 1=0

Does anyone know if this is expected behaviour in Rails 5.2 or a bug?

Why not just use a proper join table instead of a serialized array? Thats probably the worst possible design. — max
– max, Commented Dec 6, 2018 at 9:08

kishore cheruku · Accepted Answer · 2018-12-06 09:39:06Z

1

That is not a bug , 5.2.2 updated for remove SQL Injection problem.

SELECT `posts`.* FROM `posts` WHERE (users = '[]')

If you observe this query we have a SQL Injection problem , that is if you pass some query instead of value then it will pass . It's too dangerous. You can use below code for to retrieve data based on array objects with where condition.

ModelName.where('users IN (?)', [array of elements] )

edited Dec 6, 2018 at 9:39

answered Dec 6, 2018 at 9:18

kishore cheruku

5212 silver badges11 bronze badges

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

5 Comments

Raj Over a year ago

Can you explain more how this is a SQL inject problem? what do you mean by "query instead of value"?

mbbxadam Over a year ago

But you can still do Post.where(some_value: some_integer). My question is more about why we can no longer query for serialized arrays.

kishore cheruku Over a year ago

Follow "netsparker.com/blog/web-security/…" this link . You will get better idea . If you have any doubt then comment me again .

kishore cheruku Over a year ago

You can use where condition with array like this ' Model.where('users IN (?)', [array of elements] ) '. From this we can grab all records of Model with array objects

mbbxadam Over a year ago

So how can I get around this, I can now never use ActiveRecord's #where method when one of the fields is serialized...

Collectives™ on Stack Overflow

ActiveRecord - #where method when field is a serialized empty array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related