How to Use Prepared Statements in PHP to Prevent SQL Injection

SQL injection remains one of the most common security vulnerabilities in web applications. Fortunately, PHP offers built‑in mechanisms—prepared statements—that separate SQL code from user data, making injection virtually impossible when used correctly. This article walks you through the concept, the two main PHP extensions (PDO and MySQLi), and practical code examples you can copy‑paste into your projects.

Why Prepared Statements Are Essential

Traditional string concatenation (e.g., $sql = "SELECT * FROM users WHERE email='$email'";) leaves the query open to manipulation. An attacker can inject malicious SQL that changes the query’s logic, extracts data, or even destroys tables.

Prepared statements solve this problem by:

• Separating query structure from data. The database parses the SQL first, then binds the parameters.
• Automatically escaping values. No manual escaping is required, reducing human error.
• Improving performance. The same statement can be executed multiple times with different parameters without re‑parsing.

How Prepared Statements Work

1. Prepare the SQL Template

The database receives a query with placeholders (? or named parameters like :email) and compiles it.

2. Bind Values to Placeholders

Values are sent separately and safely bound to the compiled statement.

3. Execute the Statement

The database runs the pre‑compiled query using the bound values, guaranteeing that the data cannot alter the query’s structure.

Implementation Using PDO (Preferred)

PDO (PHP Data Objects) provides a consistent API for many database systems, making it the go‑to choice for new projects.

Step‑by‑Step Example

<?php
// 1️⃣  Create a PDO connection (replace credentials accordingly)
$dsn = 'mysql:host=localhost;dbname=your_database;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
try {
    $pdo = new PDO($dsn, 'db_user', 'db_pass', $options);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

// 2️⃣  Prepare the SQL with named placeholders
$sql = "SELECT id, username, email FROM users WHERE email = :email AND status = :status";
$stmt = $pdo->prepare($sql);

// 3️⃣  Bind values (automatic type detection, or specify with PDO::PARAM_*)
$email  = $_POST['email'] ?? '';
$status = 'active';
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->bindParam(':status', $status, PDO::PARAM_STR);

// 4️⃣  Execute the statement
$stmt->execute();

// 5️⃣  Fetch results
$user = $stmt->fetch();

if ($user) {
    echo "Welcome, " . htmlspecialchars($user['username']);
} else {
    echo "No matching user found.";
}
?>

Key points:

• Use prepare() once, then execute() as many times as needed.
• Never concatenate user input into the SQL string.
• Always set PDO::ATTR_ERRMODE to ERRMODE_EXCEPTION for proper error handling.

Implementation Using MySQLi (Procedural & Object‑Oriented)

If you’re working with legacy code that already uses MySQLi, you can still achieve the same level of security.

Object‑Oriented Example

<?php
// 1️⃣  Connect
$mysqli = new mysqli('localhost', 'db_user', 'db_pass', 'your_database');
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// 2️⃣  Prepare the statement
$stmt = $mysqli->prepare("INSERT INTO orders (user_id, product_id, quantity) VALUES (?, ?, ?)");
if (!$stmt) {
    die('Prepare failed: ' . $mysqli->error);
}

// 3️⃣  Bind parameters (i = integer, s = string, d = double, b = blob)
$userId    = $_POST['user_id'];
$productId = $_POST['product_id'];
$quantity  = $_POST['quantity'];
$stmt->bind_param('iii', $userId, $productId, $quantity);

// 4️⃣  Execute
if ($stmt->execute()) {
    echo "Order saved successfully.";
} else {
    echo "Execute failed: " . $stmt->error;
}

// 5️⃣  Clean up
$stmt->close();
$mysqli->close();
?>

Procedural Example

<?php
$link = mysqli_connect('localhost', 'db_user', 'db_pass', 'your_database');
if (!$link) {
    die('Connect Error: ' . mysqli_connect_error());
}

$sql = "SELECT name, price FROM products WHERE category = ?";
$stmt = mysqli_prepare($link, $sql);
$category = $_GET['cat'] ?? 'default';
mysqli_stmt_bind_param($stmt, 's', $category);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $name, $price);

while (mysqli_stmt_fetch($stmt)) {
    echo htmlspecialchars($name) . ' - $' . htmlspecialchars($price) . '<br>';
}

mysqli_stmt_close($stmt);
mysqli_close($link);
?>

Common Pitfalls & How to Avoid Them

• Mixing placeholders with concatenated strings. Keep the entire query inside prepare().
• Using the wrong placeholder type. PDO accepts both named (:name) and positional (?) placeholders—choose one style and stay consistent.
• Forgetting to set the character set. Use charset=utf8mb4 in the DSN or mysqli_set_charset() to prevent encoding‑based injection.
• Not handling exceptions. Wrap PDO operations in try/catch blocks; for MySQLi, check return values and use mysqli_error().

Best Practices for Secure Database Access

1. Always use prepared statements. Treat them as non‑negotiable for any query that includes user input.
2. Validate and sanitize input. While prepared statements protect against injection, validation ensures data integrity (e.g., email format, numeric ranges).
3. Limit database privileges. Use a dedicated DB user with only the permissions required (SELECT, INSERT, UPDATE, DELETE as needed).
4. Enable HTTPS. Prevent man‑in‑the‑middle attacks that could alter submitted data before it reaches your server.
5. Log errors securely. Do not expose raw SQL errors to users; log them to a file with restricted access.

Conclusion

Prepared statements are a powerful, built‑in defense against SQL injection in PHP. By separating query logic from data, they make your code safer, cleaner, and often faster. Whether you choose PDO for its flexibility or MySQLi for legacy compatibility, the steps are straightforward:

1. Establish a secure connection (set the charset).
2. Prepare the SQL with placeholders.
3. Bind user‑supplied values.
4. Execute the statement and handle results.

Implement these practices today, and you’ll dramatically reduce the risk of one of the most dangerous web vulnerabilities.

Ready to secure your PHP applications? Contact us for a code review or custom security audit.