The Dark Side of the Force: SQL Injection and Mass Assignment

Today, we will cover two ways in which your website can be compromised as well as some precautions you can take to avoid them. I should add a disclaimer that these are pretty amateur mistakes (ones which a new programmer such as myself could easily make), but they are easily avoidable and hopefully this post will help you better understand how they work rather than just knowing how to prevent them.

First is sql injections. Most applications, especially if they involve users and passwords will be backed up by a database to store all that information. With malicious sql injections, people can completely destroy your tables or steal users’ passwords. It goes something like this.

def so_much_bunny
  sql = <<-SQL
    SELECT *
    FROM bunnies
    WHERE bunnies.name = "#{name}"
  SQL

  DB.execute(sql)
end

Now if your name variable were set to, lets say “Marvin”, that’s fine and dandy, you would get back a row matching the bunny Marvin. But a less than ethical programmer might set name = “ OR 1 – which would run sql code that looked something like this

def so_much_bunny
  sql = <<-SQL
    SELECT *
    FROM bunnies
    WHERE bunnies.name = "" OR 1 -- "
  SQL

  DB.execute(sql)
end

What happens is the “OR 1” returns true and everything else after is commented out due to the double dash. So you would get back all the records in the table bunnies because your where statement has essentially been changed to “WHERE true” instead of actually needing a bunny’s name and returning only the row where it finds a match. Not very secure, eh?

One solution to this problem is never using string interpolation. You can sanitize the data by using parameters and passing them in like so:

def so_much_bunny
  sql = <<-SQL
    SELECT *
    FROM bunnies
    WHERE bunnies.name = ?
  SQL

  DB.execute(sql, name)
end

This will escape any special characters, so if someone tried to pass in “ OR 1 –, the quote would get escaped and treated like a part of the string rather than a closing of the name query.

In rails, the correct form would look something like this

Bunny.where("name = ?", params[:name])

Moving on, our second point of discussion is mass assignment in rails. Mass assignment is extremely useful as it allows you to create a new instance of a class and populate all it’s fields by just passing in a hash. So instead of doing this:

@bunny = Bunny.new
@bunny.name = "Margaret"
@bunny.status = "Happy"
@bunny.weight = "Heavy"
@bunny.color = "Brown"
@bunny.age = 2

you can just pass in a hash called bunny_params that holds all the information

@bunny = Bunny.new(bunny_params)

Unfortunately, with mass assignment, it’s possible to have a user change an attribute you don’t want them to touch. Let’s say we had an attribute “free_bunnies” that was set to a default boolean of false, and we don’t want users to willy nilly have the ability to free all the bunnies. Using mass assignment with no limitations, they very well can.

For example, if you had a form to create bunnies, that contained fields for name, status, weight, etc, but not a field for free_bunnies since you don’t want the user changing it, it would look something like this:

<%= form_for(@bunny) do |f| %>
  <div class="field">
      <%= f.label :name %><br>
      <%= f.text_field :name %>
    </div>
    <div class="field">
      <%= f.label :status %><br>
      <%= f.text_field :status %>
    </div>
    <div class="field">
      <%= f.label :weight %><br>
      <%= f.text_field :weight %>
    </div>
<% end %>

without any limitations, the user can change the form easily in their browser to pass free_bunnies to params instead of name and set the value to true and it would be passed through when creating a new bunny. So the original html would look like this

<input type="text" name="bunny[name]" id="bunny_name">

and the fanagled one would look like this

<input type="text" name="bunny[free_bunnies]" id="bunny_free_bunnies">

The solution here, or at least one solution, is something called strong parameters. Essentially, you have to whitelist the attributes you want to allow, otherwise they will get passed into the params hash but will be “kicked out” and not allowed when you create your new object and you’ll get an error saying “Unpermitted parameters: x, y, z”.

Back in Rails 3, you used to have to require a strong params gem, but now in the current version of Rails it’s built in. When you generate a scaffold (for bunnies in this case) with the associated fields, you will automatically get some lines that look like this

# Never trust parameters from the scary internet, only allow the white list through.
def bunny_params
  params.require(:bunny).permit(:name, :status, :weight, :color, :age)
end

By default all of the fields will be permitted, you will have to set what you will or won’t allow, and that’s that!

Now, I’m still no expert in the nefarious ways which people can use code, so enlighten me! What are the worst things you can think of to do if someone failed to account for malicious sql injections or accidentally whitelisted a param that they didn’t mean to?