In Progress
Unit 1, Lesson 1
In Progress

LIKE Injection in SQL

If you develop with Ruby on Rails, there’s a good chance you use a SQL database as your app’s data store. Postgres and other SQL databases give us some powerful tools for enabling users to search through their data, but if we’re not careful we can accidentally expose our app to attacks from malicious actors. Our guest today, Alex Piechowski, specializes in security and he has a quick tip for avoiding a common source of denial-of-service attacks. Enjoy!

Video transcript & code

LIKE Injection in SQL

Scroll down for complete transcript. Screenshot of a menu search

We finally created a wonderful application to store and share recipes, but as recipes are added, our users are noticing it's very hard to find specific recipes. Due to this barrier, our recipe website is having a hard time getting consistent users. To resolve this, we're going to add a basic search.

Assuming we're using ActiveRecord, we can start by creating a class method .search on our model that, adds a where clause to our query, we can later utilize this in our controller.


class Recipe < ApplicationRecord
  def self.search(query)
    where(title: query)
  end
end

The query executes a direct match where against title which works great.

Screenshot of a menu search

If I search for “Fried cheese”, it shows me all results where all the titles are exactly “Fried cheese".

Unfortunately this search is too rigid for our end users. What if I want to find all recipes that instead start with the words "Fried Cheese"?

Screenshot of a menu search (This screenshot is an extra feature for the transcript only.)

The results would include items like "Fried Cheese", "Fried Cheese and Potatoes", and "Fried Cheese and Onions".

Assuming we're using PostgreSQL, we can implement this functionality utilizing an ILIKE with a trailing wildcard, which is the percent symbol.


class Recipe < ApplicationRecord
  def self.search(query)
    where(‘title ILIKE ?’, "#{query}%")
  end
end

With our new feature, our recipe site has created quite the impact in the cooking community. But with the newfound love of our recipe site, we also start getting complaints about the site becoming intermittently unavailable.

After inspecting the server logs, we find there's a particular search request that seems to be taking longer than all of the rest of the requests

The request is being made to our RecipesController's search action and is taking many seconds to complete, while other searches are completed much faster.

As we take a look at all of the queries taking longer than the normal, we start to notice a common theme. Of all searches that took longer than 4 seconds based on our logs, the requests that take the longest to complete have a high number of wildcards in the search query such as this search.


class Recipe < ApplicationRecord
  def self.search(query)
    where(‘title ILIKE ?’, "#{query}%")
  end
end

In this example, one of the malicious search queries resulting in the following query, our database is forced to process 9 total wildcards, 8 from the user’s query and 1 from our code’s trailing wildcard.


class recipe < ApplicationRecord
  def self.search(query) # query = "%a%a%a%a%a%a%a%a"
    where(‘title ilike ?’, "#{query}%")
    # SELECT "recipes".* FROM "recipes" WHERE (title ILIKE '%a%a%a%a%a%a%a%a%')
    #                                                       ^ ^ ^ ^ ^ ^ ^ ^ ^
    #                                                       1 2 3 4 5 6 7 8 9
  end
end

These requests are being made by somebody exploiting a vulnerability in our search. They might be doing this to have a laugh, to retaliate against this application, or perhaps to demonstrate their capability as a hacker to a smaller, potentially less-techy target.

The more wildcards that are added, the slower the query becomes. Multiple wildcards cause indices to be less effective, eventually resulting in a sequential scan. A sequential scan scans every record row by row.

This attack which utilized extra wildcards has resulted in a denial of service, also known as DOS attack.

We can prevent this denial of service attack by escaping the user submitted wildcards with the database’s escape character. An escape character is a special character that when placed before special characters causes them to be interpreted as literal characters.

By default in PostgreSQL, this escape character is \\ (a single backslash). For example, if I wanted to create a page...

Screenshot of a menu search ...for recipes that started with "100% Beef" for an upcoming beef conference,... ...I would have to utilize this query,...


Recipe.where('title ILIKE ?', '100\% Beef%')

...using a backslash as an escape. Without this escape,...


Recipe.where('title ILIKE ?', '100\% Beef%')
#                                 ^

Screenshot of a menu search

...I would be surprised to find that this also shows recipes for 100% Vegetarian Beef.

In the case of the user submitted percent symbols, we could attempt to...

Screenshot of a menu search ...handle this with String's #gsub method with something similar to the following, but doing this ourselves can be tedious and error prone.


query.gsub('%', '\%')`

This particular solution is vulnerable as it does not escape the escape character, which means that if the query input includes an escape character prior to a wildcard, our gsub will add another escape resulting in an escaped escape character and the percent symbol is still registered as a wildcard!


query = '\%'
query.gsub('%', '\%')` #=> '\\%'

Luckily, we don't have to manage the craziness involved with escaping sql LIKEs on our own when we're using ActiveRecord.

ActiveRecord ships with #sanitize_sql_like which takes care of the details of...


class recipe < ApplicationRecord
  def self.search(query) # query = "%a%a%a%a%a%a%a%a"
    where(‘title ilike ?’, "#{query}%")
    # SELECT "recipes".* FROM "recipes" WHERE (title ILIKE '%a%a%a%a%a%a%a%a%')
    #                                                       ^ ^ ^ ^ ^ ^ ^ ^ ^
    #                                                       1 2 3 4 5 6 7 8 9
  end
end

...sanitizing special characters, such as %, which acts as a wildcard.


class Recipe < ApplicationRecord def self.search(query) # query = "%a%a%a%a%a%a%a%a" query = sanitize_sql_like(query) # => '\%a\%a\%a\%a\%a\%a\%a\%a'
    where(‘title ILIKE ?’, "#{query}%")
    # SELECT "recipes".* FROM "recipes" WHERE (title ILIKE '\%a\%a\%a\%a\%a\%a\%a\%a%')
    #                                                                               ^
    #                                                                               1
  end
end

This fixes our LIKE injection which solves our unresponsive application issues, stopping the denial of service attack.

Any web application that accepts form submissions must take into account the possibility of malicious user input. One common attack is slowing the database to a crawl by exploiting unprotected ILIKE queries. Fortunately, as we've seen today, Rails gives us the tools to easily sanitize untrusted user input for database queries. We just have to remember to use them.

Happy hacking!

Responses