Description
Combining external input directly with SQL, including through string concatenation or template literals, can let an attacker change the query. Unauthorized reads or changes are possible, with the impact limited by the database account's permissions and configuration.
Potential impact
- Sensitive data such as passwords may be exposed.
- Database records may be inserted, modified or deleted without permission.
- Malicious queries may disrupt the database or application.
Remediation
- Bind user input as parameters instead of inserting it directly into SQL.
- Validate the input's type, length and permitted characters.
- Select dynamic table or column names from a fixed allow-list where value binding cannot be used.
Examples
Before
javascript
const express = require('express');
const mssql = require('mssql');
app.get('/user', async (req, res) => {
const id = req.query.id;
const pool = mssql.connect(/* config */);
const request = (await pool).request();
// User input is inserted directly into the query
const result = await request.query(`SELECT * FROM users WHERE id = ${id}`);
res.json(result);
});
After
javascript
const express = require('express');
const mssql = require('mssql');
app.get('/user', async (req, res) => {
const id = parseInt(req.query.id, 10); // Integer conversion only: validate the input separately
const pool = mssql.connect(/* config */);
const request = (await pool).request();
// Bind the value as a parameter
request.input('userId', mssql.Int, id);
const result = await request.query('SELECT * FROM users WHERE id = @userId');
res.json(result);
});
The first excerpt inserts id directly into the query. The attacker may change its meaning within the connection account's permissions. The second binds the value to @userId, keeping it separate from SQL syntax. parseInt is not strict input validation: check the entire input format, NaN, the SQL Int range and the requester's access permissions. Connection configuration and error handling are omitted.