PHP MySQL Prepared Statements

A prepared statement sends the SQL query and the data to MySQL separately. First you send a query with placeholders (?), then you send the values. MySQL treats these values only as data, never as a part of SQL code.

Because of this, prepared statements protect your website from SQL injection. They are the right way to run queries when the values come from a form, URL or any user input.

---

Why not put variables inside the query?

Look at this login-like code. The email comes directly from the form and it is added in the SQL text.

<?php
// Unsafe code
$email = $_POST["email"];
$sql = "SELECT * FROM users WHERE email = '$email'";
?>

If the visitor types ' OR '1'='1 in the email field, MySQL receives this query:

SELECT * FROM users WHERE email = '' OR '1'='1'

The condition '1'='1' is always true, so the query returns every user in the table. With a prepared statement this can not happen, because the typed text is never mixed with SQL.

---

Steps to use a prepared statement

• Prepare: write the SQL with ? placeholders and send it using prepare().
• Bind: connect PHP variables with the placeholders using bind_param().
• Execute: run the statement using execute().
• Get result and close: read the rows (for SELECT) and close the statement.

Syntax

$stmt = $conn->prepare("INSERT INTO users (name, mobile, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $mobile, $email);
$stmt->execute();

The first parameter of bind_param() is a string of types. One letter for each placeholder, in the same order.

Letter	Type
s	String
i	Integer
d	Double (decimal number)
b	Blob (binary data)

---

Examples

Example #1: Insert a record

We have three placeholders and three string values, so the type string is “sss”.

<?php
require_once('db-connection.php');

$name = "Ram";
$mobile = "9898989898";
$email = "ram@example.com";

$stmt = $conn->prepare("INSERT INTO users (name, mobile, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $mobile, $email);
$stmt->execute();

echo "New record inserted. ID: " . $stmt->insert_id;

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

Output:

---

Example #2: Insert many records with one statement

The statement is prepared one time, and then we run it again and again with different values. This is faster than preparing the same query every time.

<?php
require_once('db-connection.php');

$users = [
    ["Sanjay", "9898969696", "sanjay@example.com"],
    ["Pankit", "9898979797", "pankit@example.com"],
    ["Mohan",  "9898989797", "mohan@example.com"]
];

$stmt = $conn->prepare("INSERT INTO users (name, mobile, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $mobile, $email);

$count = 0;
foreach ($users as $user) {
    [$name, $mobile, $email] = $user;
    $stmt->execute();
    $count++;
}

echo $count . " records inserted.";

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

Output:

Notice that we bind the variables only once. When execute() runs, MySQL reads the current values of $name, $mobile and $email.

---

Example #3: Select with a condition

For SELECT we call get_result() to get the rows, then read them like in a normal query. Here we search a user by email.

<?php
require_once('db-connection.php');

$email = "ram@example.com";

$stmt = $conn->prepare("SELECT id, name, mobile FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();

$result = $stmt->get_result();

if ($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . "<br>";
    echo "Name: " . $row["name"] . "<br>";
    echo "Mobile: " . $row["mobile"];
} else {
    echo "No user found.";
}

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

Output:

The get_result() method needs the mysqlnd driver. It is enabled by default in almost all modern PHP installations.

---

Example #4: Update using two placeholders

Here the first value is a string (mobile) and the second is an integer (id), so the types are “si”.

<?php
require_once('db-connection.php');

$mobile = "9111111111";
$id = 1;

$stmt = $conn->prepare("UPDATE users SET mobile = ? WHERE id = ?");
$stmt->bind_param("si", $mobile, $id);
$stmt->execute();

echo "Rows updated: " . $stmt->affected_rows;

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

Output:

---

Common mistakes

• Putting quotes around ?. Write WHERE email = ?, not WHERE email = '?'. MySQL adds the quotes for you.
• Wrong number of types. If you have 3 placeholders, the type string must have exactly 3 letters.
• Passing a value directly in bind_param(). It needs variables, so bind_param("s", "Ram") gives an error. Store the value in a variable first.
• Using ? for table or column names. Placeholders work only for values, not for names of tables or columns.

---

Summary

• Prepared statements keep SQL and data separate, so they stop SQL injection.
• The steps are prepare(), bind_param(), execute() and then close.
• Type letters: s (string), i (integer), d (double), b (blob).
• Use get_result() to read rows of a SELECT query.
• One prepared statement can run many times with different values.
• Always use them when the value comes from the user.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.