Node.js 41 🟢 Preventing SQL Injection with Parameterized Queries
SQL injection is the most persistent vulnerability in web applications. It has been in the OWASP Top 10 since the list was created, and it remains there because the underlying mistake is so easy to make. The mistake is string concatenation: building a query by pasting user input into a template. The input is treated as SQL, not as data, so a value like ' OR '1'='1 changes the meaning of the query. The fix is parameterized queries, which separate the SQL template from the values. The database parses the template with placeholders, and the values are bound as data. The input can never change the query’s structure, no matter what it contains.
The principle is not specific to any database. PostgreSQL, MySQL, SQLite, SQL Server, and Oracle all support parameterized queries, and every Node.js driver exposes them. The syntax differs — $1 for PostgreSQL, ? for MySQL and SQLite — but the mechanism is the same. The driver sends the template and the parameters separately, and the database handles the substitution. The parameter is never concatenated into the SQL string, so the injection is structurally impossible.
The most common mistake is not the absence of parameterization but its partial application. A developer parameterizes the WHERE clause but concatenates the ORDER BY column, the LIMIT value, or the table name. These are the cases where the parameterization is not possible with the standard placeholder syntax, because the database must know the column or the table at parse time. The fix is to validate the input against an allowlist: the column name must be one of the known columns, and the table name must be one of the known tables. The allowlist is the only correct approach for identifiers, and it is a small amount of code.
This chapter covers three areas. First, why SQL injection exists — the string-concatenation problem and the difference between data and structure. Second, how parameterized queries work in the major drivers — the pg driver with $1 placeholders, the mysql2 driver with ? placeholders, and the better-sqlite3 driver with its prepared statements. Third, the cases where parameterization does not apply — the identifiers (table names, column names), the ORDER BY direction, and the LIMIT value, and the allowlist pattern that handles them. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the injection and the prevention.
Key point: Parameterized queries separate the SQL template from the values. The template contains placeholders ($1 for PostgreSQL, ? for MySQL), and the values are passed as an array. The database parses the template and binds the values as data, so the input can never change the query’s structure. Identifiers (table names, column names) and the ORDER BY direction cannot be parameterized; they must be validated against an allowlist.
Why SQL injection exists
The string-concatenation problem. A query is a string, and a developer builds it by concatenating the parts. The parts include the SQL keywords, the table and column names, and the user input. The user input is data, but the string concatenation does not distinguish it from the SQL. If the input contains a quote, the quote closes the string literal in the SQL, and the rest of the input is interpreted as SQL. The classic example is ' OR '1'='1, which turns a WHERE email = 'input' clause into WHERE email = '' OR '1'='1', which is always true.
The data-versus-structure problem. The SQL query has two parts: the structure (the keywords, the table names, the column names, the operators) and the data (the values). The structure is determined by the developer, and the data comes from the user. The injection happens when the data is treated as structure. The parameterized query separates the two: the template is the structure, and the parameters are the data. The database parses the template once, and the parameters are bound to the placeholders. The data can never become structure.
The defense-in-depth problem. Parameterization is the primary defense, but it is not the only one. The principle of least privilege limits the damage: the application’s database user should have only the permissions it needs, not DROP TABLE or GRANT. The input validation rejects the inputs that do not match the expected format, which reduces the attack surface. The error handling hides the database errors from the user, which prevents the attacker from learning the schema. The parameterization is the first line, and the other measures are the layers behind it.
The ORM problem. An ORM like Prisma, TypeORM, or Sequelize generates parameterized queries for the common operations. The developer does not write the SQL, so the injection is prevented by default. The problem is the raw query escape hatch: prisma.$queryRaw, sequelize.query, and the equivalent methods in other ORMs. The escape hatch takes a SQL string, and if the developer concatenates the input into the string, the injection is possible. The rule is the same in the ORM: use the parameterized form, not the string concatenation.
The dynamic-query problem. Some queries are genuinely dynamic. The user chooses the sort column, the filter field, or the table. The parameterization cannot handle these cases, because the identifier must be known at parse time. The naive fix is to concatenate the identifier into the SQL, which reopens the injection. The correct fix is the allowlist: the input is checked against a list of known identifiers, and the query uses the matched identifier. If the input does not match, the query is rejected.
The trade-off. Parameterized queries are slightly more verbose than string concatenation. The query is a template with placeholders, and the parameters are a separate array. The developer must keep the two in sync, and a mismatch is a runtime error. The allowlist for the identifiers is additional code. The trade-off is between the verbosity of the safe approach and the convenience of the unsafe one. For the injection-prone cases, the verbosity is a small price for the security.
a. Parameterized queries with the pg driver
The pg driver uses $1, $2 placeholders. The query method takes the SQL template and the parameters array.
const { Pool } = require('pg');
const pool = new Pool();
async function findUser(email) {
const result = await pool.query(
'SELECT id, name, email FROM users WHERE email = $1',
[email]
);
return result.rows[0];
}The $1 placeholder is replaced with the first element of the parameters array. The driver sends the template and the parameters separately, and PostgreSQL binds the value. The email value is treated as data, no matter what it contains.
The same pattern applies to INSERT, UPDATE, and DELETE:
async function createUser(name, email) {
const result = await pool.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name',
[name, email]
);
return result.rows[0];
}
async function updateUser(id, name) {
await pool.query(
'UPDATE users SET name = $1 WHERE id = $2',
[name, id]
);
}
async function deleteUser(id) {
await pool.query('DELETE FROM users WHERE id = $1', [id]);
}The RETURNING clause returns the inserted row, and the parameters are bound in the order they appear in the template. The placeholder numbering is by position, and the same placeholder can be used multiple times:
await pool.query(
'SELECT * FROM users WHERE email = $1 OR backup_email = $1',
[email]
);The $1 placeholder is used twice, and the single parameter is bound to both occurrences.
The LIKE clause. A LIKE pattern needs the wildcards, and the wildcards are part of the value, not the SQL:
await pool.query(
'SELECT * FROM users WHERE name LIKE $1',
[`%${search}%`]
);The % characters are part of the parameter value, and the database treats them as the LIKE wildcards. The search value is still data, and a quote in it cannot escape the string literal.
The IN clause. A list of values needs a placeholder per element, and the number of placeholders is dynamic:
async function findUsersByIds(ids) {
const placeholders = ids.map((_, i) => `$${i + 1}`).join(', ');
const result = await pool.query(
`SELECT * FROM users WHERE id IN (${placeholders})`,
ids
);
return result.rows;
}The placeholders string is built from the array’s length, and the ids array is passed as the parameters. The placeholders are generated by the code, not by the user input, so the injection is not possible. The ids values are bound as data.
b. Parameterized queries with the mysql2 driver
The mysql2 driver uses ? placeholders. The execute method uses prepared statements, which are the fastest and the safest form.
const mysql = require('mysql2/promise');
const pool = mysql.createPool({ /* config */ });
async function findUser(email) {
const [rows] = await pool.execute(
'SELECT id, name, email FROM users WHERE email = ?',
[email]
);
return rows[0];
}The ? placeholder is replaced with the first element of the parameters array. The execute method uses a server-side prepared statement, which parses the template once and reuses the plan. The parameters are bound as data.
The INSERT with RETURNING is not supported in MySQL, so the insert uses the insertId from the result:
async function createUser(name, email) {
const [result] = await pool.execute(
'INSERT INTO users (name, email) VALUES (?, ?)',
[name, email]
);
return { id: result.insertId, name, email };
}The result.insertId is the generated ID, and the result.affectedRows is the number of rows affected.
The difference between query and execute. Both methods accept placeholders, but execute uses a prepared statement and query escapes the values client-side. The execute method is faster for repeated queries and is the recommended choice. The query method is for the one-off queries where the prepared statement overhead is not worth it.
The IN clause. The mysql2 driver does not support an array parameter directly. The placeholders must be generated:
async function findUsersByIds(ids) {
const placeholders = ids.map(() => '?').join(', ');
const [rows] = await pool.execute(
`SELECT * FROM users WHERE id IN (${placeholders})`,
ids
);
return rows;
}The placeholders string is built from the array’s length, and the ids array is passed as the parameters. The placeholders are generated by the code, and the values are bound as data.
c. The cases where parameterization does not apply
The parameterization works for the values, but not for the identifiers. A table name, a column name, and the ORDER BY direction are part of the SQL structure, and the database must know them at parse time. The placeholder syntax cannot represent them. The fix is the allowlist: the input is checked against a list of known identifiers, and the query uses the matched identifier.
The ORDER BY column. The user chooses the sort column from a list. The column name is validated against the allowlist:
const SORT_COLUMNS = {
name: 'name',
email: 'email',
created_at: 'created_at',
};
async function findUsersSorted(sortBy, order) {
const column = SORT_COLUMNS[sortBy];
if (!column) {
throw new Error('Invalid sort column');
}
const direction = order === 'desc' ? 'DESC' : 'ASC';
const result = await pool.query(
`SELECT * FROM users ORDER BY ${column} ${direction}`,
[]
);
return result.rows;
}The SORT_COLUMNS object maps the user-facing names to the actual column names. The sortBy value is looked up in the object, and an invalid value throws an error. The column is a known string, not the user input. The direction is normalized to ASC or DESC, so the user cannot inject a subquery into the ORDER BY clause.
The table name. The same pattern applies to the table name:
const TABLES = {
users: 'users',
orders: 'orders',
products: 'products',
};
async function countRows(tableName) {
const table = TABLES[tableName];
if (!table) {
throw new Error('Invalid table');
}
const result = await pool.query(`SELECT COUNT(*) FROM ${table}`);
return result.rows[0].count;
}The TABLES object is the allowlist, and the table is a known string. The user cannot inject a different table or a subquery.
The LIMIT value. The LIMIT value is a number, and it can be validated as an integer:
async function findUsers(limit) {
const n = parseInt(limit, 10);
if (!Number.isInteger(n) || n < 1 || n > 100) {
throw new Error('Invalid limit');
}
const result = await pool.query(`SELECT * FROM users LIMIT $1`, [n]);
return result.rows;
}The LIMIT value can be parameterized in PostgreSQL and MySQL, so the $1 or ? placeholder is used. The value is validated as an integer before it is passed. The validation is the defense in depth: the parameterization handles the value, and the range check rejects the unreasonable values.
The ORDER BY direction. The direction is ASC or DESC, and it cannot be parameterized. The fix is to normalize the input:
const direction = order.toLowerCase() === 'desc' ? 'DESC' : 'ASC';The input is compared to the known values, and the result is a known string. Any input that is not desc becomes ASC, which is the safe default.
Complete Example Session
// ============================================
// PART 1: THE VULNERABLE QUERY
// ============================================
// ❌ VULNERABLE: string concatenation
async function findUserVulnerable(email) {
const result = await pool.query(
`SELECT * FROM users WHERE email = '${email}'`
);
return result.rows[0];
}
// Input: "' OR '1'='1"
// Query: SELECT * FROM users WHERE email = '' OR '1'='1'
// Result: all users returned
// ============================================
// PART 2: THE PARAMETERIZED QUERY
// ============================================
// ✅ SAFE: parameterized
async function findUser(email) {
const result = await pool.query(
'SELECT * FROM users WHERE email = $1',
[email]
);
return result.rows[0];
}
// Input: "' OR '1'='1"
// Query: SELECT * FROM users WHERE email = $1
// Parameters: ["' OR '1'='1"]
// Result: no users returned
// ============================================
// PART 3: INSERT WITH RETURNING
// ============================================
async function createUser(name, email) {
const result = await pool.query(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name',
[name, email]
);
return result.rows[0];
}
// ============================================
// PART 4: UPDATE AND DELETE
// ============================================
async function updateUser(id, name) {
await pool.query(
'UPDATE users SET name = $1 WHERE id = $2',
[name, id]
);
}
async function deleteUser(id) {
await pool.query('DELETE FROM users WHERE id = $1', [id]);
}
// ============================================
// PART 5: THE LIKE CLAUSE
// ============================================
async function searchUsers(search) {
const result = await pool.query(
'SELECT * FROM users WHERE name LIKE $1',
[`%${search}%`]
);
return result.rows;
}
// The % wildcards are part of the parameter value.
// ============================================
// PART 6: THE IN CLAUSE
// ============================================
async function findUsersByIds(ids) {
const placeholders = ids.map((_, i) => `$${i + 1}`).join(', ');
const result = await pool.query(
`SELECT * FROM users WHERE id IN (${placeholders})`,
ids
);
return result.rows;
}
// The placeholders are generated from the array length.
// The ids are bound as data.
// ============================================
// PART 7: THE ORDER BY ALLOWLIST
// ============================================
const SORT_COLUMNS = {
name: 'name',
email: 'email',
created_at: 'created_at',
};
async function findUsersSorted(sortBy, order) {
const column = SORT_COLUMNS[sortBy];
if (!column) {
throw new Error('Invalid sort column');
}
const direction = order === 'desc' ? 'DESC' : 'ASC';
const result = await pool.query(
`SELECT * FROM users ORDER BY ${column} ${direction}`
);
return result.rows;
}
// ============================================
// PART 8: THE MYSQL2 VERSION
// ============================================
const mysql = require('mysql2/promise');
async function findUserMySQL(email) {
const [rows] = await pool.execute(
'SELECT * FROM users WHERE email = ?',
[email]
);
return rows[0];
}
async function createUserMySQL(name, email) {
const [result] = await pool.execute(
'INSERT INTO users (name, email) VALUES (?, ?)',
[name, email]
);
return { id: result.insertId, name, email };
}
// ============================================
// PART 9: THE TRANSACTION
// ============================================
async function transferFunds(fromId, toId, amount) {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[amount, fromId]
);
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[amount, toId]
);
await client.query('COMMIT');
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}
}
// The parameters are bound in every query.
// The transaction is scoped to the checked-out client.
// ============================================
// PART 10: THE COMPLETE EXAMPLE
// ============================================
const express = require('express');
const app = express();
app.use(express.json());
app.get('/users', async (req, res) => {
const { search, sortBy = 'name', order = 'asc', limit = 20 } = req.query;
const column = SORT_COLUMNS[sortBy];
if (!column) {
return res.status(400).json({ error: 'Invalid sort column' });
}
const direction = order === 'desc' ? 'DESC' : 'ASC';
const n = parseInt(limit, 10);
if (!Number.isInteger(n) || n < 1 || n > 100) {
return res.status(400).json({ error: 'Invalid limit' });
}
const result = await pool.query(
`SELECT * FROM users
WHERE name LIKE $1
ORDER BY ${column} ${direction}
LIMIT $2`,
[`%${search}%`, n]
);
res.json(result.rows);
});
app.listen(3000);The ten parts show the vulnerable query, the parameterized query, INSERT with RETURNING, UPDATE and DELETE, the LIKE clause, the IN clause, the ORDER BY allowlist, the mysql2 version, the transaction, and the complete example.
Quick Reference
The Two Drivers
| Driver | Placeholder | Method |
|---|---|---|
pg | $1, $2 | pool.query(sql, params) |
mysql2 | ? | pool.execute(sql, params) |
better-sqlite3 | ? | stmt.run(params) |
The Parameterized Patterns
| Case | Pattern |
|---|---|
WHERE value | WHERE email = $1, [email] |
INSERT | VALUES ($1, $2), [name, email] |
UPDATE | SET name = $1 WHERE id = $2, [name, id] |
DELETE | WHERE id = $1, [id] |
LIKE | WHERE name LIKE $1, [%${search}%] |
IN | IN ($1, $2, ...), ids |
The Allowlist Patterns
| Case | Pattern |
|---|---|
ORDER BY column | SORT_COLUMNS[sortBy] |
| Table name | TABLES[tableName] |
ORDER BY direction | order === 'desc' ? 'DESC' : 'ASC' |
LIMIT value | parseInt and range check |
The Injection Inputs
| Input | Effect if concatenated |
|---|---|
' OR '1'='1 | Always-true condition |
'; DROP TABLE users; -- | Drops the table |
' UNION SELECT ... -- | Returns other data |
' AND 1=1 -- | Comment out the rest |
Best Practices
✅ Do This:
// Use placeholders for the values
pool.query('SELECT * FROM users WHERE email = $1', [email]); // ✅
// Use the allowlist for the identifiers
const column = SORT_COLUMNS[sortBy]; // ✅
// Use parseInt and a range check for LIMIT
const n = parseInt(limit, 10); // ✅
// Use execute for mysql2
pool.execute('SELECT * FROM users WHERE email = ?', [email]); // ✅
// Use the RETURNING clause for the inserted row
'INSERT INTO users (name) VALUES ($1) RETURNING id' // ✅
// Use the least-privilege database user
// GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'%' // ✅❌ Don’t Do This:
// Don't concatenate the user input
`SELECT * FROM users WHERE email = '${email}'` // ❌
// Don't concatenate the ORDER BY column
`ORDER BY ${sortBy}` // ❌
// Don't concatenate the table name
`SELECT * FROM ${table}` // ❌
// Don't trust the LIMIT value
`LIMIT ${limit}` // ❌
// Don't use the query method with unescaped values in mysql2
pool.query(`SELECT * FROM users WHERE email = '${email}'`) // ❌
// Don't grant the application superuser
// GRANT ALL PRIVILEGES ON *.* TO 'app'@'%' // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
Injection in ORDER BY | The column is concatenated | Use the allowlist |
| Injection in the table name | The table is concatenated | Use the allowlist |
Injection in LIMIT | The value is concatenated | Use parseInt and a range check |
| Placeholder mismatch | The parameter count differs | Count the placeholders |
| Array parameter unsupported | The driver does not support it | Generate the placeholders |
LIKE wildcards missing | The % is not in the value | Add the wildcards to the parameter |
| The ORM escape hatch | The raw query concatenates | Use the parameterized form |
Real-World Examples
1. Parameterized SELECT
pool.query('SELECT * FROM users WHERE email = $1', [email]);2. Parameterized INSERT
pool.query('INSERT INTO users (name) VALUES ($1)', [name]);3. Parameterized UPDATE
pool.query('UPDATE users SET name = $1 WHERE id = $2', [name, id]);4. Parameterized DELETE
pool.query('DELETE FROM users WHERE id = $1', [id]);5. The LIKE Clause
pool.query('SELECT * FROM users WHERE name LIKE $1', [`%${search}%`]);6. The IN Clause
const placeholders = ids.map((_, i) => `$${i + 1}`).join(', ');
pool.query(`SELECT * FROM users WHERE id IN (${placeholders})`, ids);7. The ORDER BY Allowlist
const column = SORT_COLUMNS[sortBy];8. The Direction
const direction = order === 'desc' ? 'DESC' : 'ASC';9. The LIMIT Validation
const n = parseInt(limit, 10);10. The mysql2 Version
pool.execute('SELECT * FROM users WHERE email = ?', [email]);Visual
The Injection
┌──────────────────────────────────────────────────────────────┐
│ THE INJECTION │
│ │
│ SAFE INPUT: alice@example.com │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Query: SELECT * FROM users WHERE email = '...' │ │
│ │ Result: the user with that email, or none │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ MALICIOUS INPUT: ' OR '1'='1 │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Query: SELECT * FROM users │ │
│ │ WHERE email = '' OR '1'='1' │ │
│ │ │ │
│ │ The WHERE clause is always true. │ │
│ │ Result: ALL users. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ MALICIOUS INPUT: '; DROP TABLE users; -- │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Query: SELECT * FROM users WHERE email = ''; │ │
│ │ DROP TABLE users; --' │ │
│ │ │ │
│ │ The table is dropped. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Parameterized Query
┌──────────────────────────────────────────────────────────────┐
│ THE PARAMETERIZED QUERY │
│ │
│ Template: SELECT * FROM users WHERE email = $1 │
│ Parameter: ["' OR '1'='1"] │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The database parses the template. │ │
│ │ The $1 is a placeholder for a value. │ │
│ │ │ │
│ │ The parameter is bound to the placeholder. │ │
│ │ The value is treated as DATA, not as SQL. │ │
│ │ │ │
│ │ Result: no users (the literal string is not found). │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The input can never change the query's structure. │ │
│ │ The quote is just a character in the string. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Allowlist for Identifiers
┌──────────────────────────────────────────────────────────────┐
│ THE ALLOWLIST FOR IDENTIFIERS │
│ │
│ const SORT_COLUMNS = { │
│ name: 'name', │
│ email: 'email', │
│ created_at: 'created_at', │
│ }; │
│ │
│ Input: sortBy = 'name' │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ column = SORT_COLUMNS['name'] = 'name' │ │
│ │ The column is a known string. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Input: sortBy = 'name; DROP TABLE users; --' │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ column = SORT_COLUMNS['name; DROP TABLE users; --'] │ │
│ │ = undefined │ │
│ │ │ │
│ │ throw new Error('Invalid sort column') │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The allowlist rejects the malicious input. │
│ The query uses only the known column names. │
│ │
└──────────────────────────────────────────────────────────────┘The Defense in Depth
┌──────────────────────────────────────────────────────────────┐
│ THE DEFENSE IN DEPTH │
│ │
│ Layer 1: PARAMETERIZATION │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The values are bound as data. │ │
│ │ The input cannot change the query's structure. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Layer 2: ALLOWLIST │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The identifiers are validated against a known list. │ │
│ │ The invalid identifiers are rejected. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Layer 3: LEAST PRIVILEGE │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The application's database user has only the │ │
│ │ permissions it needs. │ │
│ │ No DROP, no GRANT, no access to other schemas. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Layer 4: ERROR HANDLING │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The database errors are logged, not returned. │ │
│ │ The attacker cannot learn the schema. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ No single layer is perfect. The combination is. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| SQL injection | The input is treated as SQL, not as data |
| Parameterized query | The template and the values are sent separately |
pg placeholder | $1, $2 |
mysql2 placeholder | ? |
LIKE clause | The % is part of the value |
IN clause | The placeholders are generated |
ORDER BY column | Validated with an allowlist |
| Table name | Validated with an allowlist |
LIMIT value | parseInt and a range check |
| Least privilege | The application’s DB user has minimal permissions |
Key takeaways:
- SQL injection is the string-concatenation problem. The input is pasted into the SQL string, and the input is treated as SQL. A quote in the input closes the string literal and the rest is interpreted as SQL. The injection is structurally impossible with parameterized queries.
- The parameterized query separates the template from the values. The template contains placeholders (
$1for PostgreSQL,?for MySQL), and the values are passed as an array. The database parses the template and binds the values as data. - The
pgdriver uses$1,$2placeholders. Thepool.query(sql, params)method takes the template and the parameters array. TheRETURNINGclause returns the inserted row. - The
mysql2driver uses?placeholders. Thepool.execute(sql, params)method uses a server-side prepared statement. Theresult.insertIdis the generated ID. - The
LIKEclause puts the wildcards in the value. The%characters are part of the parameter, not the SQL. The database treats them as theLIKEwildcards. - The
INclause generates the placeholders. The placeholder string is built from the array’s length, and the array is passed as the parameters. The placeholders are generated by the code, not the user input. - The identifiers cannot be parameterized. A table name, a column name, and the
ORDER BYdirection are part of the SQL structure. The allowlist is the correct approach: the input is checked against a list of known identifiers, and the query uses the matched identifier. - The defense in depth is the layers behind the parameterization. The least-privilege database user, the input validation, and the error handling reduce the damage if the parameterization is bypassed or the ORM’s escape hatch is misused.
Remember: SQL injection is prevented by parameterized queries, which separate the SQL template from the values. The template contains placeholders, and the values are bound as data. The pg driver uses $1, the mysql2 driver uses ?, and the mechanism is the same. The values are safe, but the identifiers are not: the table names, the column names, and the ORDER BY direction cannot be parameterized, and they must be validated against an allowlist. The defense in depth adds the least-privilege database user, the input validation, and the error handling. The result is an application where the user input can never change the query’s structure, no matter what it contains.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!