Ok so I have an SQL request using string interpolation and running perfectly :
ActiveRecord::Base.connection.execute("UPDATE my_table SET location = ST_SetSRID(ST_MakePoint('#{my_table.longitude}'::numeric, '#{my_table.latitude}'::numeric), 4326)::geography WHERE id=#{id}")
What I want to do is using SQL variables instead of string interpolation (for security reasons (SQL injections)) but I have been into some troubles with my query :
ActiveRecord::Base.connection.execute("UPDATE my_table SET location = ST_SetSRID(ST_MakePoint(':longitude'::numeric, ':latitude'::numeric), 4326)::geography WHERE id=#{id}", longitude: my_table.longitude, latitude: my_table.latitude)
The error I get is :
PG::InvalidTextRepresentation: ERROR: invalid input syntax for type numeric: ":longitude" (ActiveRecord::StatementInvalid)
Is there a way to use SQL variables properly in the last query ?
Try '?' instead of interpolation
ActiveRecord::Base.connection.execute("UPDATE my_table SET location = ST_SetSRID(ST_MakePoint( ? , ? , 4326)::geography WHERE id= ? ", "#{my_table.longitude}::numeric", "#{my_table.latitude}::numeric)", id )
