Parameterized queries, not escaping, are the true shield against SQL injection in your Express API.
Let’s watch this in action. Imagine a simple Express route that fetches user data based on an ID from the URL:
const express = require('express');
const mysql = require('mysql');
const app = express();
const dbConfig = {
host: 'localhost',
user: 'root',
password: 'password',
database: 'mydatabase'
};
const connection = mysql.createConnection(dbConfig);
app.get('/users/:id', (req, res) => {
const userId = req.params.id;
const query = `SELECT * FROM users WHERE id = ${userId}`; // Vulnerable!
connection.query(query, (error, results) => {
if (error) {
console.error("Database error:", error);
return res.status(500).send("Error retrieving user data.");
}
res.json(results);
});
});
app.listen(3000, () => {
console.log('Server running on port 3000');
});
If a user navigates to /users/1, it works fine. But what if they go to /users/1 OR 1=1? The query string becomes SELECT * FROM users WHERE id = 1 OR 1=1. This query will return all users, not just the one with ID 1. This is the simplest form of SQL injection.
The mental model here is that SQL injection happens when user-supplied input is directly concatenated into a SQL query string, allowing attackers to manipulate the query’s logic. The database executes the entire string as SQL.
Here’s how parameterized queries flip this. Instead of building the query string with user input, you define placeholders and then provide the user input separately. The database driver then treats the provided input strictly as data, not as executable SQL code.
Let’s rewrite that route using parameterized queries:
const express = require('express');
const mysql = require('mysql');
const app = express();
const dbConfig = {
host: 'localhost',
user: 'root',
password: 'password',
database: 'mydatabase'
};
const connection = mysql.createConnection(dbConfig);
app.get('/users/:id', (req, res) => {
const userId = req.params.id;
// Notice the '?' placeholder
const query = 'SELECT * FROM users WHERE id = ?';
// The user ID is passed as an array in the second argument
connection.query(query, [userId], (error, results) => {
if (error) {
console.error("Database error:", error);
return res.status(500).send("Error retrieving user data.");
}
res.json(results);
});
});
app.listen(3000, () => {
console.log('Server running on port 3000');
});
Now, if an attacker tries /users/1 OR 1=1, the userId variable will be the string '1 OR 1=1'. The query sent to the database will be something like SELECT * FROM users WHERE id = '1 OR 1=1'. The database will look for a user whose ID is literally the string "1 OR 1=1", which won’t exist (assuming your id column is numeric). The attacker’s malicious SQL code is treated as a single, literal value.
The core problem parameterized queries solve is the separation of code and data. In the vulnerable example, user input is mixed directly into the SQL code. With parameterization, the database receives the SQL command structure separately from the values to be used within that structure. The driver then handles the "quoting" and "escaping" of the data values itself, ensuring they are interpreted as literal data, not as executable SQL commands. This is why it’s fundamentally more robust than manual string escaping, which is error-prone and easy to miss edge cases.
The exact mechanism varies slightly by database driver, but the principle is universal. For mysql (the Node.js driver), the ? is the placeholder. For other drivers or SQL dialects, you might see named parameters like :userId or $1. The key is that your application code never directly interpolates untrusted input into the SQL string itself.
When you’re using an ORM (Object-Relational Mapper) like Sequelize or TypeORM, they typically use parameterized queries under the hood for most operations. However, it’s still crucial to understand this underlying principle, especially when you need to drop down to raw SQL queries or when dealing with dynamic query construction. Even with ORMs, if you’re building complex queries or using features that allow raw SQL fragments, the risk of injection can re-emerge if not handled carefully.
The next logical step is understanding how to handle other types of SQL injection, such as those targeting LIKE clauses or involving data manipulation commands.