Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Rails SQL Injection with LIKE

Rails SQL Injection with LIKE

SQL ‘LIKE’ injection is a form of denial-of-service attack where an end-user adds wildcards to a SQL query that uses the ‘LIKE’ keyword. This greatly increases the time it takes to run the query. If your Rails application allows user searching using email:

users = User.includes(:profile).where("profiles.email LIKE ?", "#{term}%).all

A user can include percent signs in their search and vastly increase the query duration, slowing down the database.

What are the risks? Because the attack causes database queries to skip the index and run slower, the main risk is a denial-of-service attack. Many searches could bog down the database.

Countermeasures in Rails Sanitizing user input is the best way to prevent injection. For Rails version 4.2 or greater, ActiveRecord has a new helper function, sanitize_sql_like, which escapes out the percent signs (and the _ character).

With my example:

old code:

coins = Coin.where("lower(name) LIKE ? OR lower(symbol) LIKE ?", 
  "%#{options[:query]}%", "%#{options[:query]}%")

new code:

sql_like = "%#{sanitize_sql_like(options[:query] || '')}%".downcase
coins = Coin.where("lower(name) LIKE :name_query OR lower(symbol) LIKE :symbol_query", 
  name_query: sql_like, symbol_query: sql_like)

ref: https://rorsecurity.info/portfolio/rails-sql-injection-like

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.