rails sanitize for subquery

Question

I am trying to sanitize a SQL query with subquery but every time I am getting a syntax error or invalid geometry error. For sanitizing I am using the sanitize_sql_array function.

SQL query

SELECT ST_Distance('SRID=4326;POINT(0 0)'::geometry, subqry.centroid)
FROM 
 (SELECT ST_AsText(ST_Centroid('SRID=4326;MULTIPOINT ( 0 0, 0 0, 0 0, 0 0 )'))::geography as centroid
 ) as subqry;

1st approach

ActiveRecord::Base::sanitize_sql_array(["select ST_Distance('SRID=4326;POINT(? ?)'::geometry, :sub_query", sub_query: "(SELECT ST_Centroid(SRID=4326;'MULTIPOINT ( ? ?, ? ?, ? ?, ? ? )'::geometry) as centroid) as sub_query", min_longitude, min_latitude, min_longitude, min_latitude, min_longitude, max_latitude, max_longitude, min_latitude, max_longitude, max_latitude])

2nd approach

Here I get "SRID=4326; SE" <-- parse error at position 13 within geometry

ActiveRecord::Base::sanitize_sql_array(["ST_Distance('SRID=4326;POINT(? ?)'::geometry, 'SRID=4326; SELECT ST_AsText(ST_Centroid(MULTIPOINT ( ? ?, ? ?, ? ?, ? ? ))) as centroid'::geometry)", min_longitude, min_latitude, min_longitude, min_latitude, min_longitude, max_latitude, max_longitude, min_latitude, max_longitude, max_latitude])

Example of st_centroid & st_distance method in PostGIS

st_centroid

SELECT ST_AsText(ST_Centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'));


st_distance

SELECT ST_Distance(
        'SRID=4326;POINT(-72.1235 42.3521)'::geometry,
        'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
    );

I know little about Ruby/Rails, but is there a way to use *Bound* parameters? Relying on string parsing seems potentially insecure. Just food for thought. — oglester, Jul 23 '21 at 12:39
What are you trying to sanitize? Is it just integers that will be inside of `POINT(? ?)`? edit: never mind i see they are coordinates — ricks, Jul 23 '21 at 12:55
I will pass lat & long inside point. In my question above I have given 0 as sample — Aniket Tiwari, Jul 23 '21 at 12:56
If all the coordinates are decimals or ints you can just validate them before you run the query by checking the class — ricks, Jul 23 '21 at 13:01
I want to validate by the sanitize method as it will parameters will come from the external API — Aniket Tiwari, Jul 23 '21 at 13:02
Are you going to build the query using Active Record? You can build a subquery like this: https://stackoverflow.com/questions/5483407/subqueries-in-activerecord and just pass the coordinates as attributes — ricks, Jul 23 '21 at 13:46

engineersmnky · Accepted Answer · 2021-07-23T17:23:33.497

You can build any query you could ever want with a bit of trickery and a lot of Arel (which will handle the sanitation for you).

WARNING: This may not look pretty but it can be extremely flexible and should produce the desired result. (This simply produces the desired query as shown in the post I do not have any knowledge as to whether or not the desired syntax is correct as I have never used PostGIS)

 class PostGISQuery
   def initialize(srid,points,point)
     @srid = srid
     @points = points 
     @point = point 
   end 

   def build_query
     return @query if @query
     multi_point = Arel::Nodes::NamedFunction.new("MULTIPOINT",[lat_long(@points)])
     centroid = Arel::Nodes::NamedFunction.new('ST_AsText',[
            Arel::Nodes::NamedFunction.new('ST_Centroid',[
                Arel.sql("SRID=#{@srid};#{multi_point.to_sql}")
            ])
         ])
     mgr = Arel::SelectManager.new 
     mgr.project(cast_geometry(centroid).as('centroid'))
     table = Arel::Table.new('subqry')
     answer= Arel::Nodes::NamedFunction.new('ST_Distance',[
        cast_geometry(Arel.sql("SRID=#{@srid};#{Arel::Nodes::NamedFunction.new('POINT',lat_long([@point])).to_sql}")),
        table[:centroid]
     ])
     mgr2 = Arel::SelectManager.new(Arel::Nodes::As.new(mgr,Arel.sql(table.name)))
     @query = mgr2.project(answer.as('answer'))
   end 

   def execute 
     ActiveRecord::Base.connection.exec_query(build_query.to_sql)
   end
   private 
     def cast_geometry(arel)
       Arel::Nodes::NamedFunction.new("CAST",[arel.as('geometry')])
     end

     def lat_long(arr)
       arr.map {|a| Arel.sql(a.join(' '))}
     end
 end

Usage

srid = 4326
points = [[1, 0], [-1, 2], [-1, 3], [-1, 4], [-1, 7], [0, 1], [0, 3], [1, 1], [2, 0], [6, 0], [7, 8], [9, 8], [10, 6]]
point = [-72.1235, 42.3521]
qry = PostGISQuery.new(srid,points,point) 
qry.execute
#=> ActiveRecord::Result

SQL Produced:

SELECT 
  ST_Distance(
    CAST('SRID=4326;POINT(-72.1235 42.3521)' AS geometry), 
    "subqry"."centroid") AS answer 
  FROM (
    SELECT 
      CAST(ST_AsText(
             ST_Centroid(
               'SRID=4326;MULTIPOINT(1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6)'
             )
           ) AS geometry) AS centroid
    ) AS subqry

rails sanitize for subquery

1 Answers1