Description
Inserting user input directly into an SQL query lets an attacker alter its structure. Malicious SQL may bypass authentication or enable unauthorized data access, modification or deletion.
Potential impact
- Sensitive database contents may be read, changed or deleted without permission.
- Injection into login or permission-check queries may bypass access controls.
- Destructive queries or changes to the database structure may disrupt the service.
Remediation
- Use parameter binding or prepared statements for query values.
- Do not insert user input directly into the SQL string.
- Use the ORM's value-binding query methods.
- Validate the input's format, length and permitted values, and reject unexpected input.
Examples
conn is an existing MySQL2 Promise connection or pool. Connection creation, error handling and request-specific access checks are omitted.
Before
javascript
const mysql = require('mysql2');
async function getUserById(req, res) {
let userId = req.query.userId;
let sql = "SELECT * FROM users WHERE id = " + userId; // Input is inserted directly into SQL
const [rows, fields] = await conn.execute(sql);
res.json(rows);
}
After
javascript
const mysql = require('mysql2');
async function getUserById(req, res) {
let userId = req.query.userId;
// Bind the value as a parameter
const [rows, fields] = await conn.execute(
"SELECT * FROM users WHERE id = ?", [userId]
);
res.json(rows);
}
The first excerpt concatenates userId into SQL and exposes the query to manipulation. Passing fixed SQL and a separate value array to execute binds the value to the prepared statement's ? placeholder. The value is handled separately from SQL syntax. Input validation and permission to access that user's data remain separate checks.