A SQL injection is a security attack that is as dangerous as it is ingenious. By abusing the data input mechanisms of an application, an attacker can manipulate the generated SQL query to their advantage, which can cause catastrophic events.
What are organizations supposed to do to avoid this problem, then? Education is the key. And that’s what this post is about: educating people about not only what SQL injections are and how they work, but most importantly how to avoid them. Let’s dig in.
The what, why, and how of SQL injections
Before learning how to protect your code against SQL injections effectively, you must understand how they work and the threat they pose. Let’s take a look at that now.
What is a SQL injection?
A SQL injection is a security threat that allows an attacker to manipulate the SQL queries that the application sends to the database. That way, the attacker might access data that they aren’t authorized to see, such as other users’ data. Worse yet is the scenario in which the attacker can get write privileges to the database. They can then update or delete data, causing serious and lasting damages.
Why are SQL injections so dangerous?
Some might think that a SQL injection is only really damaging when the attacker gets write privileges to the database.
That couldn’t be further from the truth.
An attacker who reads unauthorized information alone can give you a lot of headaches. The attacker could access and expose sensitive data like financial data and personal information. Besides tarnishing your organization’s reputation, such a leak would cause consequences to your users/customers as the data is shared and sold. It could also put you in trouble with privacy regulations such as Europe’s GDPR or California’s CCPA, which could lead to severe legal and financial consequences.
How do SQL injection attacks work?
Most applications allow their users to input data somehow, and web applications are no different. Malicious individuals can abuse those data entering mechanisms in ways that interfere with the generation of SQL queries.
The things I call “data entering mechanisms” are the ways by which users input data into the application. Most of the time, these would be form fields and URL parameters. By tinkering with those elements in just the right way, attackers can inject—hence the name—additional SQL commands, which get executed.
A SQL injection example
Imagine a real estate agency website. After accessing the site, you can search for different kinds of properties and further filter by “renting” or “buying.” Typical stuff. After selecting your search parameters and clicking on the Search button, the selected parameters get added as URL parameters. So, let’s say you’re searching for apartments. Your URL would look something like this:
https://fakerealestate.com/properties?category=Apartments
And the resulting SQL query that gets sent to the database would be something like this:
SELECT * FROM properties WHERE category = 'Apartments'
The query above is pretty straightforward. We want to retrieve the rows from the properties table in which the value of the category column is equal to Apartments.
For the sake of the argument, let’s say this site is vulnerable to SQL injections. Let’s see what would happen if someone changed the URL to this:
https://fakerealestate.com/properties?category=Apartments’+OR+1=1–
That would effectively result in the following query:
SELECT * FROM properties WHERE category = 'Apartments' OR 1 = 1
Since one always equals one, the query above successfully asks the database for properties from all categories. This is a harmless example, but the general modus operandi is the same: The attacker wants to conclude the legitimate query and inject more commands after it in order to gain access to something they’re not supposed to be able to access, such as a list of all users and passwords.
Protecting your code against SQL injections
The most important aspect of blocking SQL injections can be summed up in a single sentence: Always assume user input to be insecure. And by user input, what I really mean is any information that comes from the outside of your application that the application itself didn’t put there.
So, don’t trust data from third-party systems your app imports. Also, any type of data provided by the user, such as form fields, URL parameters, or user-provided files should be automatically distrusted. Data from APIs your app consumes also go on that list. These examples are far from exhaustive, but the same rule applies: Consider user input unsafe by default. Verify it first, and only when you deem it safe should you proceed.
This tip might seem like general security advice that is applicable to a lot of different scenarios. How do you apply it specifically to the SQL injection issue? That’s what we’ll look at now.
Employ ORMs
One way to avoid writing SQL queries that are vulnerable to exploits is to not write SQL queries at all! Most languages nowadays count with powerful and flexible ORM (Object-Relational Mapping) frameworks that do all of the database-related heavy lifting for you.
However, like we mentioned earlier, ORMs are far from being the silver bullet against SQL injection. As you’ll remember, most Rails security exploits are SQL injections, despite Active Record. And they also introduce their own security problems in the form of bugs, large API surfaces that might confuse users, or other problems.
Use parameterized queries
When you do have to craft SQL queries that incorporate data entered by users, you must do it safely. Luckily, most programming languages offer ways to create parameterized queries. Parameterized queries, as the name suggests, are queries you prepare using a template. This prepared query contains parameters which you can then bind with the values informed by the user.
That way, you avoid string concatenations, and the “gimmick” of providing a single quote to close a statement won’t work, resulting only in invalid SQL.
Code analysis
Code analysis tools, also called linters, are tools that analyze your source code and provide guidance related to best practices, adherence to coding standards, and formatting rules, among other things. They can also identify anti-patterns and possible security problems.
Fortunately, most major programming languages count with linters. In Ruby, you have tools like Rubocop and Brakeman, among many others. Python developers can count on Pylint, which, despite not being explicitly a security tool, can detect code problems that might lead to security issues.
Execute with the least privilege possible
This tip is different from the previous ones. Here, we’ll leave the realm of programming languages for a bit. Instead, we’ll venture into the database. This tip is less about avoiding SQL injections and more about mitigating their effects, should they happen.
The idea itself is straightforward: In areas where you’re just reading data, don’t use connection strings in which the user has writing privileges. That way, even if an attacker manages to inject a malicious query, they won’t be able to insert, change, or delete any data.
In a nutshell, always execute things with the least privilege you can get away with.
Security is hard; don’t make it harder
Getting security right is hard. Getting it wrong has dire consequences. In this post, we’ve talked about SQL injection, an old security threat that refuses to die. Throughout this introduction to the topic, we’ve explained what SQL injections are, told you why they’re so dangerous, and outlined simple steps you can take to avoid them.
Thank You for reading till here. Meanwhile you can check out my other blog posts and visit my Github.
Top comments (1)
This article is very interesting, but I would tell about protecting your code against SQL injections a bit differently.
To avoid SQL injection attacks, keep the data separate from SQL. So that data stays as data and is not read as commands by the SQL parser: