MySQL Prepared

What Is a Prepared Statement?

In simple terms: A prepared statement is an SQL query with blanks (?) where the values go. You send the query to MySQL first, and the values separately. MySQL treats the values strictly as data — never as part of the SQL command — so no input, however strange or malicious, can change what the query does.

Technically: With prepared statements, MySQL parses and plans the SQL template once (prepare), then executes it with bound parameters (execute), possibly many times. Because SQL code and data travel separately, prepared statements are the primary defense against SQL injection. MySQLi supports them through mysqli::prepare() and the mysqli_stmt class, plus the shortcuts mysqli_stmt::execute(array) (PHP 8.1+) and mysqli::execute_query() (PHP 8.2+).

The Problem: SQL Injection

<?php
$email = "x' OR '1'='1";   // submitted through a login or search form

$unsafeSql = "SELECT id, name FROM customers WHERE email = '$email'";
echo $unsafeSql . "\n";

Output

SELECT id, name FROM customers WHERE email = 'x' OR '1'='1'

The input closed the string with ' and added OR '1'='1', a condition that is always true. Executed, this query would return every customer. Similar tricks can bypass logins, read other tables, or delete data.

The Same Lookup With a Prepared Statement

<?php
require __DIR__ . '/src/db.php';

$email = "x' OR '1'='1";

$result = db()->execute_query('SELECT id, name FROM customers WHERE email = ?', [$email]);

echo "Matching customers: " . $result->num_rows . "\n";

Output

Matching customers: 0

MySQL searched for a customer whose email is literally x' OR '1'='1 — and found none. The quotes inside the value have no special meaning.

The Prepared Statement Lifecycle

1. prepare  ──►  SELECT name, price, stock FROM products WHERE sku = ?   (parsed once)
2. bind     ──►  ? = 'PEN-01'
3. execute  ──►  MySQL runs the plan with the value
4. fetch    ──►  read the result rows
   (repeat 2–4 with other values)

Step by Step With bind_param()

<?php
require __DIR__ . '/src/db.php';

$db = db();

$stmt = $db->prepare('SELECT name, price, stock FROM products WHERE sku = ?');
$stmt->bind_param('s', $sku);

foreach (['PEN-01', 'LAMP-01', 'NOPE-99'] as $sku) {
    $stmt->execute();
    $product = $stmt->get_result()->fetch_assoc();

    echo $product === null
        ? "$sku: not found\n"
        : "$sku: {$product['name']}, ₹{$product['price']}, {$product['stock']} in stock\n";
}

$stmt->close();

Output

PEN-01: Gel Pen, ₹20.00, 120 in stock
LAMP-01: Desk Lamp, ₹1299.00, 8 in stock
NOPE-99: not found

How It Works

1. prepare() sends the template with one ? placeholder. MySQL parses it and returns a statement object.
2. bind_param('s', $sku) binds the variable $sku (by reference) as a string parameter.
3. On each loop iteration, $sku changes; execute() sends its current value.
4. get_result() returns a mysqli_result from which rows are fetched as arrays. fetch_assoc() returns null when there are no (more) rows.
5. The statement is prepared once and executed three times.

Type Characters for bind_param()

The type string has one character per placeholder, in order: bind_param('sdi', $name, $price, $stock).

Shortcuts in Modern PHP

<?php
require __DIR__ . '/src/db.php';

$db = db();

// One-off query: execute_query()
$customer = $db->execute_query('SELECT name, city FROM customers WHERE id = ?', [3])->fetch_assoc();
echo "{$customer['name']} lives in {$customer['city']}\n";

// Repeated execution: prepare once, execute with arrays
$stmt = $db->prepare('SELECT name FROM products WHERE id = ?');
foreach ([2, 5] as $id) {
    $stmt->execute([$id]);
    echo "Product $id: " . $stmt->get_result()->fetch_column() . "\n";
}

Output

Charu lives in Chennai
Product 2: Notebook A5
Product 5: Wireless Mouse

Sending values as strings works for almost all queries, because MySQL converts them to the column types. One notable exception is LIMIT, which needs a real integer; bind it with bind_param('i', ...). (fetch_column() returns the first column of the next row; it was added in PHP 8.1.)

Result Types With Prepared Statements

Prepared statements use MySQL's binary protocol, which returns integers and floats as native PHP types:

<?php
$row = db()->execute_query('SELECT id, stock, price FROM products WHERE sku = ?', ['PEN-01'])->fetch_assoc();

var_dump($row['id'], $row['stock'], $row['price']);

Output

int(1)
int(120)
string(5) "20.00"

DECIMAL values are always returned as strings, which preserves their exact value. Convert them deliberately, for example to integer paise for calculations, or format them directly for display.

Dynamic IN (...) Lists

A placeholder represents exactly one value. For a list, generate one placeholder per element:

<?php
$skus = ['PEN-01', 'MOUSE-01', 'NOPE-99'];

$placeholders = implode(', ', array_fill(0, count($skus), '?'));

$result = db()->execute_query(
    "SELECT name FROM products WHERE sku IN ($placeholders) ORDER BY id",
    $skus
);

echo implode(', ', array_column($result->fetch_all(MYSQLI_ASSOC), 'name')) . "\n";

Output

Gel Pen, Wireless Mouse

An empty list would produce invalid SQL (IN ()), so check for $skus === [] before querying.

What Cannot Be Bound: Identifiers

Placeholders work only for values. Table names, column names, sort directions, and SQL keywords cannot be parameters. When they depend on input (such as a "sort by" option), map the input to an allow-list:

<?php
$sortOptions = [
    'price_asc' => 'price ASC',
    'price_desc' => 'price DESC',
    'name' => 'name ASC',
];

$sort = $sortOptions[$_GET['sort'] ?? ''] ?? 'name ASC';

$stmt = db()->prepare("SELECT name, price FROM products ORDER BY $sort LIMIT ?");
$limit = 2;
$stmt->bind_param('i', $limit);
$stmt->execute();

foreach ($stmt->get_result() as $row) {
    echo "{$row['name']}: ₹{$row['price']}\n";
}

Output (for ?sort=price_desc)

Desk Lamp: ₹1299.00
Backpack: ₹899.00

Only SQL fragments written in your code can reach the query; the user merely chooses between them. LIMIT is bound as an integer with bind_param('i', ...). A mysqli_result can be iterated directly with foreach.

Older Style: bind_result()

Without get_result() (rare today; it requires the standard mysqlnd driver), results are bound to variables:

<?php
$stmt = db()->prepare('SELECT name, stock FROM products WHERE category = ? ORDER BY id');
$category = 'stationery';
$stmt->bind_param('s', $category);
$stmt->execute();
$stmt->bind_result($name, $stock);

while ($stmt->fetch()) {
    echo "$name ($stock)\n";
}

Output

Gel Pen (120)
Notebook A5 (45)

fetch() fills the bound variables with the next row and returns null when there are no more rows.

Common Mistakes

Concatenating Values Into SQL "Because It Is Internal"

Effect: Values from databases, files, or APIs can also contain quotes or malicious content.

Fix: Use placeholders for every value.

Quoting Placeholders

Effect: WHERE email = '?' searches for a literal question mark.

Fix: Write placeholders without quotes: WHERE email = ?.

Trying to Bind Identifiers

Effect: Syntax errors or literal strings instead of column names.

Fix: Use allow-lists for table names, columns, and sort directions.

Binding Values Instead of Variables With bind_param()

Effect: bind_param('s', 'PEN-01') fails, because parameters are bound by reference.

Fix: Bind variables, or pass values to execute([...]) / execute_query().

Mismatched Type String

Effect: An error is thrown when the number of type characters differs from the number of bound variables or placeholders.

Fix: Use exactly one type character per placeholder.

Best Practices

• Use prepared statements for every query that contains values from outside the code.
• Use execute_query() for one-off queries and prepare() for repeated execution.
• Generate placeholder lists for IN (...); never interpolate the values.
• Map dynamic identifiers (columns, sort orders) to allow-listed SQL fragments.
• Treat DECIMAL results as strings and convert deliberately.

Official reference: https\://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php