Before we build an endpoint that uses user input (like a user ID from the URL), we need to learn about a critical security issue called SQL injection and how to prevent it.

What is SQL Injection?

SQL injection happens when user input is directly inserted into SQL queries without proper protection. This allows attackers to run malicious SQL commands.

Dangerous approach (NEVER do this)

// DANGEROUS - Don't do this!
const userId = req.params.id;
const query = `SELECT * FROM users WHERE id = ${userId}`;

If someone visits /users/1; DROP TABLE users;, this becomes:

SELECT * FROM users WHERE id = 1; DROP TABLE users;

The semicolon (;) ends the SELECT command and allows the DROP command to run after it, potentially deleting your entire users table.

The Safe Way: Parameterised Queries

The mysql2 library provides parameterised queries (prepared statements) that safely handle user input:

// SAFE - Always do this!
const userId = req.params.id;
const [users] = await pool.execute("SELECT * FROM users WHERE id = ?", [
  userId,
]);

How it works:

  • The ? is a placeholder for the actual value in the SQL query.
  • The second argument ([userId]) is an array containing the values to substitute for the placeholders.
  • mysql2 safely inserts the userId value where the ? appears.
  • The library automatically escapes dangerous characters.
  • Even if someone tries to inject SQL, it’s treated as literal text instead of executable code.

With parameterised queries, the malicious input 1; DROP TABLE users; becomes:

SELECT * FROM users WHERE id = '1; DROP TABLE users;';

The entire injection attempt is treated as a literal string value, not executable SQL commands.

Why This Is Important

  • Security: Prevents attackers from running malicious SQL.
  • Data protection: Keeps your database safe from attacks.
  • Industry standard: All professional applications use parameterised queries.
  • Required knowledge: This is a fundamental security practice.

Now let’s use this safe approach in our single-user endpoint.

Tags: