April 9, 2013

How to avoid SQL Injection in Rails

SQL injection is any situation in which a user can manipulate a database query in an unintended manner. Consequences of SQL injection vulnerabilities range from data leaks, to authentication bypass, to root access on a database server.

Most Rails applications interact with a database through ActiveRecord, the default and convenient Object Relational Mapping (ORM) layer which comes with Rails. Generally, use of ORMs is safer. ORM can provide abstraction and safety and allow developers to avoid manually building SQL queries. ORM can incorporate best practices and prevent loosely handling of user input.

sqlquery = "SELECT * FROM users WHERE name = '#{name}' AND password = '#{password'} LIMIT 1"
results = DB.execute(sqlquery)

Safer, simpler code like
User.where(:name => name, :password => :password).first

Rails framework will protect them as long as they avoid the "obviously dangerous" methods, like find_by_sql.
ActiveRecord does provide parametrization of queries or some methods. But for some methods it does not provide parametrization of sql queries, these methods are not intended to be used with user input.
Here is an example of using exists?
User.exists? params[:user_id]
However, there is no guarantee params[:user_id] is a string.
Hacker could send a request with ?user_id[]=some_hack_string, which Rails will turn into an array ['some_hack_string']. Now the argument is an array, the first element of which is not escaped.

To avoid this problem, we need to convert the user input to the expected type:
User.exists? :id => params[:user_id]
User.exists? params[:user_id].to_i
This should be the approach for all uses of user input.
Post a Comment