I want to show you a snippet of code that looks perfectly innocent. I've seen this exact pattern in junior devs' PRs and even in some legacy codebases I've inherited. It's a simple search feature where we let the user look up a profile by their username.

def show
  @user = User.where("username = '#{params[:username]}'").first
end

At first glance, it works. You type "alice" into the search box, ActiveRecord generates SELECT * FROM users WHERE username = 'alice', and you get your user. But there is a massive hole here. Because we are using Ruby string interpolation (the #{...} syntax) inside the query string, we are trusting the user to give us a "clean" string. In the real world, users are not always clean.

The "Always True" Exploit

Imagine a malicious user doesn't type a name. Instead, they type this into your search field: ' OR 1=1 --. Let's look at what happens to the SQL query when that hits your controller:

# The resulting SQL becomes:
SELECT * FROM users WHERE username = '' OR 1=1 --'

The ' closes the first quote, OR 1=1 makes the entire WHERE clause true for every single row in the database, and the -- tells SQL to ignore everything that follows it as a comment. Suddenly, your .first call isn't returning the searched user—it's returning the very first user in your database, which is often the Administrator. You've just handed over an admin session to a stranger.

Using Parameterized Queries

The fix is simple, but it requires a shift in how you think about your queries. You should never build a SQL string yourself. Instead, you tell ActiveRecord where the "holes" are, and you let the database driver handle the escaping. This is called parameterization.

The most common way to do this is using a hash, which ActiveRecord handles safely by default:

# Safe and idiomatic
@user = User.where(username: params[:username]).first

If you have a more complex query where a hash doesn't fit—say, you're using a LIKE clause—you can use the array syntax with placeholders (the ? mark):

# Safe way to handle fragments
@user = User.where("username LIKE ?", "%#{params[:username]}%").first

When you use the ?, ActiveRecord ensures that any single quotes or semicolons in the params[:username] are properly escaped before they ever touch the database. The input is treated as a literal string, not as executable SQL code.

Handling Dynamic Column Names

Now, here is where things get tricky. Sometimes you want the user to choose which column to search (e.g., searching by "email" OR "username"). You might be tempted to do this:

# DANGEROUS: You cannot parameterize column names!
User.where("#{params[:column]} = ?", params[:value])

Placeholders only work for values, not for table or column names. If you interpolate params[:column], you're right back to square one with SQL injection. To fix this, you need an "Allow List." You explicitly define which columns are safe to query and reject everything else.

def search
  # Define exactly what is allowed
  allowed_columns = ["username", "email", "display_name"]
  column = params[:column]

  if allowed_columns.include?(column)
    @results = User.where("#{column} = ?", params[:value])
  else
    # Handle the error or default to a safe column
    @results = User.none
  end
end

By checking the input against a hardcoded list, you ensure that the only strings being interpolated into your SQL are ones that you, the developer, wrote.

📋 Practical Task

Exercise: Hardening the Product Filter

You have been handed a legacy ProductController that is dangerously vulnerable to SQL injection. The current implementation allows users to filter products by a category and a minimum price, but it does so using string interpolation.

Your Task: Rewrite the index method in the controller to eliminate all SQL injection vulnerabilities. You must use parameterized queries for the values and implement an allow-list for the sort_by parameter to ensure users can only sort by "price", "created_at", or "name".

class ProductController < ApplicationController
  def index
    # VULNERABLE CODE:
    # This is the code you need to fix.
    @products = Product.where("category = '#{params[:category]}' AND price >= #{params[:min_price]}")
                       .order("#{params[:sort_by]} DESC")
  end
end

Ensure your solution handles the case where params[:sort_by] is missing or contains an invalid column name by defaulting to "created_at".