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:
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.
Since an attacker has full control of
payload (for example sake let’s say via
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:
- The second part,
1=1always equals true, which results in the first entry in the
userstable 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.
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
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
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:
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
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
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:
And viola, the attacker now has knowledge of every table in your database, including the
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
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
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.
Sometimes you need to chain together a series of queries, usually that’s with an
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.
Another common scenario is doing a starts with/ends with filter
LIKE. This query is more apt to introduce SQL injection because many people don’t understand how
1 2 3 4 5
Notice that with both queries, you’re going to have to do some string interpolation to insert the
You’ll want to make sure that this occurs inside the value that will be parameterized.
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.