In a previous post on Fixing Command Injection Vulnerabilities you saw the damage that can be caused when an attacker gets access to your system. It’s basically Game Over!
The same is true of SQL Injection also known as SQLi. The dangers of SQL Injection have been talked about for a long time, but for many developers they’ve never seen it in practice. This post is going to explore what a SQL Injection is, why you need to fix it, and how to fix it!
What is a SQL Injection Vulnerability?
SQL Injection falls into the Injection category of vulnerabilities detailed in the OWASP Top 10. SQL Injection is easy to exploit, occurs commonly, and the impact is severe. As a professional software developer it is your job to recognize and fix these vulnerabilities!
This is what SQL Injection looks like:
1
|
|
That’s all that’s required for an attacker to gain access to your entire database. Don’t believe me? Let’s see how an attacker could own your database.
Basic Exploitation
Since an attacker has full control of payload
(for example sake let’s say via params[:email]
)
they can insert whatever they’d like into your where
query. Here’s an example:
1 2 3 4 5 6 7 |
|
Above the attacker is sending a payload of ') or 1=1--
. It works like this:
- The first part of the payload
')
sets the query to return zero results; email is blank:email=''
. - The second part,
1=1
always equals true, which results in the first entry in theusers
table being returned. - The final part,
--
is a SQL comment. This is a technique to cancel out any further query modifications that could occur server side. Essentially, this reduces the fine tuning to make a payload work.
Simplified, most SQL Injections will follow this type of payload format:
- Close the query
- Insert the attack
- Prevent server modifications
While this seems trivial, an attacker can now manipulate payloads to get access to juicier information. Let’s see another example:
1 2 3 4 5 6 7 |
|
Using the payload ') or admin='t'--
the attacker has gotten the system to return an admin user. They now
have knowledge about an admin in your database.
Enumerating
In order to get a full dump of admin accounts the attacker needs to be able to enumerate through your admin table. It turns out that this is trivial to accomplish using an id filter:
1 2 3 4 5 6 7 |
|
Here the attacker adds and id > 193
to get the next admin user. At this point, they keep incrementing
id until they dump every admin out of your database.
In the back of your mind maybe you’re thinking:
“My user table gets owned, but I encrypt my passwords so at least the damage is just limited to a single table. Big deal if someone gets access to all my user’s … that’s not too bad … right?”
Now you’re smart so I’m sure you didn’t say that to yourself. Because this is bad. And an attacker can do worse!
Discovering Other Tables
How can an attacker find out what other tables exist in the application? Via the sqlite_master
table.
This table lists the entire database’s schema including tables and indexes.
In order to access this information a couple of new techniques will be required. Let’s see the payload first and then look at the techniques:
1 2 3 4 5 6 7 |
|
The first new technique is the addition of the union
operator. This is a SQL operator (not limited to sqlite3) that combines the result of two select statements.
This payload also introduces a new technique of querying a system table:
1
|
|
What’s happening here is that the attacker is selecting the name column from the sqlite_master
table, and then inserting
1’s to fill out the remaining columns. Without those 1’s the database would throw an exception:
1 2 3 |
|
The end query that gets sent to the database looks like this:
1 2 3 |
|
Remember that the first query to users
doesn’t return a result so the result of the second query is interpreted
as a User
and fills a User
object with the sqlite_master
information. Specifically, the payload is crafted
so that the name field corresponds with the email field in User
.
In this particular example the result was email: "schema_migration"
which isn’t helpful. Of course an attacker
could use the enumeratation technique from eariler to traverse the entires in the sqlite_master
table, but
that’s slow. Instead the payload can be modified to use a function and get all the tables in the database at once!
1 2 3 4 5 6 7 |
|
Above the payload is using the group_concat
function provided by sqlite3
to pull together all of the tables into a single value: users,credit_cards,schema_migrations,unique_schema_migrations,sqlite_sequence
And viola, the attacker now has knowledge of every table in your database, including the credit_cards
table!
Accessing Other Tables
Now that the attacker has discovered the credit_cards
table in the application, they’re going to pull as much
out of it as they can. Using the same union
technique from above:
1 2 3 4 5 6 7 |
|
The output of User
ought to scare you! An attacker has managed to populate the email field with a credit
card number.
For our attacker this is where the party really starts. They have a toe hold into your system, and it’s a matter of time and a simple script to dump all your database. allthethings
How to fix SQL Injection Vulnerabilities
By now it should be crystal clear why you must fix SQL Injection vulnerabilities. In order to fix your SQL queries you’ll need to use parameterization. Parameterization, in a nutshell, is the safest way to handle unsafe user input. And whether you’re using ActiveRecord, Sequel, ROM, or some other ORM they’re all going to have facilities for parameterizing queries.
Let’s look at some common unsafe queries that frequently occur and how to fix them (these examples are ActiveRecord based.)
Single Parameter Queries
The most common use case for Ruby queries is a single parameter.
1 2 3 4 5 6 7 8 |
|
While line 3
above looks very similar to line 8
, they are different in that line 3
uses
string formatting instead of parameterization
which is unsafe for protecting against SQL injection.
Looking at the Unsafe vs Safe examples above you can extrapolate a rule of thumb: If you have to add surrounding quotes to your query, you’re vulnerable to SQL Injection.
Compounding Queries
Sometimes you need to chain together a series of queries, usually that’s with an AND
statement:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
|
ActiveRecord is great because it allows you to easily chain together multiple pieces of a query and because they’re evaluated lazily.
One of the real tricky places I’ve seen people struggle with is OR
statements. This is
in the process of changing
but right now the common pattern is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
|
While not very pretty, notice that the parameters are passed in separately from the query. This way parameterization can still occur keeping you safe from SQL Injection. There are ways to pretty this up which I’d encourage you to use if this type of code is in your code base.
LIKE Query
Another common scenario is doing a starts with/ends with filter
using LIKE
. This query is more apt to introduce SQL injection because many people don’t understand how
it works!
1 2 3 4 5 |
|
Notice that with both queries, you’re going to have to do some string interpolation to insert the %
signs.
You’ll want to make sure that this occurs inside the value that will be parameterized.
Raw Queries
The final common scenario is raw queries. These are queries where you need to get right into the SQL itself without using ActiveRecord or any other type of framework.
1 2 3 4 5 6 7 8 9 10 11 |
|
The above query is too simple for a raw query, you’d normally be doing a complex query, but at least now you can see proper parameterization. Raw queries follow in the same footsteps as previous examples of query parameterization.
That wraps up this post on SQL Injection. I hope that you learned something new. If there’s a Ruby or Rails security topic that you’d like me to touch on send me a tweet or an email.