Back

Parameterizing raw SQL Queries in Rails

May 16, 2020

For many applications, the ActiveRecord abstraction provided by Rails gives us a convenient and simple method of performing database operations in Rails.

However, in cases where performance is a concern, nothing beats raw SQL. This presents a problem though, as raw SQL is vulnerable to SQL injection attacks, particularly in cases where user input is being used. Consider this example:

select
items.id as ItemId,
items.name as ItemName,
items.price as ItemPrice
from items
where items.id = 12
view raw 1.sql hosted with ❤ by GitHub

This is simple a simple enough query, calling for a few columns in the items table, with a filter on the id column. To use this in Rails, we need a bit more:

query = %(
select
items.id as ItemId,
items.name as ItemName,
items.price as ItemPrice
from items
where items.id = 12
)
query_results = ActiveRecord::Base.send(:sanitize_sql_array, query)
query_results = ActiveRecord::Base.connection.select_all(query_results).rows
query_results #=> [ [12, "My item", 4.99] ]
view raw 2.rb hosted with ❤ by GitHub

Now suppose we're getting a user input from an open text field in the UI for the item ID to filter by. In this case we could use string interpolation to insert the item ID into the query:

query = %(
select
items.id as ItemId,
items.name as ItemName,
items.price as ItemPrice
from items
where items.id = #{item_id}
)
view raw 3.rb hosted with ❤ by GitHub

Since we're passing a user input directly into the query, we've now exposed ourselves to potential SQL injection.

One way to get around this is to parameterize the query. In doing this, we'll move the item ID value into a parameter that we pass when we run the query:

query = %(
select
items.id as ItemId,
items.name as ItemName,
items.price as ItemPrice
from items
where items.id = ?
)
query_results = ActiveRecord::Base.send(:sanitize_sql_array, [query, item_id])
query_results = ActiveRecord::Base.connection.select_all(query_results).rows
view raw 4.rb hosted with ❤ by GitHub

Note that the query parameter in ActiveRecord::Base.send becomes an array, and we pass in the item_id value here. In composing the query, Rails will apply this value to the SQL where it sees the placeholder ?.

The approach above works well if you need to parameterize a single value, but what about multiple values? Let's change up some of the filters on our query:

select
items.id as ItemId,
items.name as ItemName,
items.price as ItemPrice
from items
where items.price > 3
and items.name ~ 'item'
view raw 5.sql hosted with ❤ by GitHub

We can parameterize the two filter values like this:

query = %(
select
items.id as ItemId,
items.name as ItemName,
items.price as ItemPrice
from items
where items.price > :item_price
and items.name ~ :item_name
)
view raw 6.rb hosted with ❤ by GitHub

Now, when we call ActiveRecord::Base.send, we send in the filter values as key-value pairs:

ActiveRecord::Base.send(:sanitize_sql_array, [query, { item_price: 3, item_name: 'item' }])
view raw 7.rb hosted with ❤ by GitHub

It might look like we're just moving the literal from one place to another, but Active Record is doing some magic behind the scenes here. When you embed the user-supplied literals into the query, Active Record cannot perform quoting on it. But when the literals are provided as a parameter, Active Record will quote the literals to prevent injection attacks. This approach is also more performant, as Active Record will compose the query first and re-use it as needed but with different parameters.