MySQL - SQL Injection

MySQL - Preventing SQL Injection

SQL Injection is a code injection technique that might destroy your database. It is one of the most common web hacking techniques. SQL injection is the placement of malicious code in SQL statements, via web page input. It occurs when an application uses user-provided data to construct SQL queries without properly validating or sanitizing it.

How a Classic SQL Injection Attack Works

Imagine a simple login form where the application builds a query by concatenating the user’s input directly into the SQL string:

// This is an example of VULNERABLE code. DO NOT USE THIS.
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = '" . $username . "' AND password = '" . $password . "';";

A legitimate user would enter their username and password. But an attacker could enter the following into the username field:

' OR '1'='1

The application would then construct this malicious SQL query:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'some_password';

Because the condition '1'='1' is always true, the WHERE clause evaluates to true for every row, and the query returns all users from the table. The attacker has successfully bypassed the login without a valid password.

The Modern Solution: Prepared Statements

The most effective way to prevent SQL injection is to always use prepared statements (also known as parameterized queries). This technique separates the SQL command structure from the data.

Here’s how it works:

1. Prepare: The SQL query is sent to the database server with placeholders (like ? or :name) instead of user data. The database parses and compiles this query template.
2. Bind: The application then sends the user-supplied data to the database separately.
3. Execute: The database combines the compiled template with the bound data and executes the query. Crucially, the data is treated strictly as data and cannot be interpreted as part of the SQL command.

Old methods like mysql_real_escape_string() are deprecated and unsafe. They are easy to implement incorrectly and do not provide the same level of protection as prepared statements.

Example: Secure Login with PHP (MySQLi)

Here is a secure implementation of the login check using PHP’s MySQLi extension and prepared statements.

<?php
// Database connection details
$host = 'localhost';
$db_user = 'your_db_user';
$db_pass = 'your_db_password';
$db_name = 'your_db_name';

$conn = new mysqli($host, $db_user, $db_pass, $db_name);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Get username from POST request
$username = $_POST['username'];

// 1. Prepare the statement with a placeholder
$stmt = $conn->prepare("SELECT user_id, password_hash FROM users WHERE username = ?");

if ($stmt === false) {
    die("Prepare failed: " . $conn->error);
}

// 2. Bind the user input to the placeholder
$stmt->bind_param("s", $username); // "s" means the parameter is a string

// 3. Execute the query
$stmt->execute();

// Get the result
$result = $stmt->get_result();

if ($result->num_rows === 1) {
    $user = $result->fetch_assoc();
    // Verify password using password_verify()
    if (password_verify($_POST['password'], $user['password_hash'])) {
        echo "Login successful!";
        // Proceed with creating a session, etc.
    } else {
        echo "Invalid username or password.";
    }
} else {
    echo "Invalid username or password.";
}

$stmt->close();
$conn->close();
?>

Example: Secure Data Fetching with Python

import mysql.connector
from mysql.connector import errorcode

def get_user_by_username(username):
    try:
        # Connection details should be stored securely, not hardcoded
        cnx = mysql.connector.connect(
            user='your_user',
            password='your_password',
            host='127.0.0.1',
            database='your_db'
        )
        cursor = cnx.cursor(dictionary=True)

        query = "SELECT user_id, username FROM users WHERE username = %s"

        # The connector handles the sanitization when you pass params as a tuple
        cursor.execute(query, (username,))

        user = cursor.fetchone()

        if user:
            print(f"Found user: {user}")
        else:
            print("User not found.")

    except mysql.connector.Error as err:
        print(f"Something went wrong: {err}")
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()
        if 'cnx' in locals() and cnx.is_connected():
            cnx.close()

# Example usage
get_user_by_username("admin")
# Try an injection attempt - it will safely find no user named "' OR '1'='1"
get_user_by_username("' OR '1'='1'")

Handling the LIKE Clause

Prepared statements also solve the challenge of using user input in a LIKE clause. You simply include the wildcard characters (%, _) in the data you bind, not in the SQL query string itself.

// PHP (MySQLi) example for a search feature
$searchTerm = $_GET['search'];

// Prepare the statement
$stmt = $conn->prepare("SELECT product_name FROM products WHERE product_name LIKE ?");

// Add wildcards to the variable to be bound
$likeTerm = "%$searchTerm%";

// Bind the parameter
$stmt->bind_param("s", $likeTerm);

$stmt->execute();
// ... fetch and display results ...

Security Best Practices

• Always use Prepared Statements: This is the golden rule. No exceptions.
• Principle of Least Privilege: Connect to the database with a user that has only the permissions it needs. For a web application’s public-facing parts, this user should not have permissions to DROP TABLE or alter schemas.
• Use an ORM: Object-Relational Mappers (e.g., Eloquent in Laravel, SQLAlchemy in Python, TypeORM in Node.js) often use prepared statements by default, adding another layer of abstraction and safety.
• Validate and Sanitize Input: While prepared statements are the primary defense, you should still validate user input on the application level to ensure it’s in the expected format (e.g., an email address looks like an email).
• Never Trust User Input: Treat all data coming from the client-side (forms, URL parameters, cookies, API requests) as potentially malicious until proven otherwise.