Bad to good

Lets consider Rails 4 and an active record query using string interpolation.

Order.where("created_at > #{params[:start_date]} AND user_id = #{current_user.id}")

We are scoping an Order to a user to restrict access from another user’s order history.

The example is inserting the query parameter directly into the query string. That makes the query vulnerable to SQL injection. It is possible to change the parameters to manipulate the query.

The user can change params[:start_date] from 2018-08-31 to '2018-08-31' OR 1 --.

OR 1 will result in true potentially satisfying each condition. -- comments out the rest of the line.

SELECT orders.* FROM orders WHERE (created_at > 2018-12-21 OR 1 -- OR user_id = 1234)

The user successfully modified the query to return all orders after the given created_at date but ignoring the user_id. We can prevent this by using placeholder’s instead of string interpolation.

Order.where("created_at > ? AND user_id = ?", params[:start_date], current_user.id)

resulting in

SELECT users.* FROM users WHERE (created_at > '2018-12-21 OR 1 --' AND user_id = 1234)

In short

You should never use string interpolation in an active record query. Fortunately Rails 5 restricts escaping characters in a query. AR queries are less prone to SQL injection although it is still a vulnerability. A few good practices when querying based off user input are:

  1. Scope the results (current_user.orders.where(...))
  2. Use placeholders (.where('created_at > ?, ‘2018-02-12’)
  3. Use keyword placeholders (.where('created_at > :start_date, start_date: ‘2018-02-12’)

 

Did you like this article? Check out these too.

 

Found this useful? Have a suggestion? Get in touch at blog@hocnest.com.