Query parameterization for safety in Node.js - Step-by-Step Execution

Concept Flow - Query parameterization for safety

Receive user input

↓

Prepare SQL query template

↓

Bind user input as parameters

↓

Send query with parameters to database

↓

Database safely executes query

↓

Return results to user

This flow shows how user input is safely added to a SQL query using parameters to avoid risks like SQL injection.

Execution Sample

Node.js

const userId = '5 OR 1=1';
const query = 'SELECT * FROM users WHERE id = ?';
db.execute(query, [userId], (err, results) => {
  console.log(results);
});

This code safely queries a user by id using a parameter placeholder and passing the user input separately.

Execution Table

Step	Action	Query Template	Parameters	Database Execution	Output
1	Receive user input	SELECT * FROM users WHERE id = ?	['5 OR 1=1']	Parameters bound safely	No SQL injection risk
2	Prepare query with placeholder	SELECT * FROM users WHERE id = ?	['5 OR 1=1']	Query ready	Waiting for execution
3	Execute query with parameters	SELECT * FROM users WHERE id = ?	['5 OR 1=1']	Database treats parameter as value	Returns user with id '5 OR 1=1' if exists, else empty
4	Log results	N/A	N/A	N/A	Prints safe query results or empty array
5	End	N/A	N/A	N/A	Execution stops safely

💡 Execution stops after safely running the parameterized query without risk of SQL injection.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
userId	undefined	'5 OR 1=1'	'5 OR 1=1'	'5 OR 1=1'	'5 OR 1=1'
query	undefined	'SELECT * FROM users WHERE id = ?'	'SELECT * FROM users WHERE id = ?'	'SELECT * FROM users WHERE id = ?'	'SELECT * FROM users WHERE id = ?'
parameters	undefined	undefined	['5 OR 1=1']	['5 OR 1=1']	['5 OR 1=1']
results	undefined	undefined	undefined	Query results or []	Query results or []

Key Moments - 2 Insights

Why don't we just put the user input directly into the query string?

Directly inserting user input can let attackers change the query meaning, causing SQL injection. The execution_table shows parameters are passed separately, so the database treats them as data, not code.

What happens if the user input looks like SQL code, like '5 OR 1=1'?

Because the input is passed as a parameter, the database treats it as a plain value, not code. The execution_table step 3 shows the database safely executes without running 'OR 1=1' as SQL.

Visual Quiz - 3 Questions

Look at the execution_table at step 1. What is the value of the parameters array?

A ['5 OR 1=1']

B ['5']

C []

D ['?']

💡 Hint

Check the 'Parameters' column at step 1 in the execution_table.

At which step does the database treat the user input as a safe value, not code?

A Step 4

B Step 1

C Step 3

D Step 2

💡 Hint

Look at the 'Database Execution' column in the execution_table.

If we removed parameterization and inserted userId directly into the query string, what risk would increase?

A Faster query execution

B SQL injection attack

C Syntax error in JavaScript

D No risk at all

💡 Hint

Refer to the key_moments section explaining why parameterization is important.

Concept Snapshot

Query parameterization means using placeholders like ? in SQL queries
and passing user input separately as parameters.
This stops attackers from changing query meaning (SQL injection).
Always use parameterized queries when working with user data.
Example: db.execute('SELECT * FROM users WHERE id = ?', [userId])

Full Transcript

Query parameterization is a way to safely include user input in database queries. Instead of putting user input directly into the SQL string, we use placeholders like ? and pass the input as separate parameters. This way, the database treats the input as data, not code, preventing SQL injection attacks. The example code shows a query with a ? placeholder and user input passed as an array. The execution steps show how the query is prepared, parameters bound, and safely executed. Variables like userId and query keep their values through the steps. Key moments clarify why direct insertion is dangerous and how parameterization protects. The quiz tests understanding of parameter values, safe execution step, and risks of skipping parameterization. Remember: always use parameterized queries to keep your app safe.

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