Description
Inserting user input into a query's SQL text can let an attacker alter database operations. Dynamically constructed Knex queries are also vulnerable when input is concatenated into SQL instead of being passed as values.
Potential impact
- Sensitive database or user information may be exposed.
- Data may be modified or deleted without permission.
- Injection into authentication or authorization queries may bypass access controls. The overall impact depends on the connection account's permissions.
Remediation
- Pass inputs as bound values instead of concatenating them into SQL.
- Validate the type, format and length of every input used by a query.
- Use the value-binding features of methods such as
.insert()and.where(). If.raw()is needed, keep the SQL text fixed and supply bindings separately, as shown below.
Examples
Before
javascript
app.post('/user', async (req, res) => {
const result = await knex.raw(
`INSERT INTO users (name, email) VALUES ('${req.body.name}', '${req.body.email}')`
);
res.send('User added');
});
After
javascript
app.post('/user', async (req, res) => {
const result = await knex.raw(
'INSERT INTO users (name, email) VALUES (?, ?)',
[req.body.name, req.body.email]
);
res.send('User added');
});
The first excerpt interpolates the user's name and email directly into SQL. The second uses two ? bindings, so the inputs are values rather than SQL syntax. Separately validate their format and length, and confirm that the requester may perform the write.