8

I am using the following query to find nearest locations google maps. Is it immune to the Sql injection. If not can anyone help me to get rid of it.

AlphaCourses.find_by_sql("SELECT *,(  6371 * acos( cos( radians( #{@latitude} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( #{@longitude} ) ) + sin( radians( #{@latitude} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM alpha_courses HAVING distance <= #{@radius} ORDER BY distance LIMIT 200")

Thanks in advance.

kiddorails
  • 12,961
  • 2
  • 32
  • 41
P J S
  • 170
  • 2
  • 16
  • Have a look at the second example here - http://apidock.com/rails/ActiveRecord/Querying/find_by_sql . Avoid interpolating the attributes directly. – kiddorails May 20 '13 at 07:15
  • Thanks for your replay I tried like this AlphaCourses.find_by_sql("SELECT *,( 6371 * acos( cos( radians( ? ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( ? ) ) + sin( radians( ? ) ) * sin( radians( latitude ) ) ) ) AS distance FROM alpha_courses HAVING distance <= #{@radius} ORDER BY distance LIMIT 200","#{@latitude}","#{@longitude}","#{@latitude}") but it shows some syntax error like (wrong no.of argumrnts 4 for 2) – P J S May 20 '13 at 07:33
  • See this syntax carefully - `Post.find_by_sql(["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date])`. Everything inside is in an array; you replace the model's fields by `?` and then sequentially write them after comma - here - `author_id`, `start_date` – kiddorails May 20 '13 at 08:46

1 Answers1

11

From: http://guides.rubyonrails.org/active_record_querying.html#pure-string-conditions

This code 

....("orders_count = ?", params[:orders])

is highly preferable to this code:

....("orders_count = #{params[:orders]}")

because of argument safety. Putting the variable directly into the conditions string will pass the variable to the database as-is. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out he or she can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string.

Apply this to your example!

bjelli
  • 9,752
  • 4
  • 35
  • 50
  • But I tried in many ways use to use where clause but I failed to use. can you please apply where to my query @bjelli – P J S May 20 '13 at 08:38
  • it's not the where-clause that's important (I edited that out). what's important is using ? in your sql string. – bjelli May 20 '13 at 09:02
  • [This](https://stackoverflow.com/a/17861739/5783745) answer may also be useful – stevec Jan 27 '21 at 14:53