Rails running sql queries with variables

Question

Is it possible to run sql queries with user inputted variables in rails?

Im trying to search through a database for restaurants with certain characteristics they are looking for (ie cuisine, score, zipcode location). Here is my html.erb as a reference.

<form id="frm1" action="form_action.asp">
  Name: <input type="text" name="name" value="Antico Pizza"><br>
  Lower Score: <input type=integer name="LowerScore" value=0>
  Higher Score: <input type="integer" name="HigherScore" value=100><br>
  Zipcode: <input type=integer name="Zipcode" value=30332><br>
            <label for="Cuisine">Cuisine: </label>
            <select name="Cuisine" id="Cuisine">
            <%= @my_cuisines.each do|cuisine|%>
                    <option value=<%= cuisine.cuisine %> onclick="getCuisine()"><%= cuisine.cuisine %></option>
              <% end %>
            </select>
</form> 

<button onclick="myFunction()">Search!</button>

<p id="demo"></p>
<script>
    function myFunction() {
        var x = document.getElementById("frm1");
}

This creates all my options and when I run
var x = document.getElementById("frm1"); in javascript, I'm able to get the values the user inserted for their search.

In my model, I'm trying to create an SQL statement that will take the users inputs and go through the database and collec them.

ie

sql = "select * from restaurant, inspection
                    where restaurant.rid = inspection.rid
                    and cuisine ='#{c}'
                    and totalscore > '#{l}'
                    and totalscore < '#{h}'
                    and zipcode = '#{z}'"

the #{x} is meant to be the users variables (ie c = cuisine, l = lowerScore...). Is there anyway of doing this in rails?

Dan Draper · Accepted Answer · 2015-07-19 08:55:01Z

2

Of course! You can use ActiveRecord's query interface (Arel).

Restaurant.joins(:inspection).where(cuisine: c, zipcode: z, totalscore: l..h)

Note that you will need an association between the Restaurant and Inspection models.

class Restaurant < ActiveRecord::Base
  has_many :inspections, foreign_key: 'rid'
end

class Inspection < ActiveRecord::Base
  belongs_to :restaurant, foreign_key: 'rid'
end

I recommend you read the Rails guides on these topics:

http://guides.rubyonrails.org/association_basics.html http://guides.rubyonrails.org/active_record_querying.html

Also, whatever you do, don't use input from the user in an SQL query without using the ActiveRecord interface. You will open yourself up to an SQL injection attack. See https://en.wikipedia.org/wiki/SQL_injection

answered Jul 19, 2015 at 8:55

Dan Draper

1,13111 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akshay Over a year ago

Thank goodness! I was worried it could not be done cleanly. I'm reading the guides now, but I am wondering how the users inputs would be stored? How is the html.erb page's user inputs being pushed into variables?

Dan Draper Over a year ago

Just answered this here too: stackoverflow.com/questions/31500096/…

Dan Draper Over a year ago

If the answer works for you would you mind accepting it?

Collectives™ on Stack Overflow

Rails running sql queries with variables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related