Friday, November 18, 2011

ActiveRecord generate sql query on ruby console

Most of the times I want to see the active record generated sql queries in console and specially when I am working on ruby console.
I have try to search on google and I found some code like
ActiveRecord::Base.logger =

This code we need to place in application.rb, as this code is set all active_record loggor activity for 'STDOUT'.

But this code was recomanded for rails-3. It may not work on rails 2x.

To view sql query in ruby console for rails 2x we need to run following code on console.
require 'logger'
if ENV.include?('RAILS_ENV')&&
   ActiveRecord::Base.logger =

Or we can create .irbrc file and put that code in this file, So we do not need to run above commands every time.

Now on ruby console we can view generated sql query against 'ActiveRecord' methods.

Saturday, November 12, 2011

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

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.