How to Use Prepared Statements for Security in PHP

Use prepared statements in PHP by preparing your SQL query with placeholders, then binding user inputs to these placeholders before execution. This method prevents SQL injection by separating code from data, ensuring user inputs cannot alter the query structure.

Syntax

Prepared statements in PHP use the mysqli or PDO extension. The main parts are:

• Prepare: Create a SQL query with placeholders (like ? or named parameters).
• Bind: Attach user input values to the placeholders safely.
• Execute: Run the query with the bound values.

php

<?php
// Using mysqli
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
?>

Example

This example shows how to securely query a user by email using mysqli prepared statements. It prevents SQL injection by separating the SQL code from user input.

php

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");

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

$email = "[e-mail]"; // User input

// Prepare the SQL statement with a placeholder
$stmt = $mysqli->prepare("SELECT id, name FROM users WHERE email = ?");

// Bind the user input to the placeholder as a string
$stmt->bind_param("s", $email);

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

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

// Fetch and display user data
if ($row = $result->fetch_assoc()) {
    echo "User ID: " . $row['id'] . "\n";
    echo "Name: " . $row['name'] . "\n";
} else {
    echo "No user found.";
}

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

Output

User ID: 1
Name: Alice

Common Pitfalls

Common mistakes when using prepared statements include:

• Not using placeholders and concatenating user input directly, which causes SQL injection risks.
• Binding parameters with wrong types or forgetting to bind them.
• Not checking if prepare() or execute() succeeded.

Always validate and bind inputs properly and check for errors.

php

<?php
// Wrong way (vulnerable to SQL injection):
$email = $_GET['email'];
$query = "SELECT * FROM users WHERE email = '" . $email . "'";
$result = $mysqli->query($query);

// Right way (safe):
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
?>

Quick Reference

• prepare(): Create a statement with placeholders.
• bind_param(): Bind variables to placeholders with types (s = string, i = integer, d = double, b = blob).
• execute(): Run the prepared statement.
• get_result(): Retrieve the result set after execution.
• close(): Close the statement and connection.

Key Takeaways

Always use prepared statements to separate SQL code from user input and prevent SQL injection.

Use placeholders in your SQL and bind user inputs with the correct data types.

Check for errors after preparing and executing statements to handle issues gracefully.

Never concatenate user input directly into SQL queries.

Close your statements and database connections to free resources.