Express SQL Injection Prevention

Introduction

SQL injection is one of the most common and dangerous web application vulnerabilities. It occurs when an attacker is able to insert or "inject" malicious SQL code into a query that your application sends to its database. When successful, SQL injection attacks can lead to unauthorized access to sensitive data, data loss, or even complete system compromise.

In Express.js applications that interact with databases, preventing SQL injection is crucial for maintaining security. This guide will walk you through understanding SQL injection vulnerabilities and implementing effective prevention techniques in your Express applications.

Understanding SQL Injection

Before diving into prevention techniques, let's understand what SQL injection looks like in an Express application.

What is SQL Injection?

SQL injection occurs when user-supplied data is used directly in SQL queries without proper validation or sanitization. Consider this vulnerable Express route:

javascript
app.get('/users', (req, res) => {
  const userId = req.query.id;
  const query = `SELECT * FROM users WHERE id = ${userId}`;
  
  db.query(query, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

In the above code, an attacker could provide id=1 OR 1=1 as the query parameter, resulting in the SQL query:

sql
SELECT * FROM users WHERE id = 1 OR 1=1

This would return all users in the database instead of just the user with ID 1, potentially exposing sensitive user information.

Prevention Techniques

1. Use Parameterized Queries

The most effective way to prevent SQL injection is to use parameterized queries (also known as prepared statements). These separate SQL code from data, making it impossible for attackers to manipulate the query structure.

Example using MySQL:

javascript
const mysql = require('mysql2');
const pool = mysql.createPool({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'my_db'
});

app.get('/users', (req, res) => {
  const userId = req.query.id;
  
  // Using parameterized query
  pool.query(
    'SELECT * FROM users WHERE id = ?', 
    [userId], 
    (err, results) => {
      if (err) {
        console.error(err);
        return res.status(500).json({ error: 'Database error' });
      }
      res.json(results);
    }
  );
});

Example using Sequelize ORM:

javascript
const { Sequelize, Model } = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

class User extends Model {}
User.init({
  name: Sequelize.STRING,
  email: Sequelize.STRING
}, { sequelize, modelName: 'user' });

app.get('/users', async (req, res) => {
  try {
    const userId = req.query.id;
    const user = await User.findByPk(userId);
    res.json(user);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Server error' });
  }
});

2. ORM (Object-Relational Mapping)

Using an ORM like Sequelize, TypeORM, or Prisma provides an additional layer of protection as they typically use parameterized queries internally.

javascript
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

app.get('/users/:id', async (req, res) => {
  try {
    const userId = parseInt(req.params.id);
    const user = await prisma.user.findUnique({
      where: {
        id: userId
      }
    });
    res.json(user);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Server error' });
  }
});

3. Input Validation

Always validate user input to ensure it matches the expected format. For example, if you expect a numeric ID, convert and validate it:

javascript
app.get('/users/:id', (req, res) => {
  // Convert to number and check if valid
  const userId = parseInt(req.params.id, 10);
  
  if (isNaN(userId) || userId <= 0) {
    return res.status(400).json({ error: 'Invalid user ID' });
  }
  
  // Proceed with database query using parameterized statement
  pool.query('SELECT * FROM users WHERE id = ?', [userId], (err, results) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Database error' });
    }
    res.json(results);
  });
});

4. Database User Permissions

Limit the database user permissions for your application to only what's necessary. This follows the principle of least privilege and can minimize the damage if an SQL injection occurs.

For example, if your application only needs to read data, give it SELECT privileges only:

sql
-- MySQL example
CREATE USER 'express_app'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON my_database.* TO 'express_app'@'localhost';

5. Use SQL Injection Prevention Middleware

You can implement or use existing middleware to help prevent SQL injection:

javascript
// A simple middleware for validating numeric IDs
function validateNumericId(req, res, next) {
  const id = parseInt(req.params.id, 10);
  if (isNaN(id) || id <= 0) {
    return res.status(400).json({ error: 'Invalid ID parameter' });
  }
  // Store the validated ID
  req.validatedId = id;
  next();
}

app.get('/users/:id', validateNumericId, (req, res) => {
  // Use the validated ID for database queries
  pool.query('SELECT * FROM users WHERE id = ?', [req.validatedId], (err, results) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Database error' });
    }
    res.json(results);
  });
});

Real-World Example: User Authentication System

Let's examine a more comprehensive example of a user login system protected against SQL injection:

javascript
const express = require('express');
const mysql = require('mysql2/promise');
const bcrypt = require('bcrypt');
const { body, validationResult } = require('express-validator');

const app = express();
app.use(express.json());

// Database connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'app_user',
  password: 'secure_password',
  database: 'user_auth'
});

// Login route with validation and protection
app.post('/login',
  // Input validation middleware
  [
    body('email').isEmail().normalizeEmail(),
    body('password').isLength({ min: 6 })
  ],
  async (req, res) => {
    // Check for validation errors
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    try {
      const { email, password } = req.body;
      
      // Use parameterized query to prevent SQL injection
      const [rows] = await pool.execute(
        'SELECT * FROM users WHERE email = ?',
        [email]
      );
      
      if (rows.length === 0) {
        return res.status(401).json({ error: 'Invalid credentials' });
      }
      
      const user = rows[0];
      
      // Compare passwords securely
      const passwordMatch = await bcrypt.compare(password, user.password_hash);
      
      if (!passwordMatch) {
        return res.status(401).json({ error: 'Invalid credentials' });
      }
      
      // User authenticated successfully
      // In a real app, you'd generate a JWT token here
      res.json({ 
        success: true, 
        user: { 
          id: user.id, 
          name: user.name, 
          email: user.email 
        } 
      });
    } catch (error) {
      console.error('Login error:', error);
      res.status(500).json({ error: 'Internal server error' });
    }
  }
);

// Registration route
app.post('/register',
  [
    body('name').trim().isLength({ min: 2 }),
    body('email').isEmail().normalizeEmail(),
    body('password').isLength({ min: 6 })
  ],
  async (req, res) => {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    try {
      const { name, email, password } = req.body;
      
      // Check if user already exists
      const [existingUsers] = await pool.execute(
        'SELECT id FROM users WHERE email = ?',
        [email]
      );
      
      if (existingUsers.length > 0) {
        return res.status(409).json({ error: 'User already exists' });
      }
      
      // Hash password
      const passwordHash = await bcrypt.hash(password, 10);
      
      // Insert new user with parameterized query
      const [result] = await pool.execute(
        'INSERT INTO users (name, email, password_hash) VALUES (?, ?, ?)',
        [name, email, passwordHash]
      );
      
      res.status(201).json({ 
        success: true, 
        userId: result.insertId 
      });
    } catch (error) {
      console.error('Registration error:', error);
      res.status(500).json({ error: 'Internal server error' });
    }
  }
);

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

The example above demonstrates several security best practices:

Input validation using express-validator
Parameterized queries for all database operations
Password hashing with bcrypt
Proper error handling
Normalized and consistent error responses

Common Mistakes to Avoid

String concatenation in SQL queries: Never build queries by concatenating strings with user input.

javascript
// NEVER DO THIS
const query = `SELECT * FROM users WHERE username = '${username}'`;

Displaying detailed database errors: Hide technical details from users to avoid leaking information about your database structure.
Trusting client-side validation only: Always validate and sanitize on the server side.
Using outdated or vulnerable database drivers: Keep your dependencies updated to benefit from security patches.
Insufficient logging: Implement proper logging to detect and investigate potential SQL injection attempts.

Summary

SQL injection remains one of the most critical web application vulnerabilities. In Express.js applications, the following practices are essential for prevention:

Use parameterized queries or prepared statements
Implement proper input validation and sanitization
Leverage ORMs that provide built-in protection
Follow the principle of least privilege for database accounts
Keep dependencies updated
Implement proper error handling and logging

By following these practices, you can significantly reduce the risk of SQL injection attacks and protect your application's data integrity and user privacy.

Additional Resources

Exercises

Practice Exercise: Identify and fix SQL injection vulnerabilities in the following Express route:

javascript
app.get('/products', (req, res) => {
  const category = req.query.category;
  const minPrice = req.query.minPrice;
  const query = `SELECT * FROM products WHERE category = '${category}' AND price >= ${minPrice}`;
  
  db.query(query, (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

Security Audit: Review an existing Express application (your own or an open-source one) and identify potential SQL injection vulnerabilities.
Implementation Task: Create a secure data access layer for an Express application that connects to a SQL database, incorporating all the prevention techniques discussed in this guide.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Understanding SQL Injection​

What is SQL Injection?​

Prevention Techniques​

1. Use Parameterized Queries​

Example using MySQL:​

Example using Sequelize ORM:​

2. ORM (Object-Relational Mapping)​

3. Input Validation​

4. Database User Permissions​

5. Use SQL Injection Prevention Middleware​

Real-World Example: User Authentication System​

Common Mistakes to Avoid​

Summary​

Additional Resources​

Exercises​