Ruby on Rails gives you a lot of tools to protect against SQL injection attacks.

Input sanitization is the most important tool for preventing SQL injection in your database. And Active Record automatically does this when you use it correctly. But that's the key, you have to use it correctly.

So here's a guide on how to use Active Record to avoid exposing your database to SQL injection.

A Quick Introduction to SQL Injection

SQL injection vulnerabilities are caused by code that passes any form of user input directly to the database.

Here's a quick example.

Let's say you want to create an account on a new website. This site happens to be vulnerable to SQL injection.

You could set your email to something odd, like this:

hello@example.com';update users set password='password'--

At some point in the future, that website runs a query and passes your email to the database.

That query might look something like this:

SELECT * FROM "users" WHERE email = 'hello@example.com';update users set password='password'--'

Can you guess what this might do?... 🤔

Yeah, it's going to set every single user's password to "password"... not good.

This is why we need to talk about SQL injection.

It's a devastating vulnerability.

Sanitize Inputs to Prevent SQL Injection

Preventing SQL injection is easy.

All you need to do is sanitize user inputs. This means taking any strings that users give you and escaping special characters that you don't want to send directly to the database.

Using the example above hello@example.com';update users set password='password'--...

We want to make sure users cannot pass characters, like single quotes, to terminate the intended input string early.

That's what's letting the rest of their input get interpreted as SQL.

Active Record and Input Sanitization

The best part about using an ORM like Active Record, is that it handles all of this for you.

The caveat is that you have to use it how it was intended.

Writing Active Record Queries Correctly

When we build queries in Active Record, we usually pass conditions in our .where() method.

Active Record supports 3 ways of supplying these conditions. Pure strings, array conditions, and hash conditions. Generally, Active Record will automatically sanitize inputs passed using arrays or hashes.

But pure string conditions are vulnerable to SQL injection.

A Deep Dive into Pure Strings Conditions

A pure string condition is used when you pass SQL directly to the Active Record .where() method.

It will usually look something like this:

Post.where("category = 'books'")

This is vulnerable to SQL injection because there is no input sanitization happening. Rails is not going to escape this string because it would break the underlying SQL instruction.

The above example doesn't pass user input to the database, but we would introduce a vulnerability if we did something like this:

Post.where("category = #{params[:category]}")

In this example, if we pull the params[:category] from the URL, a user could attack our database by adjusting the value in the URL like this:

https://example.com/products?category=books'+OR+1=1--

And that value would be passed to our Active Record query and result in a SQL injection attack.

SELECT * FROM products WHERE category = 'books' OR 1=1--'

This example is probably harmless since the OR 1=1 condition just means our query will expose all rows.

But an attacker could just as easily add a nasty INSERT or UPDATE command to modify our database.

The takeaway is pretty simple...

Never use string interpolation to pass variables to pure string conditions.

When you need to do so, use a different strategy for passing conditions like array conditions or hash conditions.

Array Conditions

Array conditions prevent SQL injection in Active Record.

They work like this.

Like a pure string condition, pass the SQL condition as the first argument to the .where() method, but pass any variables or user input as additional arguments.

The first parameter will still contain a string of SQL, but Active Record will sanitize your additional arguments to prevent SQL injection.

It should look like this:

Post.where("category = ?", params[:category])

Active Record will then replace every ? in your query with the arguments it was given.

You can use this approach with as many arguments as you need.

# Using multiple variables
Post.where("category = ? AND published = ?", params[:category], true)

Active Record will replace each ? with each supplied argument in the order they were given.

Hash Conditions

Active Record also lets you pass your conditions as a hash to avoid writing SQL entirely.

This prevents SQL injection and automatically escapes user input, making it probably the safest way to build queries in Active Record.

Hash conditions look like this:

Post.where(category: params[:category])

We don't write any SQL and the value of params[:category] will be sanitized. We should have nothing to worry about!

Active Record Best Practices

Use hash conditions whenever possible. Active Record sanitizes these inputs and protects you from SQL injection automatically.

There are times when you need to use a SQL operator that Active Record doesn't support (such as the LIKE operator or when comparing dates). These scenarios might prevent you from using hash conditions.

Avoid using pure string conditions though.

And definitely avoid using string interpolation inside these conditions.

If you need to pass a variable or any user input to a query, use array conditions so that Active Record sanitizes that input before giving it to the database.