Query parameterization for safety in Node.js - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use query parameterization?

Why is query parameterization important when working with databases in Node.js?

A It prevents SQL injection by separating code from data inputs.

B It encrypts the database connection for security.

C It allows queries to be written without any syntax rules.

D It makes queries run faster by caching results automatically.

💡 Hint

Think about how user input can affect database commands.

Query parameterization helps keep user data separate from SQL commands, stopping attackers from injecting harmful code.

❓ component_behavior

intermediate

Output of parameterized query execution

Given the following Node.js code using a parameterized query, what will be the output if the user input is "O'Reilly"?

Node.js

const userInput = "O'Reilly";
const query = 'SELECT * FROM authors WHERE last_name = ?';
db.execute(query, [userInput], (err, results) => {
  if (err) throw err;
  console.log(results);
});

A The query throws a syntax error due to the apostrophe in the input.

B The query runs successfully and returns authors with last name O'Reilly.

C The query returns no results because the input is ignored.

D The query causes a runtime error because parameters are not supported.

💡 Hint

How does parameterization handle special characters like apostrophes?

Parameterization safely escapes special characters, so the query runs without syntax errors and returns matching results.

📝 Syntax

advanced

Identify the correct parameterized query syntax

Which of the following Node.js code snippets correctly uses parameterized queries with the mysql2 library?

A db.execute('SELECT * FROM users WHERE id = $1', userId, callback);

B db.query('SELECT * FROM users WHERE id = ' + userId, callback);

C db.execute('SELECT * FROM users WHERE id = ?', [userId], callback);

D db.query('SELECT * FROM users WHERE id = ?', userId);

💡 Hint

Check how parameters are passed as an array in mysql2.

mysql2 uses '?' placeholders and an array of values for parameters in the execute method.

🔧 Debug

advanced

Find the error in this parameterized query code

What error will occur when running this Node.js code snippet using mysql2?

Node.js

const userId = 5;
db.execute('SELECT * FROM users WHERE id = ?', userId, (err, results) => {
  if (err) throw err;
  console.log(results);
});

A ReferenceError because userId is not defined.

B No error; the query runs successfully.

C SyntaxError due to missing semicolon after the query string.

D TypeError because parameters must be passed as an array, not a single value.

💡 Hint

Check the type of the second argument to execute().

mysql2 expects parameters as an array. Passing a single value causes a TypeError.

❓ state_output

expert

Result of multiple parameterized queries with async/await

Consider this async function using mysql2's promise API. What will be logged to the console?

Node.js

async function getUserData(userId) {
  const [rows1] = await db.execute('SELECT * FROM users WHERE id = ?', [userId]);
  const [rows2] = await db.execute('SELECT * FROM orders WHERE user_id = ?', [userId]);
  console.log(rows1.length, rows2.length);
}

getUserData(10);

A Two numbers showing how many users and orders exist for userId 10.

B An error because multiple awaits cannot be used in the same function.

C Undefined because rows1 and rows2 are not arrays.

D Zero and zero because the queries do not run without a callback.

💡 Hint

Think about how async/await works with promise-based query execution.

Each await pauses until the query completes, returning arrays of rows. The console logs the counts of results.

Practice

1. Why is query parameterization important in Node.js database queries?

easy

A. It prevents SQL injection by separating user input from the query structure.

B. It makes queries run faster by caching results automatically.

C. It allows queries to be written without any syntax rules.

D. It automatically encrypts the database connection.

Solution

1. Step 1: Understand SQL injection risk

   When user input is directly included in query strings, attackers can inject harmful SQL code.

2. Step 2: Role of parameterization

   Parameterization sends user input separately, so the database treats it only as data, not code.

3. Final Answer:

   It prevents SQL injection by separating user input from the query structure. -> Option A

4. Quick Check:

   Query parameterization = prevents SQL injection [OK]

Hint: Parameterization separates data from code to block injections [OK]

Common Mistakes:

• Thinking parameterization speeds up queries automatically
• Believing it removes all syntax rules
• Confusing parameterization with encryption

2. Which of the following is the correct way to use parameterized queries with the 'mysql2' library in Node.js?

easy

A. connection.query('SELECT * FROM users WHERE id = ' + userId);

B. connection.query('SELECT * FROM users WHERE id = $userId');

C. connection.query('SELECT * FROM users WHERE id = ${userId}');

D. connection.query('SELECT * FROM users WHERE id = ?', [userId]);

Solution

1. Step 1: Identify parameter placeholder syntax

   The 'mysql2' library uses '?' as placeholders for parameters.

2. Step 2: Check correct parameter passing

   Parameters must be passed as an array as the second argument to 'query'.

3. Final Answer:

   connection.query('SELECT * FROM users WHERE id = ?', [userId]); -> Option D

4. Quick Check:

   mysql2 uses '?' + array for parameters [OK]

Hint: Use '?' and array for parameters in mysql2 queries [OK]

Common Mistakes:

• Concatenating variables directly into query strings
• Using template literals inside query strings
• Using incorrect placeholder syntax like $userId

3. What will be the output of this Node.js code snippet using 'pg' (PostgreSQL) library?

const { Client } = require('pg');
const client = new Client();
await client.connect();
const userId = "1; DROP TABLE users;";
const res = await client.query('SELECT * FROM users WHERE id = $1', [userId]);
console.log(res.rows.length);

medium

A. It will return the number of rows with id exactly matching "1; DROP TABLE users;".

B. It will throw a syntax error due to SQL injection.

C. It will delete the users table and return zero.

D. It will return all rows from the users table.

Solution

1. Step 1: Understand parameterization effect

   The query uses parameterized input with '$1', so the input is treated as data, not code.

2. Step 2: Analyze input impact

   The string "1; DROP TABLE users;" is searched as a literal id, which likely matches no rows.

3. Final Answer:

   It will return the number of rows with id exactly matching "1; DROP TABLE users;". -> Option A

4. Quick Check:

   Parameterized queries treat input as data, not commands [OK]

Hint: Parameterized queries treat input literally, preventing injection [OK]

Common Mistakes:

• Assuming injection will run despite parameterization
• Expecting syntax errors from malicious input
• Thinking all rows will be returned without a WHERE clause

4. Identify the error in this Node.js code using 'sqlite3' for parameterized queries:

db.run('INSERT INTO users (name, age) VALUES (?, ?)', name, age, (err) => {
  if (err) throw err;
  console.log('User added');
});

medium

A. The 'db.run' method cannot accept parameters.

B. Parameters should be passed as an array, not separate arguments.

C. The query string should use named parameters like ':name' instead of '?'.

D. The callback function is missing an error parameter.

Solution

1. Step 1: Check parameter passing syntax

   sqlite3 expects parameters as an array or object, not separate arguments.

2. Step 2: Correct usage

   Parameters should be passed as an array: [name, age].

3. Final Answer:

   Parameters should be passed as an array, not separate arguments. -> Option B

4. Quick Check:

   sqlite3 parameters = array [OK]

Hint: Pass parameters as array in sqlite3 queries [OK]

Common Mistakes:

• Passing parameters as separate arguments
• Confusing named and positional parameters
• Ignoring callback error handling

5. You want to safely insert multiple user records in a batch using Node.js and 'pg' library. Which approach correctly uses query parameterization to avoid SQL injection?

hard

A. Run separate queries for each user without parameterization.

B. Build a single query string by concatenating all user values directly.

C. Use a parameterized query with multiple placeholders and pass all values in an array.

D. Use template literals to insert user data inside the query string.

Solution

1. Step 1: Understand batch insertion with parameterization

   To insert multiple records safely, use placeholders for each value and pass all values as an array.

2. Step 2: Avoid unsafe concatenation or unparameterized queries

   Concatenating strings or using template literals risks injection; separate queries without parameters are inefficient and unsafe.

3. Final Answer:

   Use a parameterized query with multiple placeholders and pass all values in an array. -> Option C

4. Quick Check:

   Batch insert = parameterized placeholders + values array [OK]

Hint: Use placeholders and pass all values as array for batch inserts [OK]

Common Mistakes:

• Concatenating user data into query strings
• Running unparameterized queries repeatedly
• Using template literals for SQL queries