Description
Concatenating user input directly into SQL lets an attacker change the query's structure and execute unintended database operations. Depending on how the query is used, this can bypass authentication, expose data or delete records.
Potential impact
- Malicious input may bypass authentication queries.
- Unauthorized users may read sensitive information.
- Attackers may insert, modify or delete database records.
Remediation
- Bind values as query parameters instead of concatenating them into SQL.
- Validate inputs against their expected types and formats, such as numbers or email addresses.
- Use value-binding APIs even with an ORM or query builder. Select table and column names from a fixed allow-list because value parameters cannot represent them.
Examples
These excerpts show only connection and query handling. In CommonJS, place this code inside an async function and add error handling and connection cleanup.
Before
javascript
const { Client } = require('pg')
const client = new Client()
await client.connect()
let query = 'SELECT * FROM users WHERE id = ' + userInput;
const res = await client.query(query);
await client.end();
After
javascript
const { Client } = require('pg')
const client = new Client()
await client.connect()
const res = await client.query('SELECT * FROM users WHERE id = $1', [userInput]);
await client.end();
The first excerpt concatenates userInput into SQL, allowing it to change the query. The second binds the input to $1 so it is handled as a value rather than SQL syntax.