~ 10 min read
Raw SQL Queries are Actually Better for Security Than ORMs?
In this article, I’d like to present my opinionated view on the following premise:
Are there security vulnerabilities that you can avoid by not using an ORM and opting for raw SQL queries or Query Builder patterns?
ORMs are libraries and a concept that stood the test of time in software. They’re like this massive historical structures that has been the pilgrimage for software developers who seek to build database-intensive applications.
Security engineers might be shocked to hear that developers choose to use ORMs, a way to abstract the database layer, for reasons not related to security at all. But yes, that’s probably the only the reason the “security department” will ask you during a code review or design review to choose an ORM - the security benefits.
What is an ORM and how does it work?
An ORM, a short for Object-Relational Mappers, is a concept that allow developers to create an abstraction layer between the lower-level database language (SQL and its different dialects) to the application code.
When you choose to opt-in to an ORM library, you’re essentially choosing to avoid writing raw SQL queries. Instead, you write code using a programming language SDK or DSL (Domain Specific Language) that interacts with the database using an object-oriented programming language.
If you’re new to ORMs, some examples of popular ORM libraries that you may have encountered are Sequelize for Node.js, Hibernate for Java, and Entity Framework for .NET.
Advantages of using SQL ORMs for security
Before we dive into the security advantage of using an ORM, let’s just call out quickly the obvious benefits for using an ORM from a developer standpoint:
- Because ORMs take out the raw SQL writing, developers don’t need to know SQL to interact with the database and while always enriching your knowledge is a good thing, using an ORM lowers the barrier of entry for developers in a team who are not familiar with SQL and they can quickly be productive.
- ORM libraries often come a long with more features and capabilities that developers need to work with a database such as connection pooling, migrations, database schema and content seeding, and more.
I’ll forgo the disadvantages list for ORMs since that’s not really the focus of this article, and anyway ORMs or not - there are often very opinionated camps pro and against.
So what are the security advantages of using an ORM? Why do security teams often recommend using an ORM.
The reason is SQL injection.
SQL injection is a type of attack where an attacker can execute arbitrary SQL queries on a database by manipulating the input data. To put it in practical terms, imagine the following raw SQL query (that does not use an ORM):
const country = req.body.country;
const query = `SELECT * FROM users WHERE country = '${country}'`;
sql.query(query, (err, result) => {
if (err) {
console.error(err);
return res.status(500).send('Internal Server Error');
}
res.json(result);
});This example works great when the user input is a valid country name, like say ‘Portugal’. But when the user input is being abused to create a manipulated query, like ' OR 1=1 --, the query will become:
SELECT * FROM users WHERE country = '' OR 1=1 --''Which leads to all users being returned, because the 1=1 condition is always true. In this example it leads to a leak of data, but user input can be manipulated in a way that creates more destructive consequences that drop database tables, escalate privileges, and more.
So if you choose an ORM, you’re essentially choosing to avoid writing raw SQL queries that could end up in concatenating user input in an insecure way, and thus avoiding SQL injection attacks. Here’s an ORM code example:
// Using Sequelize ORM
const country = req.body.country;
const result = await Countries.findAll({
where: {
country: country
}
});In such a code pattern, there’s no string concatenation of user input into an SQL query and thus no SQL injection vulnerability (not precise, I’ll add a note on this later in the article).
In short, the ORM library’s abstraction layer takes the responsibility to create SQL queries in a secure and proper manner that avoids SQL injection vulnerabilities, and this is exactly why security teams often recommend using an ORM - they move the risk to the third-party component and its maintainers and away from their own engineering teams.
👋 Just a quick break
I'm Liran Tal and I'm the author of the newest series of expert Node.js Secure Coding books. Check it out and level up your JavaScript
Advantages of not using SQL ORMs for security
After we’ve set the stage for the security advantages of using an ORM, it begs an interesting question - if you choose not to use an ORM, would you be able to mitigate a type of security risk or actual vulnerability class, just by opting to use raw SQL queries or a Query Builder pattern?
Let me put in front of you a practical code pattern that is a classical pattern of using ORMs:
const User = sequelize.define('User', {
name: {
type: DataTypes.STRING,
allowNull: false
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
role: {
type: DataTypes.STRING,
allowNull: false
}
});
function route.put('/users', async (req, res) => {
const user = req.body;
const userDetails = await User.save(user);
res.json(userDetails);
});I can sense you’re nodding your head in acceptance as you read this code snippet and already theorize where this is going… but for the sake of a baseline, let’s quickly explain what is happening:
- A User model is defined with 3 fields: name, email, and role. Role, as in
admin, oruser, for example. - A POST route is defined that creates a new user in the database using the User model and the request body.
And so getting to the weeds of the problem that lies in this code pattern, let’s consider the scenario in which the POST request body looks as follows:
{
"name": "Angela",
"email": "angela.bennette@thenet.com",
"role": "admin"
}Next, the application code takes in this request body and passes it to another layer in the application that is responsible to persist it - the User.save(user) method call.
However, perhaps unexpected by the developer, the User.save(user) method blindly takes in the entire request body and creates or saves user entry in the database with the role of admin.
This sets the stage for the classic Mass Assignment security vulnerability via an ORM.
Mass assignment security vulnerabilities in ORMs and Raw SQL
Mass Assignment security vulnerabilities are a type of vulnerability where an attacker can modify more fields than the developer initially intended, by manipulating the request payload or any other object that gets passed down the chain to a database or persistency.
Specifically with ORMs and ODMs (ODMs are applicable if you do MongoDB and other document-based databases), mass assignment vulnerabilities is a very common security issue because these types of libraries prefer language abstractions such as User.save({}) or User.create({}) that take in an object and persist it to the database based on its properties.
All it takes, is a developer’s oversight, distraction or naive mistake to blindly pass a user input object (such as req.body.user) to the underlying ORM library, without running it first through a schema, role-based allow-list or other validations and sanitization checks.
Unlike in an ORM, when you write raw SQL queries you have more control over the fields being updated and can prevent mass assignment vulnerabilities because the code pattern is more explicit and you have to manually write the SQL query, such as:
const user = req.body;
const query = `INSERT INTO users (name, email) VALUES ('${user.name}', '${user.email}')`;
// p.s. written this way for brevity, DO NOT use this insecure code pattern in productionSimilarly, if you use a Query Builder pattern, you can also prevent mass assignment vulnerabilities by explicitly defining the fields being updated, such as:
const user = req.body;
const userDetails = await knex('users').insert({
name: user.name,
email: user.email
});Can developers build a similar abstraction layer to ORMs that they’ll pass the request body and blindly persist an object even when they practice raw SQL queries or Query Builder patterns? Probably yes if they really work hard to do that but the point is that this User.save() pattern is very likened to ORMs and very unlikely to be used in raw SQL queries or Query Builder patterns.
Excessive privileges for ORMs
When I asked the question of this article’s topic on X/Twitter, Arandel (@arandel) offered another interesting insight about access controls and how ORMs can also introduce excessive privileges to the database.
At first, I was a bit skeptical about this point because from my own experience I never assigned different roles or permissions based on whether I chose to use an ORM or not but this nevertheless is a valid point.
For example, for ORMs to function well in terms of their higher abstraction layer, they may need more excessive permissions to the database to facilitate reflection and other means for these libraries to essentially “gather data” about the database.
Another practical example is that they may require a specific permission to query table schema information to extract metadata about the tables and columns in the database so they can properly map them to the object-oriented programming language.
ORMs open up code to SQL Injection vulnerabilities
Update: this section was added on 2024-10-15, after the initial publication, as more context about ORMs and SQL injection vulnerabilities came to light.
ORMs are often huge libraries. How well do you know the entire API surface of say sequelize, the popular Node.js ORM? Let’s explore the premise of how ORMs themselves have API loopholes, which are usage patterns that are not inherently secure and can lead to SQL injection vulnerabilities. Most probably, without the developer knowing it at all.
The popular Ruby on Rails web framework makes use of an ORM called ActiveRecord. It appears that ActiveRecord exposes some APIs that developers can use, which aren’t making use of parameterized queries. Here are a few examples of these:
ActiveRecord calculate methods
params[:column] = "age) FROM users WHERE name = 'Bob';"
Order.calculate(:sum, params[:column])Would result in the following SQL query:
SELECT SUM(age) FROM users WHERE name = 'Bob';) FROM "orders"ActiveRecord delete_all method
params[:id] = "1) OR 1=1--"
User.delete_by("id = #{params[:id]}")Similarly to the above, would result in the following SQL query:
DELETE FROM "users" WHERE (id = 1) OR 1=1--)ActiveRecord vulnerable to SQL injection methods
Other such methods that developers might query using the ActiveRecord ORM that aren’t using parameterized queries are:
User.exists? params[:user]User.find_by params["admin = '1'"]- The
frommethod - The
groupmethod
And quite a few more are on this list.
The rails-sqli.org website has a comprehensive list of these ActiveRecord methods that are vulnerable to SQL injection attacks. It is a good reference to consult and make sure you’re not accidentally using these methods in your Rails application without proper security controls in place to prevent SQL injection vulnerabilities.
Conclusion
So to summarize the premise:
Are there security vulnerabilities that you can avoid by not using an ORM and opting for raw SQL queries or Query Builder patterns?
Yes, at least two. Mass Assignment vulnerabilities and excessive privileges to the database.
Does it mean you should avoid ORMs and opt-in to full on raw SQL queries? My god, no. However, pure raw SQL queries are not really a thing anymore (I hope). I’d like to think we’ve passed beyond them and now default to parameterized queries even if the style sometimes looks like raw SQL:
// The popular Next.js sql`` tagged template literal pattern:
sql`SELECT * FROM users WHERE email = ${email}`There’s also a good middle-ground with choosing Query Builders.
So are ORMs safe from SQL Injection vulnerabilities?
If only it was that simple. ORMs are not a silver bullet for SQL injection vulnerabilities, no. There are still ways to introduce SQL injection vulnerabilities in an application that uses an ORM but most of the time, it’s due to one of 2 primary reasons:
- Developers who use the ORM misuse the API in some way (for example, they use the raw query API that is left as an escape hatch for developers to write raw SQL queries).
- The ORM library itself has a vulnerability due to the way the maintainers have implemented certain features, perhaps missed to correctly apply SQL parameterization, and other security best practices.
And so, true for an ORM and for every other third-party dependency you use - always keep it up-to-date with security patches and follow the best practices for using the library.