SQL Injections in Node.js

SQL Injection is a security problem that takes advantage of how an application talks to its database to run SQL commands it shouldn't.

This part shows first how SQL injections can happen in Node.js applications and then gives examples of how to protect against these dangers.

How SQL Injections Happen

SQL injections happen when user input is itself a SQL query and our application lacks proper validation or sanitation. This allows attackers to manipulate our databases.

This will make more sense as an example:

const mysql = require('mysql');
const connection = mysql.createConnection({
    host     : 'example.com',
    user     : 'dbuser',
    password : 'dbpassword',
    database : 'mydb'
});

const userInput = "'; DROP TABLE users; --"; // Malicious user input
const query = "SELECT * FROM users WHERE email = '" + userInput + "'";

connection.query(query, function (error, results, fields) {
    if (error) throw error;
    // process results
});

Here I'm using userInput as an example attacker input. This will inject a malicious payload ("'; DROP TABLE users; --") that alters the query to delete the users table.

Pretty fun, right?

How Do I Stop Them?

There are a few ways you can prevent SQL injections.

Here's a few of the easiest ways with examples:

Use Parameterized Queries

Using "parameterized queries" is a primary defense against SQL injections.

This method ensures that user inputs are treated as data, not as part of the SQL command.

Here's an example with pg (PostgreSQL):

const { Pool } = require('pg');
const pool = new Pool();

// Correct way to handle user input
const text = 'SELECT * FROM users WHERE id = $1';
const values = [userId]; // Assume 'userId' is user input

// Execute query
pool.query(text, values, (err, res) => {
  if (err) {
    console.log(err.stack);
  } else {
    console.log(res.rows[0]);
  }
});

Sanitize User Input

Like most backend code, validating and sanitizing inputs ensure they conform to expected formats, further reducing injection risks.

My favorite library at the minute to help me with sanitation is Zod, so here's a quick example so you can see how you can validate data:

const zod = require('zod');

// Define a schema for the user input
const UserInputSchema = zod.object({
  username: zod.string().min(1).regex(/^[a-zA-Z0-9]+$/), // Alphanumeric characters only
  // Add more fields as necessary
});

// Example user input
let userInput = {
  username: "someUserInput" // Assume this comes from the user
};

try {
  // Validate the user input against the schema
  UserInputSchema.parse(userInput);
  
  // If the input is valid, proceed with processing
  console.log("User input is valid.");
  // Proceed with your SQL query with sanitized input
} catch (error) {
  // Handle the case where the input does not conform to the schema
  console.error("Invalid input detected:", error.errors);
}

Use ORM Libraries

ORMs (Object-Relational Mapping) like Sequelize abstract SQL operations into JavaScript, minimizing direct SQL string manipulations. Plenty of options here, so you can quickly search and find your favorite flavor.

But here's an example with Sequelize:

const { Sequelize, Model, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:');

class User extends Model {}

User.init({
  username: DataTypes.STRING,
  birthday: DataTypes.DATE
}, { sequelize, modelName: 'user' });

// Using Sequelize to find a user securely
const safeFindUser = async (userId) => {
  const user = await User.findByPk(userId);
  console.log(user);
}

safeFindUser(someUserId); // someUserId is user-provided input

Now with this knowledge you should be able to quickly and easily plug those vulnerabilities.