Is it possible to put variables in my query that do not use string interpolation?

advertisements

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  )