SQL Injection in Rails 5 Apps

Security Mantra

Do not trust user input. Don't trust params or cookies.

Steps

Step 1

The tasks index page in the app/views/tasks/index.html.erb lists all tasks.

<%= form_tag(tasks_path, method: :get) do %>
    <%= text_field_tag :term %>
    <%= submit_tag 'Search' %>
<% end %>
<h1>Tasks</h1>
<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Complete</th>
      <th colspan="3"></th>
    </tr>
  </thead>
  <tbody>
    <% @tasks.each do |task| %>
      <tr>
        <td><%= task.name %> in <%= task.project.name %></td>
        <td><%= task.complete %></td>
        <td><%= link_to 'Show', task %></td>
        <td><%= link_to 'Edit', edit_task_path(task) %></td>
        <td><%= link_to 'Destroy', task, method: :delete, data: { confirm: 'Are you sure?' } %></td>
      </tr>
    <% end %>
  </tbody>
</table>
<%= link_to 'New Task', new_task_path %>

It has a search field that allows searching for tasks. The form_tag uses POST by default, so we over-ride it to GET. Because we need to display the projects index page as the search results page.

Step 2

Populate the database with some seed data. In seeds.rb:

wealthy = Project.create(name: 'Wealth Building')
wealthy.tasks.create(name: 'Get rich quick', complete: false, priority: 4)
wealthy.tasks.create(name: 'Write a book', complete: true, priority: 5)

happy = Project.create(name: 'Be Happy')
happy.tasks.create(name: 'Buy a puppy', complete: false, priority: 9)
happy.tasks.create(name: 'Dance in the rain', complete: true, priority: 7)
happy.tasks.create(name: 'Dance in the snow', complete: true, priority: 6)
happy.tasks.create(name: 'Walk on water', complete: true, priority: 7)
happy.tasks.create(name: 'Just Blog It', complete: true, priority: 5)
happy.tasks.create(name: 'Play tennis', complete: true, priority: 4)
happy.tasks.create(name: 'Make fun of Donald Trump', complete: true, priority: 3)
happy.tasks.create(name: 'Walk on moon', complete: true, priority: 2)

Run

rake db:seed

Step 3

Let's search for Dance. It displays all the tasks that begin with the search term 'Dance'. The index method is like this:

def index
  @tasks = Task.where("name LIKE '%#{params[:term]}%'")
end

You can see the generated SQL query in the development.log file:

Started GET "/tasks?utf8=%E2%9C%93&term=Dance&commit=Search" for ::1 at 2016-03-16 17:08:40 -0700
Processing by TasksController#index as HTML
  Parameters: {"utf8"=>"✓", "term"=>"Dance", "commit"=>"Search"}
  Task Load (0.3ms)  SELECT "tasks".* FROM "tasks" WHERE (name LIKE '%Dance%')
  Project Load (0.1ms)  SELECT  "projects".* FROM "projects" WHERE "projects"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  CACHE (0.0ms)  SELECT  "projects".* FROM "projects" WHERE "projects"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
  Rendered tasks/index.html.erb within layouts/application (14.6ms)
Completed 200 OK in 46ms (Views: 38.5ms | ActiveRecord: 1.6ms)

The database calls takes user input to form the database query. This is susceptible to SQL injection.

Step 4

If you have a quote in the search term: Dance ' delete table. The browser throws the ActiveRecord::StatementInvalid error.

Started GET "/tasks?utf8=%E2%9C%93&term=Dance+%27delete+table&commit=Search" for ::1 at 2016-03-16 17:09:46 -0700
Processing by TasksController#index as HTML
  Parameters: {"utf8"=>"✓", "term"=>"Dance 'delete table", "commit"=>"Search"}
  Task Load (0.4ms)  SELECT "tasks".* FROM "tasks" WHERE (name LIKE '%Dance 'delete table%')
  Rendered tasks/index.html.erb within layouts/application (2.9ms)
Completed 500 Internal Server Error in 7ms (ActiveRecord: 0.4ms)

ActionView::Template::Error (SQLite3::SQLException: near "delete": syntax error: SELECT "tasks".* FROM "tasks" WHERE (name LIKE '%Dance 'delete table%')):

Step 5

Use question mark character ? to bind user input in params[:term]. Rails will escape the user input for us. Change the tasks index method:

def index
  @tasks = Task.where("name LIKE ?", "%#{params[:term]}%")  
end

In order to allow search and list all tasks on the same page, we need a conditional in the index action.

def index
  if params[:term]
    q = "%#{params[:term]}%"
    @tasks = Task.where("name LIKE ?", q )
  else
    @tasks = Task.all
  end
end

Step 6

If you have single quote in the search term: Dance ' delete table. The search term does not have the single quote anymore in the generated SQL. It has been replaced with a blank string. You can see that in the log file

Started GET "/tasks?utf8=%E2%9C%93&term=Dance+%27delete+table&commit=Search" for ::1 at 2016-03-16 17:41:23 -0700
Processing by TasksController#index as HTML
  Parameters: {"utf8"=>"✓", "term"=>"Dance 'delete table", "commit"=>"Search"}
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks" WHERE (name LIKE '%Dance ''delete table%')
  Rendered tasks/index.html.erb within layouts/application (1.4ms)
Completed 200 OK in 32ms (Views: 30.4ms | ActiveRecord: 0.1ms)

We are using SQLite. So the quote is escaped by using two single quotes.

Summary

In this article, you learned how to avoid SQL injection in Rails 5 apps.


Related Articles


Create your own user feedback survey