X-Git-Url: https://git.arvados.org/arvados.git/blobdiff_plain/0e2a467a923bd490f3e1dc4d8c00a9e2f29e59d4..16c7c2547c1c4ae9a00d346fee7c44b0e7ca1c7e:/services/api/lib/record_filters.rb diff --git a/services/api/lib/record_filters.rb b/services/api/lib/record_filters.rb index 994e850310..e51223254f 100644 --- a/services/api/lib/record_filters.rb +++ b/services/api/lib/record_filters.rb @@ -19,7 +19,7 @@ module RecordFilters # +model_class+ subclass of ActiveRecord being filtered # # Output: - # Hash with two keys: + # Hash with the following keys: # :cond_out array of SQL fragments for each filter expression # :param_out array of values for parameter substitution in cond_out # :joins array of joins: either [] or ["JOIN containers ON ..."] @@ -31,7 +31,10 @@ module RecordFilters model_table_name = model_class.table_name filters.each do |filter| attrs_in, operator, operand = filter - if attrs_in == 'any' && operator != '@@' + if operator == '@@' + raise ArgumentError.new("Full text search operator is no longer supported") + end + if attrs_in == 'any' attrs = model_class.searchable_columns(operator) elsif attrs_in.is_a? Array attrs = attrs_in @@ -44,9 +47,10 @@ module RecordFilters raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter") end + operator = operator.downcase cond_out = [] - if attrs_in == 'any' && (operator.casecmp('ilike').zero? || operator.casecmp('like').zero?) && (operand.is_a? String) && operand.match('^[%].*[%]$') + if attrs_in == 'any' && (operator == 'ilike' || operator == 'like') && (operand.is_a? String) && operand.match('^[%].*[%]$') # Trigram index search cond_out << model_class.full_text_trgm + " #{operator} ?" param_out << operand @@ -54,22 +58,6 @@ module RecordFilters attrs = [] end - if operator == '@@' - # Full-text search - if attrs_in != 'any' - raise ArgumentError.new("Full text search on individual columns is not supported") - end - if operand.is_a? Array - raise ArgumentError.new("Full text search not supported for array operands") - end - - # Skip the generic per-column operator loop below - attrs = [] - # Use to_tsquery since plainto_tsquery does not support prefix - # search. And, split operand and join the words with ' & ' - cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)" - param_out << operand.split.join(' & ') - end attrs.each do |attr| subproperty = attr.split(".", 2) @@ -98,9 +86,9 @@ module RecordFilters end # jsonb search - case operator.downcase + case operator when '=', '!=' - not_in = if operator.downcase == "!=" then "NOT " else "" end + not_in = if operator == "!=" then "NOT " else "" end cond_out << "#{not_in}(#{attr_table_name}.#{attr} @> ?::jsonb)" param_out << SafeJSON.dump({proppath => operand}) when 'in' @@ -133,31 +121,53 @@ module RecordFilters end when 'exists' if operand == true - cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)" + cond_out << "jsonb_exists_inline_op(#{attr_table_name}.#{attr}, ?)" elsif operand == false - cond_out << "(NOT jsonb_exists(#{attr_table_name}.#{attr}, ?)) OR #{attr_table_name}.#{attr} is NULL" + cond_out << "(NOT jsonb_exists_inline_op(#{attr_table_name}.#{attr}, ?)) OR #{attr_table_name}.#{attr} is NULL" else raise ArgumentError.new("Invalid operand '#{operand}' for '#{operator}' must be true or false") end param_out << proppath + when 'contains' + cond_out << "#{attr_table_name}.#{attr} @> ?::jsonb OR #{attr_table_name}.#{attr} @> ?::jsonb" + param_out << SafeJSON.dump({proppath => operand}) + param_out << SafeJSON.dump({proppath => [operand]}) else raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'") end - elsif operator.downcase == "exists" - if col.type != :jsonb + elsif operator == "exists" + if col.nil? or col.type != :jsonb raise ArgumentError.new("Invalid attribute '#{attr}' for operator '#{operator}' in filter") end - cond_out << "jsonb_exists(#{attr_table_name}.#{attr}, ?)" + cond_out << "jsonb_exists_inline_op(#{attr_table_name}.#{attr}, ?)" param_out << operand + elsif expr = /^ *\( *(\w+) *(<=?|>=?|=) *(\w+) *\) *$/.match(attr) + if operator != '=' || ![true,"true"].index(operand) + raise ArgumentError.new("Invalid expression filter '#{attr}': subsequent elements must be [\"=\", true]") + end + operator = expr[2] + attr1, attr2 = expr[1], expr[3] + allowed = attr_model_class.searchable_columns(operator) + [attr1, attr2].each do |tok| + if !allowed.index(tok) + raise ArgumentError.new("Invalid attribute in expression: '#{tok}'") + end + col = attr_model_class.columns.select { |c| c.name == tok }.first + if col.type != :integer + raise ArgumentError.new("Non-numeric attribute in expression: '#{tok}'") + end + end + cond_out << "#{attr1} #{operator} #{attr2}" else - if !attr_model_class.searchable_columns(operator).index attr + if !attr_model_class.searchable_columns(operator).index(attr) && + !(col.andand.type == :jsonb && ['contains', '=', '<>', '!='].index(operator)) raise ArgumentError.new("Invalid attribute '#{attr}' in filter") end + attr_type = attr_model_class.attribute_column(attr).type - case operator.downcase + case operator when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike' - attr_type = attr_model_class.attribute_column(attr).type operator = '<>' if operator == '!=' if operand.is_a? String if attr_type == :boolean @@ -171,8 +181,8 @@ module RecordFilters when '0', 'f', 'false', 'n', 'no' operand = false else - raise ArgumentError("Invalid operand '#{operand}' for " \ - "boolean attribute '#{attr}'") + raise ArgumentError.new("Invalid operand '#{operand}' for " \ + "boolean attribute '#{attr}'") end end if operator == '<>' @@ -196,6 +206,10 @@ module RecordFilters cond_out << "#{attr_table_name}.#{attr} #{operator} ?" param_out << operand elsif (attr_type == :integer) + if !operand.is_a?(Integer) || operand.bit_length > 64 + raise ArgumentError.new("Invalid operand '#{operand}' "\ + "for integer attribute '#{attr}'") + end cond_out << "#{attr_table_name}.#{attr} #{operator} ?" param_out << operand else @@ -203,17 +217,24 @@ module RecordFilters "for '#{operator}' operator in filters") end when 'in', 'not in' - if operand.is_a? Array - cond_out << "#{attr_table_name}.#{attr} #{operator} (?)" - param_out << operand - if operator == 'not in' and not operand.include?(nil) - # explicitly allow NULL - cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)" - end - else + if !operand.is_a? Array raise ArgumentError.new("Invalid operand type '#{operand.class}' "\ "for '#{operator}' operator in filters") end + if attr_type == :integer + operand.each do |el| + if !el.is_a?(Integer) || el.bit_length > 64 + raise ArgumentError.new("Invalid element '#{el}' in array "\ + "for integer attribute '#{attr}'") + end + end + end + cond_out << "#{attr_table_name}.#{attr} #{operator} (?)" + param_out << operand + if operator == 'not in' and not operand.include?(nil) + # explicitly allow NULL + cond_out[-1] = "(#{cond_out[-1]} OR #{attr_table_name}.#{attr} IS NULL)" + end when 'is_a' operand = [operand] unless operand.is_a? Array cond = [] @@ -236,6 +257,31 @@ module RecordFilters end end cond_out << cond.join(' OR ') + when 'contains' + if col.andand.type != :jsonb + raise ArgumentError.new("Invalid attribute '#{attr}' for '#{operator}' operator") + end + if operand == [] + raise ArgumentError.new("Invalid operand '#{operand.inspect}' for '#{operator}' operator") + end + operand = [operand] unless operand.is_a? Array + operand.each do |op| + if !op.is_a?(String) + raise ArgumentError.new("Invalid element #{operand.inspect} in operand for #{operator.inspect} operator (operand must be a string or array of strings)") + end + end + # We use jsonb_exists_all_inline_op(a,b) instead of "a ?& + # b" because the pg gem thinks "?" is a bind var. + # + # See note in migration + # 20230815160000_jsonb_exists_functions about _inline_op + # functions. + # + # We use string interpolation instead of param_out + # because the pg gem flattens param_out / doesn't support + # passing arrays as bind vars. + q = operand.map { |s| ActiveRecord::Base.connection.quote(s) }.join(',') + cond_out << "jsonb_exists_all_inline_op(#{attr_table_name}.#{attr}, array[#{q}])" else raise ArgumentError.new("Invalid operator '#{operator}'") end