1

I've got a postgresql query using some ActiveRecord from Ruby On Rails

my_table.location = ActiveRecord::Base.connection.execute("UPDATE my_table SET location = ST_SetSRID(ST_MakePoint(my_table.longitude, my_table.latitude), 4326)::geography")

I want to put variables into my query like that because I don't want to use the column longitude and latitude from the database but rather some variables containing longitude and latitude values.

my_table.location = ActiveRecord::Base.connection.execute("UPDATE my_table SET location = ST_SetSRID(ST_MakePoint((?), (?), 4326)::geography"), longitude, latitude)

I can't find a way to make it work but you should understand the idea behind. Does it exist some method to use variables in my query ?

Ilya
  • 13,337
  • 5
  • 37
  • 53
Monsieur
  • 97
  • 1
  • 11
  • tell me did it work? – Aniket Tiwari May 17 '16 at 12:26
  • 1
    Possible duplicate of [How to execute a raw update sql with dynamic binding in rails](http://stackoverflow.com/questions/4483049/how-to-execute-a-raw-update-sql-with-dynamic-binding-in-rails) – max May 17 '16 at 12:54
  • Well it worked partially, when my longitude and latitude values are nil it displays this error : `ERROR: invalid input syntax for type double precision: "" (ActiveRecord::StatementInvalid)` – Monsieur May 17 '16 at 13:45

2 Answers2

1

Yes, you can easily do it. Make sure to sanitize the query before passing it to the database. I have here used the method quote. Also, read the comment by KNejad for more detail

 conn = ActiveRecord::Base.connection
 @from = conn.quote(params["from"))
 @to = conn.quote(params["to"))

 
 @from_date = Time.parse(@from).to_date.beginning_of_day
 @till_date = Time.parse(@to).to_date.end_of_day
          
 sql = "select abc.sss,im.lat,
 im.long from  users as rq JOIN  images as 
 im ON rq.id= im.imageable_id where  rq.created_at  
 BETWEEN '#{@from_date}' AND '#{@till_date}' ;" 
  
 response = ActiveRecord::Base.connection.execute(sql)
Aniket Tiwari
  • 3,561
  • 4
  • 21
  • 61
  • Are you using the same thing as suggested below ? – Monsieur May 17 '16 at 12:18
  • did you understand how to use variable in your sql query i have shown you one of my example – Aniket Tiwari May 17 '16 at 12:19
  • '#{longitude}' you can use this if it doesn't work then use #{longitude} – Aniket Tiwari May 17 '16 at 12:20
  • Yeah thanks, i think i understood well, trying to adjust to my case, will mark as answered right after. – Monsieur May 17 '16 at 12:34
  • 3
    This works fine in this case, but you should note that if the @from_date is coming directly from the user, this is susceptible to an SQL Injection attack. So you should always make sure to sanitise the data before passing it to the database (e.g. by parsing it into a date object, like this answer is doing) – KNejad Jul 21 '20 at 12:36
  • @KNejad I have updated my answer as per your suggestion – Aniket Tiwari Aug 18 '20 at 04:08
  • @AniketTiwari your answer was actually fine the way it was because Time.parse will always return a string without any SQL. In other words it handles the sanitisation. Your new code is not valid though (e.g. you aren't closing the square brackets on the @from and @to lines). My comment was mostly to warn others that you shouldn't pass user params directly to the database. For example if they decided to use `@from_date = params[:from_date]` then it would be susceptible to an SQL injection attack – KNejad Aug 18 '20 at 08:45
  • @KNejad Thanks for the suggestion I have updated the syntax error now – Aniket Tiwari Aug 18 '20 at 09:03
0

Here's a solution for your use case, I believe checking for longitude.present? and latitude.present? is required:

my_table.location = ActiveRecord::Base.connection.execute("UPDATE my_table SET location = ST_SetSRID(ST_MakePoint(#{longitude}, #{latitude}, 4326)::geography")) if longitude.present? and latitude.present?
webaholik
  • 1,619
  • 1
  • 19
  • 30