SQL injection vulnerability in Express.js applications where user-controlled request data is concatenated into SQL strings instead of using parameters, allowing attackers to manipulate database queries and potentially access or modify sensitive data.
const express = require('express');
const mysql = require('mysql2');
const app = express();
app.use(express.json());
const connection = mysql.createConnection({
host: 'localhost',
user: 'app_user',
password: 'password',
database: 'myapp'
});
// VULNERABLE: Template literal injection
app.get('/users/:id', (req, res) => {
const userId = req.params.id;
const query = `SELECT * FROM users WHERE id = \${userId}`;
connection.query(query, (error, results) => {
if (error) return res.status(500).send('Error');
res.json(results);
});
});
// VULNERABLE: String concatenation
app.post('/login', (req, res) => {
const { username, password } = req.body;
const sql = "SELECT * FROM users WHERE username = '" + username +
"' AND password = '" + password + "'";
connection.query(sql, (error, results) => {
if (error) return res.status(500).send('Error');
if (results.length > 0) {
res.json({ success: true, user: results[0] });
} else {
res.status(401).json({ success: false });
}
});
});
// VULNERABLE: Dynamic query building
app.get('/search', (req, res) => {
const { name, department, status } = req.query;
let sql = "SELECT * FROM users WHERE 1=1";
if (name) {
sql += " AND name LIKE '%" + name + "%'";
}
if (department) {
sql += " AND department = '" + department + "'";
}
if (status) {
sql += " AND status = '" + status + "'";
}
connection.query(sql, (error, results) => {
if (error) return res.status(500).send('Error');
res.json(results);
});
});
// Malicious inputs:
// GET /users/1 OR 1=1 --
// POST /login with username: admin' OR '1'='1' --
// GET /search?name='; DROP TABLE users; --const express = require('express');
const mysql = require('mysql2/promise');
const app = express();
app.use(express.json());
const pool = mysql.createPool({
host: 'localhost',
user: 'app_user',
password: 'password',
database: 'myapp',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// Input validation middleware
const validateUserId = (req, res, next) => {
const userId = parseInt(req.params.id);
if (isNaN(userId) || userId <= 0) {
return res.status(400).json({ error: 'Invalid user ID' });
}
req.params.id = userId;
next();
};
// SECURE: Parameterized query
app.get('/users/:id', validateUserId, async (req, res) => {
try {
const userId = req.params.id;
const query = 'SELECT id, username, email, department FROM users WHERE id = ?';
const [results] = await pool.execute(query, [userId]);
if (results.length === 0) {
return res.status(404).json({ error: 'User not found' });
}
res.json(results[0]);
} catch (error) {
console.error('Database error:', error);
res.status(500).json({ error: 'Database error' });
}
});
// SECURE: Parameterized query with validation
app.post('/login', async (req, res) => {
try {
const { username, password } = req.body;
// Input validation
if (!username || !password) {
return res.status(400).json({ error: 'Username and password required' });
}
if (username.length > 50 || password.length > 100) {
return res.status(400).json({ error: 'Invalid input length' });
}
const query = 'SELECT id, username FROM users WHERE username = ? AND password = ?';
const [results] = await pool.execute(query, [username, password]);
if (results.length > 0) {
res.json({ success: true, user: results[0] });
} else {
res.status(401).json({ success: false, error: 'Invalid credentials' });
}
} catch (error) {
console.error('Login error:', error);
res.status(500).json({ error: 'Login failed' });
}
});
// SECURE: Parameterized query with validation
app.get('/search', async (req, res) => {
try {
const { name, department, status } = req.query;
const params = [];
let sql = "SELECT id, username, email, department, status FROM users WHERE 1=1";
// Validate and build query safely
if (name && typeof name === 'string') {
if (name.length > 100) {
return res.status(400).json({ error: 'Name search term too long' });
}
sql += " AND name LIKE ?";
params.push(`%\${name}%`);
}
if (department) {
const validDepartments = ['IT', 'HR', 'Finance', 'Marketing', 'Sales'];
if (!validDepartments.includes(department)) {
return res.status(400).json({ error: 'Invalid department' });
}
sql += " AND department = ?";
params.push(department);
}
if (status) {
const validStatuses = ['active', 'inactive', 'pending'];
if (!validStatuses.includes(status)) {
return res.status(400).json({ error: 'Invalid status' });
}
sql += " AND status = ?";
params.push(status);
}
const [results] = await pool.execute(sql, params);
res.json(results);
} catch (error) {
console.error('Search error:', error);
res.status(500).json({ error: 'Search failed' });
}
});
app.listen(3000, () => {
console.log('Server running on port 3000');
});The vulnerable code uses template literals and string concatenation to build SQL queries with user input. The fixed version uses parameterized queries with ? placeholders, implements proper input validation, and uses async/await with connection pooling for better performance.
User-controlled request data is concatenated into SQL strings instead of using parameters.
Sourcery automatically identifies sql injection from http request data in sql string in express and many other security issues in your codebase.