Rails. Filter Postgres objects by their Arrays. ERROR: malformed array literal

Question

Cant deal with array filtering. This:

@profiles = Profile.filter(params.slice(:location, :status, :items))

Passes records to be filtered:

def filter(filtering_params)
  results = self.where(nil)
  puts filtering_params

  filtering_params.each do |key, value|
    if value.is_a?(Array)     
      results = results.public_send(key, value.to_s) if self.where("#{key}", value)
    else
      results = results.public_send(key, value) if value.present? 
    end
  end
  results
end

But there are some problems with Postgres Array. Params passed to filter method are:

Parameters: {"utf8"=>"✓", "location"=>"0", "status"=>"0", "items"=>["0", "1"], "commit"=>"Filtruoti"}

and method receives them:

{"location"=>"0", "status"=>"0", "items"=>["0", "1"]}

But I get error:

PG::InvalidTextRepresentation: ERROR: malformed array literal: "0" LINE 1: ..." = $2 AND "profiles"."items" IN ('0', '1') ... ^

DETAIL: Array value must start with "{" or dimension information. : SELECT "profiles".* FROM "profiles" WHERE "profiles"."location" = $1 AND "profiles"."status" = $2 AND "profiles"."items" IN ('0', '1') LIMIT $3 OFFSET $4

And this error actually points to where I want to display received records:

<% rofiles.each do |profile| %>

What is happening with this Postgres Array..?

gem 'rails', '~> 5.0.3'
gem 'pg', '~> 0.18'

EDIT:

Looks like t works well now with scope:

scope :items, -> (items) { where "items && ARRAY[?]::integer[]", items }

And:

def filter(filtering_params)
  results = self.where(nil)

  filtering_params.each do |key, value|
    puts value
    if value.is_a?(Array)     
      value = [0, 1, 2]
      results = results.public_send(key, value) if self.where("key && ARRAY[?]::integer[]", value)
    else
      results = results.public_send(key, value) if value.present? 
    end
  end
  results
end

The profiles.items column is an array of integers and you want the query to find rows whose items contain 1 or 0? — mu is too short
– mu is too short, Commented Nov 6, 2017 at 20:45
@mu is too short, with the code above I tried to find only those that holds both of them. But now I actually need items that contains any of them :) And my column is array of strings, for now.. ["0", "1"] — Julius Dzidzevičius
– Julius Dzidzevičius, Commented Nov 7, 2017 at 5:42

max · Accepted Answer · 2017-11-06 22:59:02Z

9

ActiveRecord does not automatically create queries for array type columns.

Querying an array column can be done with:

Profile.where("items@> ARRAY[?]::varchar[]", ["0", "1"])
# or 
Profile.where("items@> ARRAY[?]::integer[]", [0, 1])

But if items is a model in your application you should create a join table instead. Arrays are not a replacement for relational data modeling.

Active Record and PostgreSQL

answered Nov 6, 2017 at 22:59

max

103k15 gold badges113 silver badges177 bronze badges

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

8 Comments

Julius Dzidzevičius Over a year ago

Oh, could you tell if this leaves any SQL or XSS injection possibilities? AFAIK, we must be careful with where and Arrays. I think I am relatively secure, because these runs GET, not POST - am I?

max Over a year ago

This uses a parametized query. The ? is replaced by the values passed by the db so it is not vulnerable in the same way that "items@> ARRAY[<%= params[:some_param] %>]::integer[]" would be as a the DB will reject the values instead of executing arbitrary SQL.

max Over a year ago

"I think I am relatively secure, because these runs GET, not POST - am I?" - nope you're just very wrong. The HTTP method does not matter when it comes to SQL injections.

max Over a year ago

POST is only really more secure for the client. If the client is on a unsecure public network and SSL (https) is not used it leaves the client vulnerable to traffic sniffing where an attacker can see what url was requested and thus any GET params. But this has nothing to do with SQL injections which is caused by inserting user provided data straight into a SQL string.

Julius Dzidzevičius Over a year ago

Ok, I felt like this before asking does GET matters :) But better be sure. And shouldn't I escape or sanitize that array, passed to where? I've read just a bit about this and that was a little confusing to understand - how array items being escaped in Rails

|

sequielo · Accepted Answer · 2020-12-23 02:33:38Z

0

I've used a different approach:

Firstly, made Profile.items a column of JSONB type.

Added a scope:

scope :has_item, -> (item) { where(%{ items::jsonb ?& array[:item] }, item: item) }

Finally, used it as follows:

Profile.has_item(:item_a)

# or

Profile.has_item([:item_a, :item_b])

answered Dec 23, 2020 at 2:33

sequielo

1,6411 gold badge20 silver badges27 bronze badges

Collectives™ on Stack Overflow

Rails. Filter Postgres objects by their Arrays. ERROR: malformed array literal

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related