Ruby Based SQL Injection

Hi Readers,

I sat to pen down regarding SQL Injection. Being the topmost finding in OWASP’s top 10, the definition and description of SQL Injection and many exercises for hands-on are available online, one being PORT SWIGGGER LABS. I’ve come across SQL injection in a RUBY ON RAILS code which made me share few points regarding it. Let’s dive into the code snippet.

Sample Snippet

def update
message = falseuser = User.where("id = '#{params[:user][:id]}'")[0]if user
user.update(user_params_without_password)
if params[:user][:password].present? && (params[:user][:password] == params[:user][:password_confirmation])
user.password = params[:user][:password]
end
message = true if user.save!
respond_to do |format|
format.html { redirect_to user_account_settings_path(user_id: current_user.id) }
format.json { render json: {msg: message ? "success" : "false"} }
end
else
flash[:error] = "Could not update user!"
redirect_to user_account_settings_path(user_id: current_user.id)
end

In the above code, it’s clear that the User is trying to update the details.

Let’s interpret this highlighted piece of code to a sample query as below:

 select * from users where email= ' ' OR 1 --';

The comments after that email ignore everything after it. So the query returns all records from the user’s table. This is because the condition is true for all records.

In the highlighted line, an unintended user(with malicious intent) gains full control of the email parameter (e.g. say via params[: user][: id]), through which he’ll be able to insert anything they choose into the application’s where clause.

eg: http://vulnsite.com/query?email=testmail@victim.com ‘) or 1=1 —

As you can see above, the attacker sends a payload of ‘) or 1=1 — .

This is how it works:

Sending the ‘) part of the payload will break the query so that it returns no results; email returns as blank: email=”. In the second part, 1=1 will always equal true, resulting in the returning of the first entry in the table of users. Finally, — part is an SQL comment. Through this, it’ll ignore any changes present after that. So, the query bypass thing worked.

# Vulnerable Line:User.where(“id = ‘#{params[:user][:id]}’”)[0]

Remediation

SQL statements are often dynamically built. A user provides some input and this input is built into the statement. A developer should be cautious while dealing with input from a user.

The recommended way to dynamically build SQL statements is to use PARAMETER BINDING.

Binding parameters means creating placeholders in the statement. The placeholder is a special mark(question mark or a colon) in the SQL statement.
Later a parameter is bound to the placeholder with a bind_param, exec_query, find_by_sql, etc. methods.

# Safe
User.where("id: '{params[:user][:id]}'")[0]
User.where("id = '?", params[:user][:id]'").[0]
User.where("id = :params[:user][:id]", id: params[:user][:id]).[0]

Ruby has built-in features to filter out SQL injection. 

For more details https://guides.rubyonrails.org/security.html#sql-injection-countermeasures

Seems a Simple one, But a kind of different implementation.

That’s it for this blog guys..! Stay curious to learn and Keep Learning..!!

Please feel free to comment