Preventing SQL Injection in MySQL: A Developer’s Checklist

SQL injection remains one of the most dangerous vulnerabilities for web applications, potentially allowing attackers to access, modify, or delete sensitive data. For developers working with MySQL databases, preventing SQL injection should be a top priority. This checklist provides concrete strategies and code examples to protect your applications from SQL injection attacks.

Understanding SQL Injection

Before diving into prevention techniques, it’s important to understand what SQL injection is. SQL injection occurs when untrusted user input is included in SQL queries without proper validation or sanitization. This allows attackers to manipulate queries to access unauthorized data or perform destructive operations.

Consider this vulnerable code:

// Vulnerable code example
$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($connection, $query);

If an attacker inputs ' OR '1'='1, the resulting query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1'

This would return all records from the users table, potentially exposing sensitive information.

Prevention Checklist

1. Use Prepared Statements with Parameterized Queries

Prepared statements are the most effective way to prevent SQL injection. They separate SQL logic from data, ensuring that user input is never treated as part of the SQL command.

PHP (MySQLi)

// Prepare statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
// Bind parameters
$stmt->bind_param("ss", $username, $password);
// Execute query
$stmt->execute();
// Get results
$result = $stmt->get_result();

PHP (PDO)

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute(['username' => $username]);
$user = $stmt->fetch();

Node.js (mysql2)

const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'db'
});

const [rows, fields] = await connection.execute(
  'SELECT * FROM users WHERE username = ?',
  [username]
);

Python (MySQL Connector)

import mysql.connector

cnx = mysql.connector.connect(user='user', password='password',
                              host='127.0.0.1', database='db')
cursor = cnx.cursor(prepared=True)

query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

for row in cursor:
    print(row)

cursor.close()
cnx.close()

2. Input Validation and Sanitization

While prepared statements are your primary defense, input validation adds an extra layer of security.

Implement Type Checking

// PHP example
if (!is_numeric($user_id)) {
    throw new Exception("Invalid user ID");
}

$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id); // 'i' indicates integer
$stmt->execute();

Use Allowlists for Valid Input

// PHP example for validating a username
function isValidUsername($username) {
    return preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username);
}

if (!isValidUsername($username)) {
    throw new Exception("Invalid username format");
}

Sanitize Output When Displaying Data

// PHP example
echo htmlspecialchars($user_data, ENT_QUOTES, 'UTF-8');

3. Use ORM Libraries

Object-Relational Mapping (ORM) libraries often include built-in protection against SQL injection.

PHP (Laravel Eloquent)

// Instead of raw queries, use the ORM
$users = User::where('status', 'active')
             ->where('role', 'admin')
             ->get();

JavaScript (Sequelize)

const { Op } = require("sequelize");
const users = await User.findAll({
  where: {
    username: username,
    status: {
      [Op.eq]: 'active'
    }
  }
});

Python (SQLAlchemy)

from sqlalchemy import select
from models import User

stmt = select(User).where(User.username == username)
result = session.execute(stmt)
user = result.scalar_one_or_none()

4. Implement Least Privilege

Limit database user permissions to reduce the impact of a successful attack.

-- Create a restricted user for your application
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_user'@'localhost';

-- Deny destructive permissions
REVOKE DROP, CREATE, ALTER ON myapp.* FROM 'app_user'@'localhost';

5. Use Stored Procedures

Stored procedures can add an extra layer of security by abstracting direct table access.

-- Create a stored procedure
DELIMITER //
CREATE PROCEDURE get_user_by_username(IN user_name VARCHAR(50))
BEGIN
    SELECT id, username, email, created_at
    FROM users
    WHERE username = user_name;
END //
DELIMITER ;

-- Call the procedure from your application
CALL get_user_by_username(?);

6. Implement Error Handling

Custom error handlers prevent leaking sensitive information through error messages.

// PHP example
try {
    // Database operations
} catch (Exception $e) {
    // Log the real error for developers
    error_log($e->getMessage());
    
    // Show generic error to users
    echo "An error occurred. Please try again later.";
}

7. Use a Web Application Firewall (WAF)

A WAF can help detect and block SQL injection attempts before they reach your application.

8. Perform Regular Security Testing

• Use automated tools to scan for SQL injection vulnerabilities
• Conduct code reviews focused on database interactions
• Implement continuous security testing in your CI/CD pipeline

Real-World Examples

Vulnerable Code vs. Secure Code

Vulnerable:

$search = $_GET['search'];
$query = "SELECT * FROM products WHERE name LIKE '%$search%'";
$result = mysqli_query($connection, $query);

Secure:

$search = $_GET['search'];
$stmt = $mysqli->prepare("SELECT * FROM products WHERE name LIKE ?");
$searchParam = "%$search%";
$stmt->bind_param("s", $searchParam);
$stmt->execute();
$result = $stmt->get_result();

Dynamic Queries

Vulnerable:

$sort = $_GET['sort'];
$query = "SELECT * FROM products ORDER BY $sort";
$result = mysqli_query($connection, $query);

Secure:

$allowedColumns = ['id', 'name', 'price', 'created_at'];
$sort = $_GET['sort'];

if (!in_array($sort, $allowedColumns)) {
    $sort = 'id'; // Default safe value
}

$query = "SELECT * FROM products ORDER BY $sort";
$result = mysqli_query($connection, $query);

Conclusion

SQL injection vulnerabilities can have devastating consequences, but they’re also highly preventable. By following this checklist, you can significantly reduce the risk of SQL injection attacks in your MySQL-based applications.

Remember that security is an ongoing process. Stay informed about new vulnerabilities and attack vectors, and regularly update your security practices. Always assume that user input is malicious until proven otherwise, and implement multiple layers of protection.

By making these practices part of your development workflow, you’ll create more secure applications and better protect your users’ data.