A Complete Guide on How SQL Injection Attacks Work
We need to talk about SQL injection.
What it is, how to do it, and most importantly how to prevent it. We'll cover some examples of SQL injection too and explain how to identify vulnerabilities so you can protect your data.
- What is SQL Injection?
- How Does SQL Injection Work?
- Identifying SQL Injection Vulnerabilities
- Preventing SQL Injection Attacks
Let's get into it.
What is SQL Injection
SQL injection is a vulnerability that allows a malicious user to access your database in unintended ways.
This vulnerability is usually created when you allow user input to be passed directly to the database. When an attacker identifies this, they are able to craft inputs that include SQL commands that run on the database.
They essentially get access to read or manipulate your entire database.
We will go over examples in a moment, but the idea is this.
If you have an input on your site, like a search box that returns records from your database. An attacker can enter a string that gets read by the database to return matching results.
When an attacker identifies an injection vulnerability, they are able to pass SQL instructions in that string to the database. The database will then run whatever SQL commands it was given by the user.
This is not good.
What Can SQL Injection Attacks Expose?
The potential impact of this vulnerability is massive. It can result in anything from letting a user read every row of every table in your database, to being able to write INSERT or UPDATE commands to modify or potentially even delete your database.
Hackers have deleted debts from government databases, and stolen and published the personal information of millions of people because of SQL injection.
Hopefully, as you can see SQL injection is an incredibly dangerous security vulnerability.
And somehow even some of the largest companies still regularly expose this vulnerability.
How Does SQL Injection Work?
The attack is based on a malicious user passing SQL instructions to your database.
There are a ton of ways this can be done, and ultimately there is a potential threat on any line of code that you use to communicate with your database.
Let's set up a scenario. Say your website sells a product and you use categories to filter what the user can see.
https://example.com/products?category=books
Your server takes the URL and parses the category query string to figure out how to filter the results for you.
It will result in a SQL statement that could look like this:
SELECT * FROM products WHERE category = 'books'
But what if a malicious user is savvy enough to realize that you are querying the database based on that URL?
They might change the URL to something like this...
https://example.com/products?category=books'+OR+1=1--
Your server might then pass that string to the database, resulting in a query like this...
SELECT * FROM products WHERE category = 'books' OR 1=1--'
What they are doing is terminating the string early by passing the closing single quote in their input.
This allows them to add additional SQL to their command. In this case they add an OR 1=1 which means they'll now see all products regardless of category since 1=1 will be true for all rows in the table. They then append the final -- which is a comment so when your server adds the closing single quote, it doesn't throw any errors.
This is a very benign example but demonstrates the easiest way to run raw SQL against someone else's database.
Now imagine if instead of an OR operator, a savvy attacker added a UNION and appended other tables.
They might even be able to return user emails, passwords, and other sensitive data this way.
Also, this is just the URL, but any interface that allows user input is a potential attack vector. Input fields on your site, any textbox, forms... anything that interacts with the database creates a potential vulnerability.
So as you can see, it's easier to pull off a SQL injection attack than you might think.
Second-Order SQL Injection
Another form of SQL injection can be categorized as second-order SQL injection.
In the above example we were able to submit SQL instructions and immediately return results from the database. Second-order attacks work slightly differently and can be harder to detect.
Instead of passing input to the database, the goal with a second-order attack is to persist malicious SQL commands for future use.
Got an example?
Yep, let's walk through an example. Say I want to change my email on a website.
In the email field, I might enter malicious data that I know will be stored on the database and retrieved later.
hello@example.com';update users set password='password'--
Can you guess what this might do?
If that website ever passes my email directly to the database, it will include my SQL instructions... (and everyone's password will then be set to password).
This strategy of attack is dangerous because malicious inputs can sit dormant for a long time before they perform their intended exploit.
How to Identify SQL Injection Vulnerabilities
When you have a good understanding of how SQL injection works, I recommend you try and identify vulnerabilities on your site.
You can do this by testing your site and trying to inject harmless SQL.
You should also review your code to make sure you aren't passing strings directly to your database.
I would recommend you take a look at libraries for the language and framework you use to develop your site. Most frameworks have helpful tools that can detect these kinds of vulnerabilities before you promote them to production environments.
Test Your Site For Vulnerabilities
A surefire way to identify SQL injection vulnerabilities is to test your site for them.
Take some of the examples in the section above. Watch how your server responds. You might be surprised to see that it's easier to perform a SQL injection attack than you thought.
Don't dismiss this vulnerability just because you can't attack your own system though.
There are many ways of performing SQL injection beyond passing user input.
As mentioned above, second order SQL injection can't be detected immediately. It can be difficult to detect if users are storing queries that can be executed at a later time.
Carefully Review Your Code
The best approach to protect against SQL injection is to learn and understand how SQL injection is made possible.
When you understand how it works and what makes it possible, you will have a better idea of where you might be exposing opportunities in your code. Take a look at your code. Anywhere that you interact with your database. And make sure you aren't passing any kind of user input or user generated content (remember, second order injection) directly to your database.
Ask yourself, what could happen if this string contained malicious SQL instructions?
It's better to be safe than sorry, or in this case secure rather than exposing your entire database.
How to Protect From SQL Injection
Protecting against SQL injection is actually really easy.
What you need is input validation and sanitization.
These are techniques that clean user input to prevent it passing harmful strings to your database.
3 of the top website attacks (SQL injection, cross-site scripting, and remote file inclusion) all come from a lack of input sanitization.
And luckily, it's not hard to sanitize inputs in most major languages and frameworks.
How to Sanitize User Input
The idea is, you take a string the user submitted and you escape any characters that could cause issues.
Ruby on Rails handles this for you automatically when you use the Active Record ORM correctly. If you want to dig into this I recommend this article on preventing SQL injection in Rails.
But if you don't use Active Record correctly, or if you write SQL directly in code, you can still open your database up to vulnerabilities.
A good example of how easy it is to accidentally expose your data...
# Don't do this!
User.where("id = #{params[:id]}")
If you pass malicious input into the params[:id], you can inject SQL into this database call.
String interpolation is generally a very bad pattern to use in your database calls.
In this case, the better way to use Active Record would be to pass the string as an additional argument. Active Record will then sanitize it for you.
# Better example
User.where("id = ?", params[:id])
The where() method sanitizes strings passed as additional arguments, but it won't sanitize the first argument's SQL command.
Avoid Writing SQL in Active Record
The truly correct way to use Active Record is to avoid passing raw SQL strings altogether.
If you use Active Record's methods without string interpolation it will always sanitize your inputs automatically.
# Safe example
User.where(id: params[:id])
# or even better
User.find(params[:id])
Sometimes you need to write queries with operators that Active Record doesn't support. Such as the LIKE operator or when comparing dates. Just remember to check for SQL injection vulnerabilities you might be introducing.
This StackOverflow post has a great in-depth explanation or input sanitization in Rails, if you're curious.
Python, PHP, and most other popular languages also come with support for sanitizing user input.
So as you can see it's not hard to protect your database, it's actually pretty easy.
The real challenge is how to you prevent incidents where someone forgets to sanitize and protect from malicious user input.
That's hard... really hard.
Even the biggest, most advanced technology companies still struggle with this.
Recent SQL Injection Attacks
The GhostShell Attack
A group of hackers from the APT group Team GhostShell used SQL injection to attack 53 universities. They managed to steal the personal records of 36,000 university students, faculty, and staff.
Turkish Government
RedHack collective (another APT group) used SQL injection to access a Turkish government website and erase debt to government agencies.
7-Eleven Attack
Attackers used SQL injection to access data from several corporations, including 7-Eleven. They managed to access over 130 million credit card numbers.
HBGary Attack
Hackers from the Anonymous activist group used SQL injection to take down the website of IT security company, HBGary. The CEO of HBGary shared that he had the names of Anonymous organization members, which prompted the attack.
Recent SQL Injection Vulnerabilities Discovered
You'll recognize some of these companies. It goes to show that nobody is immune from this exploit.
Fortnite Vulnerability
In 2019, a SQL injection vulnerability was discovered that allowed attackers to access user accounts. Fortnite, by the way, has over 350 million users. Luckily, the vulnerability was quickly patched.
Cisco Vulnerability
In 2018, a SQL injection vulnerability was discovered in the Cisco Prime License Manager. The vulnerability gave attackers shell access to systems where license manager was deployed. This vulnerability has been patched.
Tesla Vulnerability
In 2014, security researchers shared that they were able to gain administrative privileges and steal user data through Tesla's website.
Final Words
All it takes is one vulnerable line of code, and your entire system is at risk.
Please, take SQL injection seriously!