Prevent SQL Injection with Parameterized Queries

What is SQL Injection and Why is it So Dangerous?

When your application takes input from a user (e.g., from a search box, a login form, or even a URL) and uses it to build a database query, you create a potential security hole.

An SQL Injection attack occurs when a malicious user provides specially crafted input that tricks your application into running unintended SQL commands. Instead of just providing data, the user "injects" bits of SQL code into your query.

The consequences can be catastrophic:

• Bypassing logins and accessing sensitive data.
• Reading, modifying, or deleting your entire database.
• Gaining administrative control over the database server.

The image below illustrates how an attacker can manipulate form inputs to bypass security and retrieve sensitive information from the database.

To get a clear overview of the threat, let's watch a brief explanation.

The Vulnerability: Building Queries with String Concatenation

The root cause of SQL injection is almost always building query strings by concatenating or interpolating untrusted user input directly into the SQL statement. Let's look at what this bad practice looks like in a Node.js/Express application.

Imagine you have an endpoint to fetch a user's profile based on their company name, which comes from a URL query parameter (req.query.userCompany). A naive and insecure way to write this query would be:

// WARNING: VULNERABLE CODE. DO NOT USE.
const userCompany = req.query.userCompany;
const sql = "SELECT * FROM users WHERE company = '" + userCompany + "';";
const result = await client.query(sql);

Or, using ES6 template literals:

// WARNING: VULNERABLE CODE. DO NOT USE.
const userCompany = req.query.userCompany;
const sql = `SELECT * FROM users WHERE company = '${userCompany}';`;
const result = await client.query(sql);

At first glance, this seems fine. If a user provides MyCo, the query becomes SELECT * FROM users WHERE company = 'MyCo';, which works as expected.

But what if a malicious user provides the following input for userCompany?
' OR '1'='1

The resulting SQL query becomes:
SELECT * FROM users WHERE company = '' OR '1'='1';

Since '1'='1' is always true, the WHERE clause is always satisfied, and the query returns all users from the table, not just those from a specific company. An attacker just bypassed your filter and accessed data they shouldn't have. Even worse, they could use similar techniques to UPDATE or DROP tables.

The Solution: Parameterized Queries

The correct way to handle user input is to use parameterized queries (also known as prepared statements). This technique separates the SQL command from the data. You provide the SQL query with placeholders, and then you provide the values for those placeholders separately.

The database driver (in our case, node-postgres) is then responsible for safely inserting the values. It treats the user input strictly as data, ensuring it cannot be interpreted as SQL code.

The node-postgres (pg) library uses placeholders in the format $1, $2, and so on.

Let's rewrite our vulnerable query securely:

// SECURE CODE
const userCompany = req.query.userCompany;
const sql = 'SELECT * FROM users WHERE company = $1';
const values = [userCompany];

const result = await client.query(sql, values);

Here’s what happens:

1. The SQL statement SELECT * FROM users WHERE company = $1 is sent to the PostgreSQL server.
2. The server parses and understands this query, noting that it expects one parameter.
3. The values array [userCompany] is sent separately.
4. The server then safely inserts the value into the prepared query. If an attacker provides ' OR '1'='1', the database will literally search for a company with the name ' OR '1'='1', which will almost certainly return no results. The malicious code is never executed.

The official node-postgres documentation provides a clear example of this.

This short video also gives a quick, practical demonstration of this exact syntax.

Alternative Syntax: The Query Config Object

You will also frequently see the query and values passed together in a single configuration object. This is just a different way of writing the same secure, parameterized query.

// SECURE CODE (alternative syntax)
const userCompany = req.query.userCompany;
const query = {
  text: 'SELECT * FROM users WHERE company = $1',
  values: [userCompany],
};

const result = await client.query(query);

This pattern is especially useful when your query has other configuration options. The Stack Overflow answer below refactors insecure code into a secure version, demonstrating this concept perfectly.

// In an Express route handler: app.put('/users/:id', async (req, res) => { ... });

const userId = req.params.id;
const { newEmail } = req.body;

// WARNING: VULNERABLE CODE
const sql = `UPDATE users SET email = '${newEmail}' WHERE id = ${userId}`;

try {
  const result = await client.query(sql);
  res.send('User updated!');
} catch (err) {
  res.status(500).send('Error updating user.');
}

// SECURE CODE
const userId = req.params.id;
const { newEmail } = req.body;

const sql = 'UPDATE users SET email = $1 WHERE id = $2';
const values = [newEmail, userId];

try {
  const result = await client.query(sql, values);
  res.send('User updated!');
} catch (err) {
  res.status(500).send('Error updating user.');
}

Defense in Depth: Additional Safeguards

While parameterized queries are your primary defense, a robust security strategy involves multiple layers. You should also validate user input before it even reaches the database query.

This means ensuring the input is in the format you expect. For example:

• If you expect a number, check that it's a valid number.
• If you expect an email, check that it looks like an email address.
• If the input can only be one of a few predefined options (e.g., 'admin', 'editor', 'viewer'), check it against an "allow list" of those values.

This approach is called input validation. It acts as a second line of defense and can prevent errors and attacks early.

Important Limitation: Parameterized queries are for values only. You cannot use them for dynamic table names, column names, or other SQL keywords. If you need to build queries with dynamic identifiers, you must use a dedicated library like pg-format to safely escape them, or use a strict allow-list to ensure the identifier is one of a few known, safe values.

Conclusion

You've now learned the most important principle for writing secure database code. Building this habit from the start will save you from enormous headaches and vulnerabilities down the line.

Key Takeaways:

• Never trust user input. Always assume it could be malicious.
• Never build SQL queries using string concatenation or template literals with user-provided variables. This is the direct path to SQL injection vulnerabilities.
• Always use parameterized queries. Separate your SQL command (text) from the user data (values). The database driver will handle the data safely.
• In node-postgres, the syntax for placeholders is $1, $2, etc.
• Employ input validation as an additional layer of defense to check data before it even gets to your query logic.

Preview of the Next Lesson:
This lesson concludes our module on PostgreSQL fundamentals. You now have the skills to design tables, perform CRUD operations, join related data, and do it all securely.

In the next module, we will begin building a complete RESTful API using Node.js and Express. You'll design a schema for a new application and implement all the endpoints for creating, reading, updating, and deleting data, putting all your new database skills into practice in a real-world project context.