How to Insert Records on SQL Database Using Node.js?

In this article, we will explore how to insert records into a SQL database using Node.js. We’ll dive into the different approaches to achieve this and discuss the best practices for securing your database queries.

Understanding SQL Injection Vulnerabilities

Before we begin, let’s quickly understand what SQL injection is. An SQL injection occurs when an attacker injects malicious SQL code into a web application’s database query in order to extract or modify sensitive data. This can happen by injecting user input into a query without proper sanitization or parameterization.

In the context of your question, you’re experiencing issues with executing SQL queries that contain placeholders for dynamic values. We’ll discuss how to resolve these issues using parameterized queries and secure your database interactions.

The Problem: Using Plain SQL Queries

Let’s revisit your original code snippet:

router.post("/add/client", (request, res) => {
  let client = {}
  client.UserInfo = request.body.name;
  client.BitMask = request.body.bitmask;

  pool.request().query("INSERT INTO users(UserInfo, BitMask) Values ?", [client],
    (err, result) => {
      if (!err) {
        return res.json({ success: true, data: result, records_added: result.affected_rows });
      }
      return res.json({ success: false, data: err });
    })
});

The problem with this approach is that the query string is concatenating user input (client) directly into the SQL statement. This creates a vulnerability to SQL injection attacks.

The Solution: Parameterized Queries

To fix this issue, you should use parameterized queries instead of concatenating values into your SQL statements. Here’s how you can modify your code:

router.post("/add/client", async (request, res) => {
  try {
    const result = await pool.request()
      .input('userInfo', request.body.name)
      .input('bitmask', request.body.bitmask)
      .query("INSERT INTO users(UserInfo, BitMask) VALUES (@userInfo, @bitmask)");
    return res.json({ 
      success: true, 
      data: result, // I would avoid this, potential to leak DB info to the client
      records_added: result.affected_rows 
    });
  } catch (e) {
    // consider returning next(e) here and handling errors somewhere common
    return res.json({ 
      success: false, 
      data: err // don't do this, again potential to leak DB info to the client
    });
  }
});

In this modified code snippet, we’re using the input() method provided by the pool library (or your SQL driver of choice) to define parameters for our query. These parameters are then passed as a parameterized array to the query() method.

How Parameterized Queries Work

When you use a parameterized query, the database driver separates the query string from the actual values being inserted. This prevents attackers from injecting malicious SQL code by manipulating the query string.

Here’s an example of how this works:

Plain SQL Query:

INSERT INTO users(UserInfo, BitMask) VALUES 'value1', 1;

Parameterized Query:

INSERT INTO users(UserInfo, BitMask) VALUES (@userInfo, @bitmask);

In the parameterized query, @userInfo and @bitmask are placeholders for dynamic values. The database driver will replace these placeholders with actual values when executing the query.

Benefits of Parameterized Queries

Using parameterized queries has several benefits:

• Improved security: By separating the query string from the actual values being inserted, you prevent attackers from injecting malicious SQL code.
• Better performance: Because the database driver can cache and reuse prepared statements, parameterized queries often perform better than plain SQL queries with concatenated values.
• Simplified error handling: With parameterized queries, it’s easier to handle errors and exceptions because the actual values being inserted are not part of the query string.

Best Practices for Secure Database Queries

Here are some best practices for secure database queries:

• Use parameterized queries: Always use parameterized queries instead of concatenating values into your SQL statements.
• Avoid storing sensitive data in tables: Don’t store sensitive data like passwords, API keys, or credit card numbers in tables that can be accessed by unauthorized users.
• Use prepared statements: Use prepared statements to execute queries with dynamic values. This helps prevent SQL injection attacks and improves performance.
• Limit database privileges: Limit the database privileges of your application user account to prevent unauthorized access to sensitive data.
• Regularly update dependencies: Regularly update your dependencies, including libraries and frameworks, to ensure you have the latest security patches.

By following these best practices and using parameterized queries, you can secure your database interactions and protect against SQL injection attacks.

Last modified on 2024-05-03