Rails Postgres query on hstore to avoid SQL injection

Question

I have a class User that has an hstore attribute :preferences. If I want to find all Users where the startpage key in their preferences is nil I can do:

User.where("preferences @> 'startpage=>NULL'")

How can I structure that query so it avoids SQL query injection?

Tried:

User.where("preferences @> :key '=>NULL'", key: 'startpage')
User.where("preferences @> :key IS 'NULL'", key: 'startpage')
User.where("preferences @> :key IS NULL", key: 'startpage')
User.where("preferences @> ? IS NULL", 'startpage')

Without luck. Anyone know how to do this?

mu is too short · Accepted Answer · 2016-01-08 05:01:50Z

1

The @> operator expects hstores on both sides. When you say:

some_hstore @> 'startpage=>NULL'

PostgreSQL will implicitly add the ::hstore cast as if you had said:

some_hstore @> 'startpage=>NULL'::hstore

But there are other ways to create an hstore. From the fine manual:

Function: hstore(text, text)
Return Type: hstore
Description: make single-item hstore

So you can switch to the more explicit hstore(text, text) function and let ActiveRecord do its normal thing with placeholders and strings:

User.where("preferences @> hstore(:key, null)", :key => 'start page')

answered Jan 8, 2016 at 5:01

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

Rails Postgres query on hstore to avoid SQL injection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related