Injection attacks are at the top of the OWASP Web Application Security Risks list. SQL injection specifically is very popular among attackers. It gives them the ability to steal all your database data as well as delete it. That's why SQL injection prevention should always be taken seriously.
Developers sometimes assume that by using a well-established web application framework like Rails, they're automatically safe. But that's not always the case. In this post, you'll see examples of vulnerable Ruby on Rails application code, and you'll learn how to secure your application against SQL injection.
What Is SQL Injection?
Let's start with the basics. In order to learn how to prevent SQL injection, you first need to understand how this attack works. So what does it mean to inject some SQL? (Almost) every modern application uses a database to store data. All the users' accounts (together with their passwords) are stored in such a database. Your application will continuously talk to the database in order to read some records, save new ones, or change existing ones. Therefore, it's normal for the application to execute all kinds of SQL queries. If you, for example, open "my orders" on your favorite online shopping website, that website will contact its database to get all the orders made by your user ID in order to present them to you. So it will have to execute a SQL query similar to this one:
SELECT * FROM orders WHERE user_id = '29361'Of course, from your user account, you should only be able to see your own orders and not orders from other users. If you were able to somehow force the application to change user_id in the above query, you'd be able to get the orders of other users. And that's what we call a SQL injection attack: an ability to modify the SQL query that an application is executing against the database. If you want to learn more about the attack itself, read our post, What is SQL Injection? Let's dive in to SQL injections, specifically in the case of the Rails framework.
SQL Injection vs. Rails
In the case of Ruby on Rails applications, you most probably won't be writing raw SQL queries at all. Rails comes with a library called "Active Records," which provides simple methods that can be used to interact with a database. So are these methods vulnerable to SQL injection? Well, most of them aren't, but some are. This means that you should always pay attention to how you're constructing database queries in Ruby on Rails. Let's see some examples first where Rails won't save you from SQL injection.
delete_all
We'll start with the scariest one. You should never use user input directly when using the delete_all method. If you pass a string to delete_all, that string won't be escaped at all. So, for example, if you let the user delete their account and you use the delete_all method without proper input parameterization to do so, you're exposing yourself to SQL injection. See this example:
User.delete_all("id = #{params[:user_id]}")With a query constructed like this, the user will be able to manipulate the user_id parameter and send a malicious SQL code instead, resulting in deleting all users from your database. How's that possible? Normal SQL query (when real user_id is passed as a parameter) constructed from the above code would look like this:
DELETE * FROM 'users' WHERE (user_id = 972983)However, just by changing the user_id to be something like "972983) OR 1=1--", the executed query will look like this:
DELETE * FROM "users" WHERE (user_id = 972983) OR 1=1--)This results in the WHERE clause being always true (OR 1=1), which means that all users will be deleted from your database. Scary, huh?
from
While developers are usually careful with delete_all methods, the very commonly used method from is also vulnerable to SQL injection. Imagine the following code:
Users.from(params[:from]).where(managed: true)You have functionality on your website that allows users to filter results by a specific parameter (for example, "show me book authors" or "show me book reviewers"). Normally you'd expect the value of the parameter :from to be, for example, "authors" or "reviewers," and the results should be filtered to only return users who are "managed" by current_user. Doing this that way, however, you again open the possibility to manipulate the :from parameter and send a completely different query than you expected. For example, your user can send the following string as a parameter:
users WHERE admin = 't' OR 1=?;Resulting in the following query to be executed against the database:
SELECT "users".* FROM users WHERE admin = 't' OR 1=?; WHERE "users"."managed" = ?group
Another very widely used method suffers from the same issue. If the user input is used directly as a method argument, it will be vulnerable to SQL injection:
Order.where(:user_id => current_user.id).group(params[:group])A hacker can send the "group" parameter as "name UNION SELECT * FROM orders", resulting in getting orders from all users, not only their own:
SELECT "orders".* FROM "orders" WHERE "orders"."user_id" = ? GROUP BY name UNION SELECT * FROM ordersHow to Prevent SQL Injection
You've seen a few examples already, so let's now look at how to prevent SQL injection from happening. The most important rule is to never trust the user input and never use it directly to construct the SQL query. You should always serialize or parameterize values from the user. Also, try to avoid passing strings as parameters to Active Records methods. Use arrays or hashes instead. So to fix this vulnerable code, avoid the following:
User.where("email = '#{params[:email]'")And instead of using user input directly, you should pass it as a parameter:
User.where(["email = ?", "#{params[:email]}"])This approach can be applied to pretty much all methods and therefore help you avoid almost all SQL injection vulnerabilities.
Another countermeasure that you can apply is to use attribute-based finder methods whenever possible. By doing so, Active Records will automatically properly escape unwanted characters, which normally allow SQL injection to happen. Something like this would be just asking for an SQL Injection vulnerability:
Order.find_by("id = '#{params[:order_id]'")But also, avoid searching like here below:
Order.find_by(id: params[:order_id])The above query is actually SQL Injection safe, so why we don’t recommend it then? Because, as shown in the first example, using find_by is not safe by default, therefore you always need to pay attention if the way you use it is secure or not.
So, instead of always trying to remember the secured syntax it’s easier to use the attribute-based equivalent instead:
Order.find_by_id(order_id)In general, you should always try to parameterize or serialize user input before using it to do anything on the back-end side. This will help you prevent not only SQL but also other types of injection attacks. Remember that SQL vulnerability is extremely dangerous as it allows an attacker to do damage on many levels. Not only deleting data in your database but also stealing private user data and passwords. Preventing SQL injection, as shown above, only requires changing a few lines of code so there's no excuse for not doing that.
Summary
As you can see, Ruby on Rails, by default, won't save you from all SQL injection attack attempts. Some of the methods of its built-in library, Active Records, prevent these types of attacks automatically, but others don't. However, it's not that difficult to make your application secure.
Just remember the golden rule of never using user input directly as an argument to Active Records methods. This simple change will help you avoid data leaks or losing database data due to a malicious SQL sent by a hacker. It doesn't solve all the potential attack vectors, so in order to be 100% sure, you should understand how every method is working. And remember to read our post here that covers in more detail how SQL injection works. This will help you better understand the attack itself. And that will help you think about possible attack vectors when writing code.
This post was written by Dawid Ziolkowski. Dawid has 10 years of experience as a Network/System Engineer at the beginning, DevOps in between, Cloud Native Engineer recently. He’s worked for an IT outsourcing company, a research institute, telco, a hosting company, and a consultancy company, so he’s gathered a lot of knowledge from different perspectives. Nowadays he’s helping companies move to cloud and/or redesign their infrastructure for a more Cloud Native approach.
