0

I'm passing the raw query to ActiveRecord, since, I have to create the Dynamic Query on the Rails backend. However, it gives an error that

"ActionView::Template::Error (PG::SyntaxError: ERROR: syntax error at or near "INTERSECT" but when I run the query on Postgres SQL it did not give me the error here is the query

select attributable_id from custom_attribute_values where (custom_attribute_values.value_string='Male' AND custom_attribute_values.custom_attribute_id=12) OR (custom_attribute_values.value_string='Female' AND custom_attribute_values.custom_attribute_id=12) intersect select attributable_id from custom_attribute_values where ( (custom_attribute_values.value_datetime='1967-11-15 00:00:00.000000' AND custom_attribute_values.custom_attribute_id=13)) intersect select attributable_id from custom_attribute_values where ( (custom_attribute_values.value_string='Marshall' AND custom_attribute_values.custom_attribute_id=9) )

Here is my data that I'm sending to the rails server

Here is the code which gives the error. Rails code

{
"filters": {
    "filters_data": [
        [
            {
                "custom_attribute_id": 12,
                "operator": "=",
                "value": "Female",
                "data_type": "string"
            },
            {
                "custom_attribute_id": 12,
                "operator": "=",
                "value": "Male",
                "data_type": "string"
            }
        ],
        [
            {
                "custom_attribute_id": 13,
                "operator": "=",
                "value": "1967-11-15 00:00:00.000000",
                "data_type": "datetime"
            }
        ],
        [
            {
                "custom_attribute_id": 9,
                "operator": "=",
                "value": "Marshall",
                "data_type": "string"
            }
        ]
    ]
}

}

def fetch_members

begin
  query=segmentation_query
  @filter_result = CustomAttributeValue.where(custom_attribute_id: 
  filter_params[:attribute_ids]).where(segmentation_query).select('attributable_id')

rescue Exception => e
  render_json_response(response, @success, e.message, e)
end
end

def filter_params

filter_params = params.require(:filters)
filter_params[:attribute_ids] = filter_params[:filters_data].map{ |filter_array| filter_array.map { |filter_object| filter_object["custom_attribute_id"] }}.uniq
filter_params

end

  def segmentation_query
query=""
filter_params[:filters_data].each_with_index do |array, index|

  array.each_with_index do |attribute_object, index|
      query+= "("+ to_sql_string('custom_attribute_id','column_name') +to_sql_string('=', 'operator')+
        to_sql_string(attribute_object['custom_attribute_id'],'column_value') +' AND '+
        to_sql_string('value_'+attribute_object['data_type'],'column_name') +
        to_sql_string(attribute_object['operator'], 'operator')+to_sql_string(attribute_object['value'], 'column_value')+")"

      query += ' ' +'OR'+ ' ' if index != array.length - 1
  end
  if index != filter_params[:filters_data].length-1
    query+= " INTERSECT SELECT "+ to_sql_string('custom_attribute_values', 'column_name')+ "." +to_sql_string('attributable_id','column_name') +" FROM "+ to_sql_string('custom_attribute_values', 'column_name')+ " WHERE "
  end

end
query
end

def to_sql_string(data, type)
data = if type.eql? 'column_name' then '"' + data.to_s+'"' elsif type.eql? 'operator' then +" "+data.to_s+" " else "'"+ data.to_s+"'"  end
end

enter image description here

enter image description here

enter image description here

Adnan
  • 195
  • 3
  • 9
  • 1
    The two queries are different. The rails screenshot shows that there is additional logic at the start of the query and, critically, the `INTERSECT` is *inside the first select* (maybe you're just missing bracket?!), which is not valid. – Tom Lord Mar 23 '21 at 18:41
  • 1
    However, I cannot diagnose exactly what's gone wrong, because you have not shown how `segmentation_query` is defined. – Tom Lord Mar 23 '21 at 18:42
  • @TomLord I have attached the error log also the SQL query and code also please could you see the code – Adnan Mar 23 '21 at 18:43
  • @TomLord the segmentation_query actually making the dynamic query is working fine the problem is when I'm passing the SQL to active records it gave me an error – Adnan Mar 23 '21 at 18:46
  • 1
    On the contrary @Adnan, `segmentation_query` is *not* working fine, because it's generating a *different, invalid query*. The problem is not that the same identical SQL query fails when running in the console, the problem is that you're generating an invalid query. – Tom Lord Mar 23 '21 at 18:52
  • 1
    As well as that method being extremely complicated/error prone/hard to understand, it also contains a fatal security flaw: A malicious user can execute arbitrary/malicious SQL commands on your database via carefully constructed URL parameters!! – Tom Lord Mar 23 '21 at 18:56
  • 1
    Instead of trying to construct the whole SQL command yourself like that, I would suggest perhaps **building two SQL commands** in the "normal" way (using `CustomAttributeValue.where(...)`, and then intersecting them something like [this](https://stackoverflow.com/a/18489693/1954610). – Tom Lord Mar 23 '21 at 18:58

0 Answers0