On Thursday, June 14, 2018 at 7:58:30 AM UTC-7, Juan M. Cuello wrote:
>
> Hi,
>
> I wonder what's the right way to set Postgres search_path using user
> input, without compromising security due to SQL injection.
> Say we have a request with many schema names as params and we have to set
> the schema path:
>
> params[:schema_names] = ['foo', 'bar']
>
> And we need:
>
> "SET search_path = foo, bar"
>
> Taking a look at adapters/shared/postgres.rb#893, I can think of something
> like this:
>
> clean_paths = params[:schema_names].map { |s| "\"#{s.gsub('"', '""')}\""
> }.join(',')
> DB.run("SET search_path = #{clean_paths}")
>
> But I'm not sure if that's all we need to avoid any risk.
>
> ¿Any thoughts?
>
>
Seems fairly dangerous to allow user input to choose an arbitrary search
path. While your approach looks like it should work and be safe, I would
be leery of doing that.
My approach would be to get a list of all valid schemas in the database,
and compare against that:
schema_names = params[:schema_names] = ['foo', 'bar']
valid_schema_names = DB[:pg_namespace].select_order_map(:nspname)
raise unless (schema_names - valid_schema_names).empty?
This is assuming you don't allow users to create arbitrary schema names.
If you do, the whitelist is not going to help much.
You may want to ask on a PostgreSQL mailing list about the safest way to do
this, since the question isn't necessary Sequel-specific.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
