Overview

While it is rare to build backends directly using raw Node.js today, understanding SQL injection attacks is still valuable. This article uses Node.js and MySQL to explain SQL injection vulnerabilities and how to mitigate them.

SQL injection is an ancient attack that has existed since the advant of Web 2.0. It typically occurs in frontend inputs like text boxes and text areas, where attackers inject SQL code into submitted content that gets sent to the backend. If the backend inadvertently concatenates this injected SQL with its own queries, it can execute malicious code. Without proper prevention, this can lead to data leaks, database deletion, and other severe security issues.

SQL Injection Demonstration

Let's use a login flow as an example. First, create a users table in MySQL, then insert a test record:

insert into users (username, password, realname) values ('leihou', '123', '雷猴');

This record has the following fields:

• username: 'leihou'
• password: '123'
• realname: '雷猴'

Next, create a basic vulnerable login endpoint in Node.js:

const mysql = require('mysql');

// Initialize database connection
const dbConnection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    port: '3306',
    database: 'testdb'
});

// Establish database connection
dbConnection.connect();

// Universal SQL execution function
function executeSQL(sqlQuery) {
  return new Promise((resolve, reject) => {
    dbConnection.query(sqlQuery, (error, result) => {
      if (error) {
        reject(error);
        return;
      }
      resolve(result);
    });
  });
}

// Vulnerable login function
const vulnerableLogin = (username, password) => {
  const unsafeSql = `
    select username, realname from users where username='${username}' and password='${password}';
  `;

  console.log(unsafeSql);
  return executeSQL(unsafeSql).then(rows => {
    return rows[0] || {};
  });
}

You can use the methods covered in Node.js HTTP Requests to create a public endpoint for frontend access; this article will skip that detail as its not the focus. You can test the endpoint either with a simple HTML page using Ajax, or a tool like Postman.

With valid credentials, the login works as expected:

• Username: leihou
• Password: 123

However, an attacker can bypass the password check entirely by inputting leihou' -- (note the spaces before and after the double hyphen) as the username, and entering any random string as the password. The resulting concatenated SQL becomes:

select username, realname from users where username='leihou' -- ' and password='aslkfjsaf';

In MySQL, -- denotes a line comment, so the query effectively only filters for users with username leihou, completely ignoring the password check.

Worse, an attacker could delete your entire users table by inputting leihou'; delete from users; -- as the username, which would execute the delete command after the original query.

Prevention Methods

SQL injection has been a known threat for over a decade, so standard mitigation techniques are well-established. One core approach is to escape special characters in user-supplied input.

The official MySQL npm package for Node.js includes an escape method that handles this sanitization automatically. Here is the fixed, secure login function:

const mysql = require('mysql');

// Initialize connection and executeSQL function as shown earlier
const escapeInput = mysql.escape;

const secureLogin = (username, password) => {
  const escapedUsername = escapeInput(username);
  const escapedPassword = escapeInput(password);
  const safeSql = `
    select username, realname from users where username=${escapedUsername} and password=${escapedPassword};
  `;

  console.log(safeSql);
  return executeSQL(safeSql).then(rows => {
    return rows[0] || {};
  });
}

When an attacker inputs leihou' -- as the username, the escaped SQL logged to the console will be:

select username, realname from users where username='leihou\' -- ' and password='123345';

The single quote in the input is properly escaped, preventing the comment injection attack.

Recommended Reading

• Node.js HTTP Requests
• Node.js 5-Minute Guide to Connecting MySQL for CRUD Operations
• Node.js Stream Basics
• Node.js Working with Cookies
• Node.js 5-Minute Guide to Connecting Redis for Read/Write Operations
• *Node.js Reading and Writing Files'