Parameterized Queries

Objectives

By the end of this lesson, you should be able to:

• Explain the difference between building a query with string concatenation and a parameterized query
• Write parameterized queries with pg’s $1, $2 placeholders
• Explain why parameterized queries are the default, not an optional extra

💡 Why this matters: Lesson 1’s examples used fixed values written directly into the SQL string. Real applications insert values a user typed, and building a query string out of user input directly is exactly how SQL injection happens.

⚠️ A note on verification: every snippet and every result shown below was actually run against a real PostgreSQL server.

Building a Query From User Input, the Unsafe Way

const username = req.body.username; // whatever the user typed

const result = await pool.query(
  `SELECT * FROM users WHERE username = '${username}'`
);

This looks harmless when username is "erin". It stops looking harmless the moment username is something else.

The Same Query, With Parameters

const username = req.body.username;

const result = await pool.query(
  'SELECT * FROM users WHERE username = $1',
  [username]
);

$1 is a placeholder, the second argument to pool.query() is an array of values, matched to the placeholders in order ($1, $2, and so on). pg sends the query text and the values to PostgreSQL separately, the value is never merged into the SQL string at all, so it’s never interpreted as SQL, no matter what it contains.

Why It Matters

const attackerInput = "nobody' OR '1'='1";

const unsafeQuery = `SELECT * FROM users WHERE username = '${attackerInput}'`;
console.log(unsafeQuery);
const unsafeResult = await pool.query(unsafeQuery);
console.log('Unsafe result:', unsafeResult.rows);

const safeResult = await pool.query('SELECT * FROM users WHERE username = $1', [attackerInput]);
console.log('Safe result:', safeResult.rows);

SELECT * FROM users WHERE username = 'nobody' OR '1'='1'
Unsafe result: [ { id: 1, username: 'erin', password: 'secret1' }, { id: 2, username: 'jordan', password: 'secret2' } ]
Safe result: []

The unsafe version turns attackerInput into part of the SQL itself, OR '1'='1' is always true, so the query matches every row in the table, a classic login-bypass injection. The parameterized version treats the exact same input as a literal string to search for, no user named that, so it correctly finds nothing.

This isn’t limited to reads, the same string could just as easily end a query early and start a destructive one:

const attackerInput = "'; DROP TABLE users; --";
const query = `SELECT * FROM users WHERE username = '${attackerInput}'`;
console.log(query);

SELECT * FROM users WHERE username = ''; DROP TABLE users; --'

Run that unsafe query against a real database, and the users table is genuinely dropped, an empty username match, followed by a second statement that deletes the entire table, followed by --, which comments out the trailing quote so the whole thing parses as valid SQL.

The Rule

Never build a SQL string with +, template literals, or any other form of string interpolation using a value that came from outside the code, user input, request bodies, query parameters, anything. Always use $1, $2 placeholders and pass values as the second argument to pool.query(). There is no case in this course, or in a real application, where concatenating a value into SQL is the right call.

Try It

1. Rewrite an unsafe, concatenated query from your own code (or the Module 1 exercises) to use $1 placeholders instead.
2. Reproduce the OR '1'='1' example above against your own users table, and confirm the unsafe version returns every row while the safe version returns none.
3. Explain, in your own words, why pg sending the query and the values separately is what actually prevents injection, not just “escaping quotes.”
4. Write a parameterized query with two placeholders ($1 and $2), filtering on two different columns.

Recap

• Parameterized queries ($1, $2, values passed as a separate array) send query text and values to PostgreSQL separately, values are never interpreted as SQL.
• String concatenation or template literals with outside input in a query is SQL injection waiting to happen, demonstrated above with both a login bypass and a dropped table.
• Every query in this course, from here on, uses parameterized queries whenever a value isn’t a fixed, hard-coded literal.

Next lesson: transactions, grouping multiple queries so they succeed or fail together.