Methods for preventing SQL injection

SQL injection is a common security threat where attackers construct malicious SQL statements to manipulate database queries, potentially leading to data leaks, tampering, or even server compromise. In a Koa2 application, preventing SQL injection requires a multi-layered approach, including parameterized queries, ORM usage, input validation, and permission control.

Parameterized Queries Are the Most Effective Defense

Parameterized queries separate SQL statements from parameters, ensuring user input is always treated as data rather than executable code. Using Koa2 with the mysql2 library as an example:

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

app.use(async (ctx) => {
  const connection = await mysql.createConnection(/* configuration */);
  
  // Dangerous traditional concatenation
  // const [rows] = await connection.execute(`SELECT * FROM users WHERE id = ${ctx.query.id}`);
  
  // Safe parameterized query
  const [rows] = await connection.execute(
    'SELECT * FROM users WHERE id = ?',
    [ctx.query.id]
  );
  
  ctx.body = rows;
});

Parameterized queries are supported by all major databases:

• MySQL: Uses ? placeholders
• PostgreSQL: Uses $1, $2 numbered placeholders
• SQLite: Supports both ? and $1 syntax

ORM Frameworks Provide an Additional Protection Layer

Using ORM frameworks like Sequelize or TypeORM automatically handles parameter escaping:

// Sequelize example
const users = await User.findAll({
  where: {
    username: ctx.query.username,
    status: 'active'
  }
});

// TypeORM example
const userRepository = getRepository(User);
const user = await userRepository.findOne({
  where: { id: ctx.params.id }
});

ORM defense mechanisms include:

1. Automatic parameter binding
2. Type safety validation
3. Query builder syntax checking
4. Protection against nested object injection

Input Validation and Data Sanitization

Business-layer validation should complement SQL parameterization:

const Joi = require('joi');

const schema = Joi.object({
  id: Joi.number().integer().min(1).required(),
  username: Joi.string().alphanum().min(3).max(30)
});

app.use(async (ctx) => {
  const { error, value } = schema.validate(ctx.query);
  if (error) throw new Error('Invalid input');
  
  // Sanitize special characters
  const safeInput = value.username.replace(/[^\w]/g, '');
});

Key validation points:

• Data type coercion
• Length restrictions
• Regular expression whitelisting
• Business rule validation (e.g., email format)

Principle of Least Privilege

Database accounts should have strictly limited permissions:

-- Create a read-only user
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT SELECT ON app_db.* TO 'webuser'@'localhost';

-- Granular table-level permissions
GRANT INSERT, UPDATE ON app_db.logs TO 'webuser'@'localhost';

Permission control essentials:

1. Application accounts should not have DBA privileges
2. Different operations should use different accounts
3. Stored procedures should restrict sensitive operations
4. Regular permission audits

Defense-in-Depth Strategy

Combine multiple defensive measures:

// Middleware example: SQL keyword filtering
app.use(async (ctx, next) => {
  const sqlKeywords = /(DROP|DELETE|INSERT\s+INTO|UPDATE\s+\w+\s+SET|SELECT\s+\*\s+FROM)/i;
  
  Object.values(ctx.query).forEach(value => {
    if (sqlKeywords.test(value)) {
      ctx.throw(400, 'Invalid request');
    }
  });
  
  await next();
});

// Query timeout settings
const pool = mysql.createPool({
  connectionLimit: 10,
  connectTimeout: 5000,
  queryTimeout: 3000
});

Enhanced protection measures:

1. Web Application Firewall (WAF) rules
2. Query log monitoring
3. Regular dependency updates
4. Error message obfuscation

Handling Special Scenarios in Practice

Special attention is required for JSON and dynamic queries:

// Safely handle JSON fields
await connection.execute(
  `UPDATE products SET metadata = ? WHERE id = ?`,
  [JSON.stringify(ctx.request.body.metadata), ctx.params.id]
);

// Validate dynamic sort direction
const validFields = ['id', 'name', 'created_at'];
const validOrders = ['ASC', 'DESC'];

const orderField = validFields.includes(ctx.query.sort) 
  ? ctx.query.sort 
  : 'id';
  
const orderDirection = validOrders.includes(ctx.query.order) 
  ? ctx.query.order 
  : 'ASC';

await User.findAll({ order: [[orderField, orderDirection]] });

Solutions for complex scenarios:

1. Field name whitelist validation
2. Encapsulating sensitive operations in stored procedures
3. Using database views to limit data exposure
4. Regular SQL audits

Testing and Vulnerability Detection

Automated testing example:

// Use supertest for injection testing
const request = require('supertest');
const app = require('../app');

describe('SQL Injection Protection Tests', () => {
  it('should block UNION injection attacks', async () => {
    await request(app)
      .get('/users?name=admin\' UNION SELECT password FROM users--')
      .expect(400);
  });

  it('should block boolean-based blind injection', async () => {
    await request(app)
      .get('/products?id=1 AND 1=CONVERT(int,(SELECT table_name FROM information_schema.tables))')
      .expect(500); // Should return a generic error, not database errors
  });
});

Detection methods include:

1. Automated scanning tools (e.g., SQLMap)
2. Fuzz testing
3. Code audits
4. Penetration testing