sqlruby-on-rails-3sql-injectionsanitizationfind-by-sql

How to Sanitize the SQL in Rails?

I'm new to this RoR world,

I've many SELECT sql queries in my RoR Application, something like this

@replies  = Offerreply.find_by_sql ("SELECT * FROM offerreplies WHERE 
offer_id="+params [:offer_id])

Some are very simple like above and some are very complex JOINS. most of them are suffering from SQL Injection problem. So., How to Sanitize such SQL statements in RoR?

Edit: How to Handle same in SQL statements which has JOINS and Sub-queries? something like this

@to_be_approved=Beneficiary.find_by_sql("SELECT * FROM beneficiaries WHERE project_id="+params[:id]+" AND NOT id IN (SELECT beneficiaries.id FROM beneficiaries INNER JOIN beneficiaryloans ON beneficiaryloans.beneficiary_id=beneficiaries.id AND beneficiaryloans.hfi_id="+session[:id].to_s+" AND beneficiaries.status_id=4) AND cso_id IN(SELECT user_id FROM user_projects INNER JOIN users ON  user_projects.user_id=users.id AND users.user_type_id=2)")
Solution

  • If you're using Rails 3 (as your tag says), you can do it like this.

    @replies  = Offerreply.where("offer_id = ?", params[:offer_id])

    You can find more information at the Rails site.

    edit: If you have more than one condition, you can do it like this.

    @replies  = Offerreply.where("offer_id = ? AND second = ?", params[:offer_id], params[:second])

    edit2: And see Micha's answer for multiple joins.