<img height="1" width="1" style="display:none;" alt="" src="https://analytics.twitter.com/i/adsct?txn_id=nv7vl&amp;p_id=Twitter&amp;tw_sale_amount=0&amp;tw_order_quantity=0"> <img height="1" width="1" style="display:none;" alt="" src="//t.co/i/adsct?txn_id=nv7vl&amp;p_id=Twitter&amp;tw_sale_amount=0&amp;tw_order_quantity=0">

Rails, SQL Injection, and You: What is SQL injection?

David Celis on Oct 26, 2015

SQL injection (SQLi) is a technique in which an attacker is able to submit SQL statements to an application and have them executed by the application's database. This allows the attacker to manipulate that database in unexpected and unintended ways. Consider the classic XKCD comic, "Exploits of a Mom":

exploits of a mom

What happened to those student records?

When new students are enrolled in classes, the school's registrar adds them to the database they use to maintain student records. Information about the students themselves gets put into thestudents table. If they were using Rails, that process would look something like this:

ActiveRecord::Base.connection.execute("INSERT INTO students (name) VALUES ('#{name}');")

If my parents were enrolling me in this school, that would end up expanding to the following SQL statement:

INSERT INTO students (name) VALUES ('David Celis');

Then I'm off to classes! But when Bobby Tables' mom enrolls him, here's what goes into the student records database:

INSERT INTO students (name) VALUES ('Robert'); DROP TABLE students;--');

Whoops. Bobby's name, Robert'); DROP TABLE students;--, closes out the VALUES declaration and then starts its own statement: DROP TABLE students;. That statement is simply executed and there goes the students table! For good measure, that last pair of hyphens starts a comment to make sure anything else in the statement is ignored so that no syntax errors prevent the malicious SQL from running.

How can we avoid this in Rails?

In Rails, the most common way of interacting with a database is using ActiveRecord. ActiveRecord is the default Object-Relation Mapper (ORM) that Rails includes as part of its framework. ORMs are a very convenient way to communicate with databases; they allow developers to query for, insert, and manipulate database records without having to hand-craft SQL statements. For example, remember that previous code to insert a new student into the database? With proper use of ActiveRecord, that code would look more like that:

Student.create(name: "David Celis")
Student.create(name: "Robert'); DROP TABLE students;--")

Definitely more convenient, but ORMs have an added benefit: it is generally safer to use them than not. In many cases, ORMs will properly escape input or create parameterized queries to avoid SQLi attacks. In the above example, ActiveRecord would create a prepared statement and execute it with the provided values. Rails would log the following:

INSERT INTO "students" ("name") VALUES ($1) RETURNING "id" [["name", "Robert'); DROP TABLE students;--"]]

Let's dissect this. What we've ended up with here is a prepared statement:

INSERT INTO "students" ("name") VALUES ($1) RETURNING "id"

and the bind values that will be passed into it:

[["name", "Robert'); DROP TABLE students;--"]]

The actual SQL that's executed in PostgreSQL would look something like that:

PREPARE student_insert (varchar) AS
  INSERT INTO "students" ("name") VALUES ($1) RETURNING "id";
EXECUTE student_insert("'Robert'); DROP TABLE students;--");

By using a prepared statement, ActiveRecord prevents unwanted SQL statements from being executed. Instead, that string is treated only as a value and will not be executed. It just gets stored in a new student record and no harm is done. Unfortunately, there are holes in ActiveRecord that are still vectors for SQLi attacks if user input is passed in haphazardly. In part two of this three-part series, we'll explore the unsafe bits of ActiveRecord and how to avoid getting your database hacked. In part 3, we'll look at how Immunio actively protects your application against SQLi attempts without you having to change your code.