What is SQL Injection and How to Prevent It?

SQL injection occurs when an external value becomes part of the SQL structure instead of remaining data. Removing apostrophes from a form field is not a reliable repair. Review the code that builds and executes the query.

Bind values instead of concatenating them

This PDO example targets MySQL and assumes a products table with id, name, and price columns. Load credentials from the environment or configuration outside the public web directory. Do not put real passwords in the source file.

<?php
$id = filter_var($_GET['id'] ?? null, FILTER_VALIDATE_INT,
    ['options' => ['min_range' => 1]]);
if ($id === false || $id === null) {
    http_response_code(400);
    exit('Invalid product ID.');
}

$pdo = new PDO(
    (string) getenv('DB_DSN'),
    (string) getenv('DB_USER'),
    (string) getenv('DB_PASSWORD'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
     PDO::ATTR_EMULATE_PREPARES => false]
);
$stmt = $pdo->prepare(
    'SELECT id, name, price FROM products WHERE id = :id'
);
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$product = $stmt->fetch(PDO::FETCH_ASSOC);
if ($product === false) {
    http_response_code(404);
    exit('Product not found.');
}
echo htmlspecialchars($product['name'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

A MySQL DSN can look like mysql:host=127.0.0.1;dbname=app;charset=utf8mb4; the PDO MySQL driver must be installed. In production, let the application's error handler log failures privately and return a generic error response. Do not expose exception details or record credentials and entire submitted forms in logs.

Notice the separate responsibilities: validate the product ID, bind its value, then escape the product name for an HTML text context. HTML escaping does not secure SQL. Prepared statements do not automatically make HTML output safe either.

Choose dynamic identifiers from an allowlist

ORDER BY :column is not a way to bind a column name. Placeholders represent values. Map a public sorting option to an expression written by the application:

<?php
// $pdo is the trusted connection created above.
$orders = [
    'newest' => 'id DESC',
    'price_low' => 'price ASC, id ASC',
    'price_high' => 'price DESC, id ASC',
];
$requested = $_GET['sort'] ?? 'newest';
if (!is_string($requested) || !isset($orders[$requested])) {
    http_response_code(400);
    exit('Invalid sorting option.');
}
$stmt = $pdo->query(
    'SELECT id, name, price FROM products ORDER BY ' . $orders[$requested] . ' LIMIT 20'
);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

The appended expression comes from the fixed map, not from the request. Apply the same distinction to table names and filter operators. For an IN list, create a separate placeholder for each value; one comma-separated parameter is still a single value.

Preserve valid data

O'Reilly is a valid name. Deleting the apostrophe damages data without fixing the underlying query design. Validate numbers, email addresses, and enumerated choices against their actual requirements. Do not treat generic text sanitization, including the old FILTER_SANITIZE_STRING approach, as an injection defence.

Give the application account only the database privileges it needs, and keep schema migration credentials separate. A WAF may provide another defensive layer, but it does not repair unsafe query construction. Authentication also needs proper password-hash verification rather than comparing a submitted password with plaintext stored in SQL.

Test the repaired boundary

Use a small database in an environment you are authorized to test. Try an ordinary ID, zero, a negative number, a missing field, and an unexpected array such as id[]=1. Check that an apostrophe in a text field survives intact. Invalid sorting should produce 400, an absent product should produce 404, and neither response should reveal a database error.

References: PDO::prepare and OWASP SQL injection prevention.