PhpHow-ToBeginner · 4 min read

How to Use Prepared Statement in PHP: Syntax and Example

In PHP, use mysqli or PDO to create prepared statements by preparing the SQL query with placeholders, binding parameters, and executing the statement. This helps prevent SQL injection and improves security when working with user input.

📐

Syntax

Prepared statements in PHP involve three main steps: prepare the SQL query with placeholders, bind the input parameters, and execute the statement.

prepare(): Prepares the SQL query with ? placeholders.
bind_param(): Binds variables to the placeholders with their types.
execute(): Runs the prepared statement with the bound values.

php

<?php
// Create connection
$conn = new mysqli('localhost', 'username', 'password', 'database');

// Prepare statement
$stmt = $conn->prepare('SELECT * FROM users WHERE email = ?');

// Bind parameters (s = string)
$email = 'user@example.com';
$stmt->bind_param('s', $email);

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

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

💻

Example

This example shows how to safely query a user by email using a prepared statement with mysqli. It prevents SQL injection by separating the query structure from the input data.

php

<?php
// Database connection
$conn = new mysqli('localhost', 'username', 'password', 'database');

// Check connection
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

// User input
$email = 'user@example.com';

// Prepare statement
$stmt = $conn->prepare('SELECT id, name FROM users WHERE email = ?');

// Bind parameter
$stmt->bind_param('s', $email);

// Execute
$stmt->execute();

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

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

// Close statement and connection
$stmt->close();
$conn->close();
?>

Output

User ID: 1, Name: John Doe

⚠️

Common Pitfalls

Common mistakes when using prepared statements include:

Not checking if prepare() returns false, which means the SQL is invalid.
Binding parameters with wrong types or wrong number of parameters.
Forgetting to execute the statement after binding.
Not closing the statement or connection, which can cause resource leaks.
Using string concatenation instead of prepared statements, which risks SQL injection.

php

<?php
// Wrong way: vulnerable to SQL injection
$email = "' OR '1'='1";
$sql = "SELECT * FROM users WHERE email = '" . $email . "'";
$result = $conn->query($sql);

// Right way: prepared statement
$stmt = $conn->prepare('SELECT * FROM users WHERE email = ?');
$stmt->bind_param('s', $email);
$stmt->execute();
$result = $stmt->get_result();
?>

📊

Quick Reference

Remember these key points when using prepared statements in PHP:

Use ? as placeholders in SQL.
Bind parameters with correct types: s for string, i for integer, d for double, b for blob.
Always check if prepare() succeeds.
Close statements and connections after use.

Function	Purpose
prepare()	Prepares SQL query with placeholders
bind_param(types, vars)	Binds variables to placeholders with types
execute()	Executes the prepared statement
get_result()	Fetches result set from executed statement
close()	Closes statement or connection

✅

Key Takeaways

Prepared statements separate SQL code from data to prevent SQL injection.

Always prepare, bind parameters with correct types, then execute the statement.

Check for errors after preparing the statement to avoid runtime issues.

Close statements and database connections to free resources.

Never build SQL queries by concatenating user input directly.