SQL injection in Rails - Learn from an attacker’s shoes
Tags: rails, security, infosec
Published: 2023-08-04 19:13:16

---
title: SQL injection in Rails - Learn from an attacker’s shoes
published: true
description: 
tags: rails, security, infosec
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2023-08-04 17:05 +0000
---

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](https://dev.to/haseebeqx/rails-security-best-practices-protecting-your-applications-from-common-threats-2aih)

### 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.

```ruby
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.

```sql
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:

```sql
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

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

2. Use prepared query

```ruby
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,

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

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

```sql
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

```sql
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

```ruby
# .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.

```ruby
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

```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.

```ruby
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.

```ruby

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.

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

## Passive detection of SQL injection

[brakeman](https://brakemanscanner.org/docs/quickstart/) 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.