SQL Injection in rails

In this section I am discussing one of the common security issue where attackers can use sql injection to get the details or secure data from our application.

What is SQL Injection?

SQL Injection is a vulnerability in web applications in which user input is not properly sanitized and characters special to SQL (such as the ' quote character) are allow to pass unfiltered in an SQL query.

This allows attackers to execute arbitrary SQL queries and possibly obtain sensitive information that could lead to full compromise of the application. SQL injection can be avoided by properly sanitizing all user input before using in an SQL query.

How to prevent SQL Injection?

The classis example of sql injection is Bypassing Authorization, to find the first record in the users table which matches the login credentials parameters supplied by the user.

User.first("login = '#{params[:name]}' AND password = '#{params[:password]}'")

If an attacker enters ' OR '1'='1 as the login/password or any of the true condition with OR operator the resulting SQL query will be:
SELECT * FROM users WHERE login = '' OR '1'='1' AND password = '' OR '2'>'1' LIMIT 1


OR

SELECT * FROM users WHERE login = 'xxxxx' AND password = '' OR true LIMIT 1

Proper way
User.find(:first, :conditions => ["login = ? AND password = ?", params[:user_name], params[:password])


After getting unauthorized access attackers may read arbitrary data from the database. The following example opens a door to the complete database and an attacker is able to read the user table by extending the input with SQL commands.

Article.find(:all, :conditions => "author = '#{params[:author]}'")

Now if an attacker input ') UNION SELECT id,login AS author,password AS title,1 FROM users

So the generated sql-query will be
SELECT * FROM articles WHERE (author = '') UNION SELECT id,login AS author,password AS title,1 FROM users -- ')


Any way Ruby on Rails has a built-in filter for special SQL characters, which will escape ' , " , NULL character and line breaks. Using Model.find(id) or Model.find_by_some thing(something) automatically applies this countermeasure.

But in SQL fragments, especially in conditions fragments (:conditions => "..."), the connection.execute() or Model.find_by_sql() methods, it has to be applied manually.

If the conditions parameter is needed, then make sure to use 'sanitize_sql_for_conditions()' or 'sanitize_sql()'

SQL injection issue in :limit and :offset parameter(Only for rails < 2.1.1)

vulnerable code:
User.find(:all, :conditions => ["name = ?", params[:name]], :limit => params[:limit], :offset => params[:offset])


attackers may entered value for params[:limit] OR params[:offset] is set to '; DROP TABLE users;' you know what it will do...

It is to good think about application security.