SQL injection in Rails - Learn from an attacker’s shoes

Imagine a scenario where an attacker gains unauthorized access to your application's database, manipulates data, bypasses authentication measures, or even executes malicious code on the server. Sounds like a nightmare, right? This is the harsh reality of SQL injection, a security vulnerability that can wreak havoc on any application. SQL injection, or SQLi, typically occurs when unescaped user data is passed to an SQL query, opening the door for attackers to exploit the system. This guide will guide you through the intricacies of SQL injection in Rails applications, helping you understand the vulnerability itself and how to prevent such a disastrous scenario.

It is important to note that SQL injection is a preventable vulnerability, and developers should take care to ensure that their code is secure and free from vulnerabilities. Regular security audits and testing can also help to identify and address vulnerabilities before they are exploited by attackers.

This is Part 2 of my Rails security series Rails Security Best Practices: Protecting Your Applications from Common Threats

Understanding SQL Injection

SQL injection can happen when a web application uses input fields to construct SQL queries without properly sanitizing the input. If an attacker can manipulate these queries, they can potentially view, modify, or delete data from the database. This is because we are constructing SQL queries by using another language. Let me explain with an example.

This is a classic login bypass example, which is easy to understand. The following query uses user supplied username and password to check if they match, so that the app can proceed to login the user.

User.where("username = '#{params[:username]}' AND password = '#{params[:password]}'")

If we give username as “admin” and password as “password”. It will generate the following SQL query.

SELECT * FROM users WHERE (username = 'admin' AND password = 'password');

But instead, If an attacker enters ' OR '1'='1'); -- as the username and leaves the password field blank, the SQL query becomes:

SELECT * FROM users WHERE (username = '' OR '1'='1'); -- AND password = '');

Since '1'='1' is always true, this query will include all usernames. And since -- is for used for commenting in SQL, the entire password part will get commented out, effectively bypassing the login mechanism.

Another way to do it is by passing the same ' OR '1'='1' as both username and password. It will work similarly.

I am not going to build suspense on how to fix it. It can be simply fixed by making sure the values are properly sanitized. It can be done in two ways:

1. Pass params as hash

User.where(username: params[:username], password: params[:password])

1. Use prepared query

User.where("email = ? AND password = ?", params[:email], params[:password])

This way, Rails will make sure your data is passed properly.

Is it that simple?

Yes, Rails makes it so easy to handle this. That's right!. But I have few more things to discuss.

Be careful of the injection vector

Injection vector is the specific location in the code where user-supplied input is improperly included or used in a command or query that is then executed or processed. Since we are using Rails, This is not only in where part of queries. In Rails it is possible in many other methods. Basically any method that accepts raw SQL as input is vulnerable.

Here are two examples. This is nowhere near a complete list. but serves as an example for different class exploitation.

Example 1: group method

If your query includes a group method and you pass a user submitted value to it. There could be this vulnerability. Lets consider this query,

User.where(subscribed: true).group(params[:group])

if we pass a proper parameter, such as name the generated SQL query will be similar to,

SELECT "users".* FROM "users" WHERE "users"."subscribed" = 1  GROUP BY "users"."email";

resulting in a proper group by statement in the query.

But, if an attacker sends the value of params[:group] as name UNION SELECT * FROM users, the generated query will become

SELECT "users".* FROM "users" WHERE "users"."subscribed" = 1  GROUP BY email UNION SELECT * FROM users;

As you can see the "users.name" part is no longer present and instead it generated an sql query to return all users.

Other methods similar to group

# .join
# .lock
# .select

Example 2. calculate method

You might be familiar with methods like sum, maximum etc. All these methods are shortcuts to calculate method.

User.where(admin: true).calculate(:sum, params[:column]) # is the same as User.sum(params[:column])

Suppose income is a valid column in the table. If a malicious user passes the value of params[:column] as income) from users where 'random_string' != ? --; they will be able to get the sum of income of all users.

Let me explain how:

1. -- is comment in sql. Here we are using sql injection to set all remaining parts of the SQL as invalid. and we included from users to address the commented out from part. See the generated SQL

SELECT SUM(income) from users where 'random_string' != ? --;) FROM "users" WHERE "users"."admin" = ?

2.The 'random_string' != ? is a technique to bypass the bind parameter error in Rails prepared queries by giving some random parameter to bind to, but making the result always true. Here it is used to bypass the internal bind parameter in .where(subscribed: true)

So, Be careful of the attack vectors. Any method that can accept raw SQL is really dangerous in Rails.

Misuse of the sanitize Method and Its Consequences

The sanitize method is good for preventing XSS, but it does not prevent SQL injection.

Let’s take the last example. But "protected" with sanitize method.

User.where(subscribed: true).sum(sanitize(params[:column]))

Sanitize method is for stripping HTML tags. So, it will try to strip any html tags. In this case, if the attacker passes income) from users where 'random_string' != ? --;, There are no HTML tags in it. So, it will simply return the string as it is. Hence the attacker is successful at SQL injection.

SQL injection can happen from stored data too

SQL injection happened from stored data is called a “Second order SQL Injection”. Stored or not, Any data from user should be handled carefully.

Let’s assume we have the following query to get payment details. Here, we allow users to provide payment details, which we will store in database. Here payment_org_secret is a unique uuid which will be present in all payments by an organization.

payment_data = Payment.where("payment_org_secret=#{external_payment.external_org_secret}")

Let’s assume if a user stored external_org_secret as ' OR '1'='1. It will return all payment information of all the organizations regardless of how secure the “secret” is.

Therefore, it's always good practice to use prepared queries or a hash-based format in all instances. We are not sure about where our methods will be used in future. especially when it is really easy to protect from this.

payment_data = Payment.where(payment_org_secret: external_payment.external_org_secret)

Passive detection of SQL injection

brakeman is a static code analyzer especially designed for detecting vulnerabilities in Ruby on Rails applications. It scans your codebase and identifies potential security vulnerabilities, including SQL injection attacks. Brakeman can be run as part of your continuous integration pipeline to ensure that your application remains secure throughout its development lifecycle. But aware that it is too proactive, it prefers to have false positives than to have security vulnerabilities in your app.

Additional level of defenses

• Use strong parameters
• Use a separate account with minimum privileges for your web app in the database.
• Properly validate your data.

Conclusion

In conclusion, SQL injection is a serious security vulnerability that can lead to unauthorized access to sensitive data, bypassing of authentication measures, and execution of malicious code on the server. However, it's a preventable vulnerability that can be mitigated with careful coding practices and regular security audits.

When using Rails, it's crucial to avoid incorporating raw SQL into your queries. Methods such as where, group, join, lock, select, and calculate can all be potential injection vectors if not used properly. Even stored data can be a source of SQL injection, emphasizing the importance of treating all user data with caution.

While Rails provides tools to make handling these issues easier, it's ultimately up to developers to use these tools correctly. The sanitize method, for instance, is effective against XSS but not against SQL injection. Therefore, understanding the nuances of these tools and how to use them effectively is key.