SQL injection protection

SQL Injection Attack Principles

SQL injection is a common web security vulnerability where attackers insert malicious SQL code through user input to manipulate database queries. This risk occurs when applications directly concatenate user input into SQL statements. Typical attack methods include:

1. Injecting malicious code through form inputs
2. Modifying URL parameters for injection
3. Forging HTTP header information

// Dangerous SQL concatenation example
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;

Protection Measures in Node.js

Using Parameterized Queries

Parameterized queries are the most effective defense, ensuring user input is always treated as data rather than SQL code. In Node.js, you can use the parameterized query features provided by various database drivers.

// Parameterized query using mysql2 library
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE username = ? AND password = ?',
  [username, password]
);

ORM Framework Protection

ORM (Object-Relational Mapping) frameworks typically have built-in SQL injection protection mechanisms:

// Using Sequelize ORM
const user = await User.findOne({
  where: {
    username: username,
    password: password
  }
});

Input Validation and Filtering

Whitelist Validation

Strictly validate input data using whitelists, allowing only data that matches specific patterns:

// Username allows only alphanumeric characters
if (!/^[a-zA-Z0-9]+$/.test(username)) {
  throw new Error('Invalid username format');
}

Type Checking

Ensure numeric parameters are indeed of numeric type:

// Ensure ID is a number
const id = Number(req.params.id);
if (isNaN(id)) {
  return res.status(400).send('Invalid ID');
}

Database Permission Minimization

Application Account Permission Restrictions

Database users should have only the minimum necessary permissions:

-- Create a user with only query permissions
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON app_db.* TO 'app_user'@'localhost';

Stored Procedure Usage

Encapsulate sensitive operations in stored procedures:

// Calling a stored procedure
await connection.execute('CALL authenticate_user(?, ?)', [username, password]);

Other Protection Measures

Error Handling Strategy

Avoid leaking database structure in error messages:

try {
  // Database operation
} catch (err) {
  console.error(err);
  res.status(500).send('Internal Server Error');
}

Regular Security Audits

Use tools to scan for SQL injection vulnerabilities:

# Scanning with sqlmap
sqlmap -u "http://example.com/login" --data="username=admin&password=123"

Practical Case Analysis

Login Form Protection

// Secure login handling
app.post('/login', async (req, res) => {
  const { username, password } = req.body;
  
  // Input validation
  if (!username || !password) {
    return res.status(400).send('Missing credentials');
  }
  
  // Parameterized query
  try {
    const [users] = await pool.query(
      'SELECT id, username FROM users WHERE username = ? AND password = SHA2(?, 256)',
      [username, password]
    );
    
    if (users.length === 0) {
      return res.status(401).send('Invalid credentials');
    }
    
    res.json(users[0]);
  } catch (err) {
    console.error(err);
    res.status(500).send('Login failed');
  }
});

Search Function Protection

// Secure search implementation
app.get('/search', async (req, res) => {
  const { q } = req.query;
  
  // Input filtering
  const searchTerm = q.replace(/[^\w\s]/gi, '');
  
  try {
    const [results] = await pool.query(
      'SELECT * FROM products WHERE name LIKE ?',
      [`%${searchTerm}%`]
    );
    
    res.json(results);
  } catch (err) {
    console.error(err);
    res.status(500).send('Search failed');
  }
});

Advanced Protection Techniques

Prepared Statement Caching

// Using mysql2's prepared statements
const stmt = await connection.prepare(
  'SELECT * FROM users WHERE id = ?'
);

// Subsequent queries use cached statements
const [rows] = await stmt.execute([userId]);

Dynamic Query Construction Protection

When dynamic query construction is needed, use safe methods:

// Secure dynamic query construction
function buildUserQuery(filters) {
  const params = [];
  let query = 'SELECT * FROM users WHERE 1=1';
  
  if (filters.name) {
    query += ' AND name LIKE ?';
    params.push(`%${filters.name}%`);
  }
  
  if (filters.age) {
    query += ' AND age = ?';
    params.push(Number(filters.age));
  }
  
  return { query, params };
}

const { query, params } = buildUserQuery(req.query);
const [users] = await connection.query(query, params);

Balancing Performance and Security

Query Optimization and Protection

// Using parameterized pagination
app.get('/posts', async (req, res) => {
  const page = Math.max(1, Number(req.query.page) || 1);
  const limit = Math.min(100, Math.max(1, Number(req.query.limit) || 10));
  const offset = (page - 1) * limit;
  
  const [posts] = await pool.query(
    'SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?',
    [limit, offset]
  );
  
  res.json(posts);
});

Batch Operation Protection

// Secure batch insertion
app.post('/products/batch', async (req, res) => {
  const products = req.body;
  
  if (!Array.isArray(products)) {
    return res.status(400).send('Invalid payload');
  }
  
  const placeholders = products.map(() => '(?, ?, ?)').join(',');
  const values = [];
  
  products.forEach(product => {
    values.push(
      product.name,
      Number(product.price),
      product.category
    );
  });
  
  try {
    await pool.query(
      `INSERT INTO products (name, price, category) VALUES ${placeholders}`,
      values
    );
    
    res.status(201).send('Products created');
  } catch (err) {
    console.error(err);
    res.status(500).send('Batch insert failed');
  }
});

Monitoring and Logging

Suspicious Query Monitoring

// Logging slow and suspicious queries
pool.on('query', (query) => {
  if (query.sql.includes('1=1') || query.sql.includes('--')) {
    console.warn('Suspicious query detected:', query.sql);
  }
  
  if (query.duration > 1000) {
    console.log('Slow query:', query.sql, query.duration);
  }
});

Audit Log Implementation

// Database operation auditing
async function auditLog(userId, action, details) {
  try {
    await pool.query(
      'INSERT INTO audit_logs (user_id, action, details, ip_address) VALUES (?, ?, ?, ?)',
      [userId, action, JSON.stringify(details), req.ip]
    );
  } catch (err) {
    console.error('Failed to write audit log:', err);
  }
}

// Calling in critical operations
app.post('/transfer', async (req, res) => {
  // Execute transfer operation
  await auditLog(req.user.id, 'money_transfer', {
    amount: req.body.amount,
    recipient: req.body.to
  });
});