SQL Security: Prevent Injection and Unsafe Queries

SQL security starts with a simple rule: do not build SQL by concatenating visitor input. Validate input for business rules, then pass values through prepared statements.

Use prepared statements for values

<?php
require "config.php";

$catId = filter_input(INPUT_GET, 'cat_id', FILTER_VALIDATE_INT);
if ($catId === false || $catId === null) {
    http_response_code(400);
    exit('Invalid category');
}

$stmt = $dbo->prepare('SELECT id, name FROM category WHERE id = :id');
$stmt->execute([':id' => $catId]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
?>

Validate numeric input when the application requires a number

<?php
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false || $id === null) {
    exit('Data error');
}
?>

Validate a list of integer IDs

The old tutorial used removed split() and each(). Use explode()/foreach, validate each element, and create one placeholder per value.

<?php
$raw = '2,3,5';
$ids = array_values(array_filter(array_map('trim', explode(',', $raw)), 'strlen'));
foreach ($ids as $id) {
    if (filter_var($id, FILTER_VALIDATE_INT) === false) {
        exit('Data error');
    }
}

$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $dbo->prepare("SELECT id, name FROM student WHERE id IN ($placeholders)");
$stmt->execute(array_map('intval', $ids));
?>

Validate text for the rule you actually need

Validation and SQL safety are different layers. If a username is defined as letters and digits only, ctype_alnum() can enforce that rule; the database query should still use parameters.

<?php
$username = trim((string)($_POST['username'] ?? ''));
if ($username === '' || !ctype_alnum($username)) {
    exit('Data error');
}
?>

For letters, numbers, spaces, dots, hyphens and underscores:

<?php
$value = trim((string)($_POST['value'] ?? ''));
if (!preg_match('/^[a-z0-9 ._-]+$/i', $value)) {
    exit('Data error');
}
?>

Identifiers cannot be bound like values

Prepared placeholders represent data values, not table names, column names or SQL keywords. For user-selectable sorting or columns, map the request to a fixed allow-list.

<?php
$allowedSort = ['name', 'mark', 'class'];
$requested = (string)($_GET['sort'] ?? 'name');
$sort = in_array($requested, $allowedSort, true) ? $requested : 'name';

$sql = "SELECT id, name, class, mark FROM student ORDER BY $sort";
$stmt = $dbo->query($sql);
?>

Do not expose database errors to visitors

Log technical details server-side and return a generic message to the visitor. Avoid publishing connection strings, SQL text, stack traces or database credentials in browser output.

Related tutorials

PDO · MySQLi · IN() · Safe dynamic ORDER BY · Record existence checks

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: