MySQL Prepared Statements
Secure and efficient database queries with prepared statements
🔒 What are Prepared Statements?
Prepared statements are a secure way to execute SQL queries by separating SQL logic from data. They protect against SQL injection attacks and improve query performance for repeated executions.
<?php
// Prepared statement example
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();
?>
Key Benefits
Security
Prevents SQL injection attacks
// Safe from injection
$stmt->bind_param("s", $input);Performance
Faster for repeated queries
// Prepare once, execute many
$stmt->prepare($sql);Clean Code
Separates SQL from data
// Clear separation
VALUES (?, ?)Reusable
Execute with different values
// Reuse statement
$stmt->execute();🔹 Basic Prepared Statement (INSERT)
Use prepared statements to safely insert data into your database. The question marks (?) are placeholders that get replaced with actual values securely.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
// Bind parameters (s = string, i = integer)
$stmt->bind_param("ssi", $name, $email, $age);
// Set values and execute
$name = "John Doe";
$email = "[email protected]";
$age = 30;
$stmt->execute();
echo "Record inserted successfully";
$stmt->close();
$conn->close();
?>Output:
Record inserted successfully
🔹 Parameter Types
Understanding bind_param types is essential for correct data handling. Each letter represents a data type for the corresponding placeholder.
Data Type Codes:
- i - Integer (whole numbers)
- d - Double (decimal numbers)
- s - String (text)
- b - Blob (binary data)
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
$stmt = $conn->prepare("INSERT INTO products (name, price, quantity, description) VALUES (?, ?, ?, ?)");
// s = string, d = double, i = integer, s = string
$stmt->bind_param("sdis", $name, $price, $quantity, $description);
$name = "Laptop";
$price = 999.99;
$quantity = 50;
$description = "High-performance laptop";
$stmt->execute();
echo "Product added successfully";
$stmt->close();
$conn->close();
?>Output:
Product added successfully
🔹 Prepared SELECT Statement
Retrieve data securely using prepared statements. This prevents SQL injection when filtering results based on user input.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Prepare SELECT statement
$stmt = $conn->prepare("SELECT name, email, age FROM users WHERE age > ?");
$stmt->bind_param("i", $min_age);
$min_age = 25;
$stmt->execute();
// Bind result variables
$stmt->bind_result($name, $email, $age);
// Fetch and display results
echo "<h3>Users older than 25:</h3>";
while ($stmt->fetch()) {
echo "Name: $name, Email: $email, Age: $age<br>";
}
$stmt->close();
$conn->close();
?>Output:
Users older than 25:
Name: Alice, Email: [email protected], Age: 30
Name: Bob, Email: [email protected], Age: 28
🔹 Multiple Executions
Prepare once and execute multiple times with different values. This is highly efficient for batch operations.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
// Prepare once
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
// Execute multiple times with different values
$users = [
['Alice', '[email protected]'],
['Bob', '[email protected]'],
['Charlie', '[email protected]']
];
foreach ($users as $user) {
$name = $user[0];
$email = $user[1];
$stmt->execute();
}
echo "All users inserted successfully";
$stmt->close();
$conn->close();
?>Output:
All users inserted successfully
🔹 Error Handling
Always check for errors when working with prepared statements to ensure data integrity and debug issues effectively.
<?php
$conn = new mysqli("localhost", "root", "", "myDB");
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
$stmt->bind_param("ss", $name, $email);
$name = "Test User";
$email = "[email protected]";
if ($stmt->execute()) {
echo "Success! New record ID: " . $stmt->insert_id;
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>Output:
Success! New record ID: 15
💡 Best Practices
- Always use prepared statements for user input
- Close statements after use to free resources
- Check for errors after prepare() and execute()
- Use appropriate data type codes in bind_param()
- Reuse prepared statements for multiple executions