SQL Injection in rails

How to avoid SQL Injection in rails

SQL injection is a problem that every web developer needs to be aware of when accepting parameters that will during the life of the request be converted into SQL statements. Rails historically has done what it can to mitigate this risk for the developer by providing vehicles for sanitizing parameter inputs at the points when they are being converted for use inside of a SQL statement, however with Rails 3 there are numerous ways to execute a SQL statement against the database and some of these methods are safer than others.
Consider two cases where valid Rails code is vulnerable to SQL injection:

user inputed parameters

params[:query] = "'username'; DROP TABLE EMPLOYEE;"

CASE 1 - find_by_sql

User.find_by_sql("SELECT * FROM users WHERE (name = '#{params[:query]}'")

(Not Good)

generated SQL

SELECT users.* FROM users WHERE (email = 'user@example.com'); DROP TABLE EMPLOYEE; ')

(THIS STATEMENT WILL DROP TABLE EMPLOYEE)

The example above shows how find_by_sql can allow parameters submitted by a user to be directly entered into a SQL statement and how an attacker might use the vulnerability to wreak havoc. These types of find_by_sql statements used to be more commonly used in earlier versions of Rails (1.0 - 2.0) and it was through these statements that the Rails community realized that SQL injection was a problem that needed addressing. Here's another example prominent in the early Rails days:
User.find :first, :conditions => "(name = '#{params[:query]}')" ##(BAD BAD BAD) produces this SQL statement:

generated SQL

SELECT users.* FROM users WHERE (email = 'user@example.com'); DROP TABLE EMPLOYEE;')

(THIS STATEMENT WILL DROP TABLE EMPLOYEE)

The above example shows a common Rails idiom for performing an ActiveRecord query, as with the previous find_by_sql example the find query here is piping the param in directly and generating the exact same tainted SQL.
Fortunately, Rails core decided to make it easier to just do the right thing and provided ways to pass in parameters by using built-in filters that handle special SQL characters, which will escape ’ , " , NULL character and line breaks. Instead of passing in the parameter directly as a raw string, you can pass in an array to sanitize the tainted strings using the built-in filters:
User.find_by_sql(["SELECT * FROM users WHERE (name = ?)", params])

(GOOD)

User.find :first, :conditions => ["(name = '?')", params]

(GOOD)

generated SQL

SELECT * FROM users WHERE (name = 'user\'); DROP TABLE EMPLOYEE; ') (RETURNS NIL)
The distinction in the filtered SQL statement is the escaped single quote right after the t in Robert which prevents the name parameter from terminating and allowing the DROP TABLE EMPLOYEE from being executed since it remains a part of the string parameter. Additionally, Rails also included these built-in filters automatically when AR queries were called from find_by_something or a conditions hash:
User.find_by_name(params[:query])

(GOOD)

generated SQL

SELECT * FROM users WHERE users.name = 'username\'); DROP TABLE EMPLOYEE; ' (RETURNS NIL) User.find :first, :conditions => {:name => params}

(GOOD)

SELECT users.* FROM users WHERE users.name = 'username\'); DROP TABLE EMPLOYEE;' (RETURNS NIL)
Rails 3 introduced AREL, which is another way to perform ActiveRecord queries and with it came as well, another way to make the exact same SQL injection mistakes that are already listed above. However having gotten accustomed to looking for SQL injection vulnerabilities in the AR query formats above you might be lulled into thinking that the new and improved ActiveRecord query methods would just magically handle the tainted strings for you and you'd be dead wrong:
User.where("name = '#{params}'") (BAD) SELECT users.* FROM users WHERE (name = 'username'); DROP TABLE EMPLOYEE; ##') ##(THIS STATEMENT WILL DROP TABLE EMPLOYEE)
The nice thing is that the same fix can also be applied:
User.where(["name = ?", params]) SELECT users.* FROM users WHERE (name = ''username\'); DROP TABLE EMPLOYEE;
(RETURNS NIL)

Rails way web caching and caching

A web cache is a mechanism for the temporary storage (caching) of web documents, such as HTML pages and images, to reduce bandwidth usage, server load, and perceived lag. So that future requests for that data can be served faster.

Rails provides three types of caching techniques by default without the use of any third party plugins.
   
1) Page caching
2) Action caching
3) Fragment caching


1) Page Caching:

Page caching is a Rails mechanism which allows the request for a generated page to be fulfilled by the webserver (i.e. Apache or nginx), without ever having to go through the Rails stack at all. Obviously, this is super-fast. Unfortunately, it can't be applied to every situation (such as pages that need authentication) and since the webserver is literally just serving a file from the filesystem.
   
To enable page caching, you need to use the caches_page method.

class ProductsController < ActionController

  caches_page :index

  def index
    @products = Products.all
  end

  def create
    expire_page :action => :index
  end

end


By default, the page cache directory is set to Rails.public_path (which is usually set to the public folder) and this can be configured by changing the configuration setting config.action_controller.page_cache_directory.

In order to expire this page when a new product is added we could extend our example controller like above.


2) Action Caching:

One of the issues with Page Caching is that you cannot use it for pages that require to restrict access somehow(such as pages that need authentication). This is where Action Caching comes in. Action Caching works like Page Caching except for the fact that the incoming web request does go from the webserver to the Rails stack and Action Pack so that before filters can be run on it before the cache is served. This allows authentication and other restriction to be run while still serving the result of the output from a cached copy.
   
class ProductsController < ActionController

  before_filter :authenticate
  caches_action :index

  def index
    @products = Product.all
  end

  def create
    expire_action :action => :index
  end

end

You can also use :if (or :unless) to pass a Proc that specifies when the action should be cached. Also, you can use :layout => false to cache without layout so that dynamic information in the layout such as logged in user info or the number of items in the cart can be left uncached.

Action Caching and Page Caching runs in an after filter. Thus, invalid requests won't generate spurious cache entries as long as you halt them. Typically, a redirection in some before filter that checks request preconditions does the job.


3) Fragment Caching:

Sometimes you only want to cache a section of a page instead of the entire page. Fragment caching is the answer of this.
Fragment Caching allows a fragment of view logic to be wrapped in a cache block and served out of the cache store when the next request comes in.

As an example, if you wanted to show all the orders placed on your website in real time and didn't want to cache that part of the page, but did want to cache the part of the page which lists all products available, you could use this piece of code:
   

<% Order.find_recent.each do |o| %>
  <%= o.buyer.name %> bought <%= o.product.name %>
<% end %>

<% cache do %>
  All available products:
  <% Product.all.each do |p| %>
    <%= link_to p.name, product_url(p) %>
  <% end %>
<% end %>
   
The cache block in our example will bind to the action that called it and is written out to the same place as the Action Cache, which means that if you want to cache multiple fragments per action, you should provide an action_suffix to the cache call:
   
<% cache(:action => 'recent', :action_suffix => 'all_products') do %>
          All available products: