Best Way to Fixing SQL Injection: ORM is not enough, should we maintain?
Sql Injection, One of the most dangerous and widespread vulnerability types is SQL Injection, which gives attackers access to your backend database. Using prepared statements and Object-Relational Mapping (ORM) is a good way to defend against SQL injection, but it’s not enough. As this post shows, ORM packages such as Sequelize and MySQL can and do have flaws that can leave you exposed. To truly protect yourself, you need to do more.
In our State of Open Source Security Report 2019 we learned that SQL Injection vulnerabilities are still a common source of security concern with a peak of 16 vulnerabilities found in libraries on the PHP Packagist repository.
TL; DR
An SQL Injection vulnerability, or SQLi for short, allows an attacker to add – “inject” – unstructured text into an SQL command, triggering unintended consequences. One way to protect against SQL Injection is to use an ORM package, which maps your objects and actions on them into SQL for you. Using such packages means you’re never actually composing SQL, and so don’t get the chance to slip and allow malicious injections. Hooray!
What’s easy to forget, though, is that just because you’re not composing SQL doesn’t mean it’s not being composed. And indeed, the ORM packages in npm still need to convert these actions into SQL. These packages are, like all packages, software, and software can have bugs. During the past year, 4 SQL Injection vulnerabilities were reported on the two top ORM packages in npm, sequelize and node-mysql, moving this concern from theory to reality. These issues are fixed in the latest package versions, but you may be using old versions, and new vulnerabilities may turn up.
This post shows a couple of examples of such flaws, and explains what other layers of defense you should apply, the key ones being:
- Test your dependencies for vulnerabilities
- Validate input, even when using ORM.
Before we dig into the problem scenarios, though, let’s start with a quick refresh on what SQL Injection is, what are your options for protecting against it, and what ORM is.
Simple SQL Injection Example
Imagine a task list app with a search function, searching items containing a given text. The search is performed as a DB query, implemented using the sequelize package. Assuming the DB connection was already set up, here’s the backend search function:
function findItems(req, resp)
{
try {
// Find the relevant items
sequelize.query(
"SELECT Desc FROM Items WHERE Desc like ('%" + req.params.snippet + "%')",
{ type: sequelize.QueryTypes.SELECT}
) // Retrieve results
.spread(function(results, metadata) {
// Add results to response
});
} catch {
// Handle error
}
}
In a legitimate use case, the user will enter a simple value (e.g. Buy), which will translate in the legitimate following query, returning all the relevant shopping tasks in our list:
SELECT Desc FROM Items WHERE Desc like ('%Buy%')
However, an attacker may intentionally submit a single-quote (') character in order to break out of the intended string context and into the query itself. For instance, the attacker may enter the value ') UNION SELECT username||'_'||password FROM Users --, resulting in the following query:
SELECT Desc FROM Items WHERE Desc like ('%') UNION SELECT username||'_'||password FROM Users -- %')
Assuming we have a Users table with the fields password and username, the query above will append to our TODO items list the full list of usernames and passwords. The remainder of the text is easily commented out using the -- command, simply to keep the SQL query intact.
This vulnerable implementation may seem especially naive, but variations of it occur quite often. In all cases, some form of unchecked user input is appended to a raw SQL query, breaking out of the original context (e.g. a string) and invoking unplanned actions. On the other side, the attack we showed was pretty simple and accurate, but real attackers will gradually send many different attack variants, and only need one of them to bear fruit.
Impact & Remediation
SQL Injection is an extremely severe vulnerability. In most cases, a single SQL Injection on any part of your website can eventually be expanded to running any query on the DB, extracting and manipulating its data. Since DBs often hold the most sensitive information in the system, allowing attackers such access is devastating.
There are two primary (not mutually exclusive) techniques to prevent SQL Injection: Input validation & prepared statements.
Input validation
SQL Injection, like other Injection Attacks, start with malicious user input. Therefore, a good way to prevent it is to verify that the input the user provided is valid. If it’s not, we can fail the operation entirely, or remove the potentially dangerous characters.
Input validation can be done using a negative or positive security model.
Negative security model means disallowing specific dangerous characters or patterns. For instance, in the example above, we could disallow the single quote that broke out of the string context. Unfortunately, SQL is complicated, making it hard to find all the potentially dangerous characters. Continuing this example, we would in fact also need to block characters like backspace (b), backslash (\), null (x00) and likely several others – and the list gets longer if we support multiple DB types. All that said, blocking dangerous characters is a fairly effective and simple mitigation technique.
Positive security model means only allowing specific characters. In the example above, limiting the input to letters, digits and spaces (/a-zA-Z0-9 /) would have effectively addressed the risk. This approach, also called Whitelisting, is usually better from a security perspective, as it avoids surprises from values you didn’t consider. However, it’s more likely to block legitimate characters, especially when expanding to support Unicode character sets.
Prepared Statements & ORM
If SQL Injection attacks begin with the input, they end with the SQL query, making it the second opportunity to avoid the issue. On this end, the crux of the problem is the simple string concatenation used to create the SQL query. If, instead, we used a template for the query, we could let the DB (or the connecting library) know we meant this to be a string value, and let it encode the string as needed. This is the solution we mentioned at the top of this post.
These templates are best known as “Prepared Statements”, or sometimes “Parameterized Statements”. The sequelize package we used above supports them as well, and so we could fix the vulnerability by modifying our function to look like this:
function findItems(req, resp)
{
try {
// Find the relevant items
sequelize.query(
"SELECT Desc FROM Items WHERE Desc like ?",
{ replacements: ['%'+req.params.snippet+'%'],
type: sequelize.QueryTypes.SELECT }
) // Retrieve results
.spread(function(results, metadata) {
// Add results to response
});
} catch {
// Handle error
}
}
Sequelize will understand the ? represents a value and not a SQL command, and will encode it appropriately, blocking attempts to escape.
Prepared Statements are not only a good way to secure your code, but also a good way to make it more readable and easier to maintain. In other words, whenever you feel tempted to concatenate values into a SQL statement, squash the urge and use a prepared statement instead.
Taking a step further, an even more programmatic approach to SQL is the use of Object-Relational Mapping (ORM). Using ORM means mapping your DB tables to your objects, allowing you to read, write and query entire objects. Since ORM further reduces your use of explicit SQL, it is also a good way to avoid SQL Injection.
When ORMs are vulnerable: Sequelize
Prepared Statements and ORM are both good ways to pass the encoding responsibility on to the “experts” – the packages that focus on doing just that. However, being an expert does not mean never having bugs… As mentioned at the top, this was demonstrated this last year through 4 SQL Injection vulnerabilities in two of the top ORM npm packages, sequelize and node-mysql.
The vulnerabilities were consistently due to unvalidated parameters in various ORM and prepared statement calls. These ORM and prepared statements function calls still, at the end of the day, need to translate the parameters into a SQL statement, and could forget to escape or validate when doing so.
Let’s review a couple of the sequelize vulnerabilities, to better understand what took place. It’s worth noting these vulnerabilities are now fixed, and that the Sequelize developers were quick to respond once the issues were found and logged. You can find the full list of Sequelize vulnerabilities on Snyk’s Vulnerability DB along with remediation guidance.
The first one, disclosed in January 2016 and fixed in version 3.17.0, affected the findAll function, often used to query objects from the DB when using ORM. When converting its parameters to SQL, the function failed to restrict the values of the LIMIT parameter, opening the door for a SQL Injection vulnerability.
Here’s an example of how this vulnerability could be triggered on a list of TODO Items.
models.Items.findAll({
limit: '1; DELETE FROM Items WHERE 1=1; --',
}).then(function (users) {
console.log(users);
});
Assuming Items contains a Username and Desc field, the resulting query would look roughly like this:
SELECT Username,Desc FROM Items LIMIT 1; DELETE FROM Items WHERE 1=1; --
Another similar oversight was disclosed in late March, 2016. This time, the flaw was when creating prepared statements and concatenating values for the IN statement. Here’s an example of an attack
db.query('SELECT Desc FROM Items WHERE Username IN (:names)', {
replacements: {
names: ["Bobby", "'); DELETE FROM Items WHERE 1=1; --')"]
}
});
These vulnerabilities, once found, are not terribly complex, but they shaw that even popular packages are not infallible. SQL is complicated, and it’s easy to miss edge case scenarios.
Solution: Defense in Depth
If it wasn’t clear before, let me make this crystal clear: you should most definitely use ORM and prepared statements. They take away the vast majority of SQL Injection risk, and are generally good software practices. However, you shouldn’t think that using these packages makes you completely immune.
Instead, you should also use input validation. This will keep malicious input out of your system to begin with, which is a great way to reduce risk. Applying multiple defences like this is often referred to as “Defense in Depth”, and it’s a very good thing. If an attack gets through one layer of defence (input validation), the second layer is likely to block it. If each layer allowed 1% of attacks through, two layers would block all but 0.01% of attacks – that’s good math.
In addition, you should stay on top of known vulnerabilities in the packages you use, and fix them as soon as they find. You can use Snyk (for free!) to test your applications for the vulnerabilities above, and integrate vulnerability tests into your dev process to help stay vulnerability free.