Prepared Statements with MySQLi

Start with fixed SQL such as SELECT name FROM products WHERE id = ?. The question mark stands for one value, and it should not be put inside quotes in the SQL. MySQL receives the value through a separate binding operation. A malicious-looking name remains a name rather than changing the WHERE clause. Do not concatenate other unvalidated fragments into the statement and then claim the whole query is safe because one parameter was bound. Sort directions, table names, and column identifiers are SQL structure, so choose them from application-owned allowlists. Prepared statements protect the database syntax boundary; authorization still determines which rows the user may access, and validation still checks the business meaning of inputs.

bind_param() takes a type string followed by variables. i represents an integer, d a floating-point number, s a string, and b supports binary handling. The type string length must match the placeholder count and variable count. Variables are bound by reference, so a later assignment changes the value used by the next execution. Passing an expression directly may fail because it is not a suitable reference target. Create a validated variable first. Do not use integer binding as a replacement for validation: unexpected text can be converted in ways your business rule never intended. For decimal money, a checked decimal string can be more appropriate than a binary floating value. Document the schema representation before selecting the binding type.

After binding, execute() sends the current variables. You can change them and execute again when inserting a bounded list of records. Reuse avoids repeated preparation overhead and keeps the statement structure stable. For an all-or-nothing batch, begin a transaction under a supported engine and commit only after every intended execution succeeds. Do not suppress exceptions and continue while telling the user the entire batch succeeded. Record accepted and rejected rows if partial acceptance is the documented policy. Close the statement when its lifecycle ends, especially in long-running workers. Avoid retaining an unbounded number of prepared statements because server resources are finite. Measure repeated-query behavior with realistic data rather than assuming preparation fixes every performance problem.

bind_result() associates selected columns with PHP variables, and fetch() fills them one row at a time. Bind one result variable per selected column. A while-fetch loop can process rows without collecting them all, which suits large results under an appropriate buffering policy. get_result() offers a result object with fetch_assoc() on installations using mysqlnd; do not assume it exists in every environment. If a statement leaves unread results, later commands on the same connection may fail with synchronization errors. Consume, buffer, or free results according to the API before issuing unrelated operations. A missing row is a normal outcome, and nullable column values are different from no row. Make both distinctions explicit in repository methods.

One placeholder cannot represent an entire comma-separated IN list. If you accept a bounded list of validated IDs, create one question mark per ID and bind each value separately. The number of placeholders comes from a checked count, not raw SQL input. Handle an empty list before generating SQL because IN () is not the desired query. Impose a maximum number of IDs to prevent excessive query size or server work. Newer MySQLi convenience APIs have their own version requirements; use them only when the supported runtime includes them. For clarity in this course, the traditional prepare and bind flow remains the baseline. Dynamic identifiers still require allowlists even when dynamic value lists are safely bound.

Test an ordinary value, a quote-containing value, a missing record, zero, and malformed input rejected before binding. Include a name such as O'Reilly to demonstrate that apostrophes remain data rather than requiring manual SQL escaping. Test duplicate-key failure and a statement with the wrong parameter count in a disposable environment. Set strict reporting explicitly to avoid code relying on historical default modes. Do not show SQL exception text to visitors because it can reveal structure and infrastructure. Explain expected validation errors separately from unexpected server failures. A prepared query can still be slow, return excessive private data, or use an incorrect ownership predicate; review its complete meaning rather than only looking for question marks.

Write a function that retrieves a product name by integer ID using prepare(), bind_param(), execute(), bind_result(), and fetch(). The solution returns a nullable or explicitly tagged not-found result rather than reading a result variable without checking fetch(). Validate the ID before calling the function and enforce ownership when the feature requires it. Then adapt the function to accept a bounded list of IDs, generating one placeholder per validated ID and defining empty-list behavior. Test a valid ID, a missing ID, an apostrophe-containing product name, and a request that tries to submit SQL text instead of an integer. Do not introduce manual quote escaping around placeholders.

This repository helper uses strict MySQLi reporting, does not depend on mysqlnd, closes its statement in finally, and returns null when no product matches. Authorization belongs in its predicate when products are private. Its caller validates the requested identifier before invoking it.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
function productName(mysqli $db, int $id): ?string {
    $stmt = $db->prepare('SELECT name FROM products WHERE id = ?');
    try {
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $stmt->bind_result($name);
        return $stmt->fetch() ? $name : null;
    } finally {
        $stmt->close();
    }
}

With product 12 named Pen, productName($db, 12) returns the string Pen. A missing row returns null. This example requires products.name to be non-null; otherwise a tagged return shape should distinguish a null column from a missing row.

Compare PDO, CRUD, and security.

The MySQLi prepared statement guide explains preparation and binding APIs.