PHP PHP MySQLi Prepared statements in MySQLi
Example
Please read Preventing SQL injection with Parametrized Queries for a complete discussion of why prepared statements help you secure your SQL statements from SQL Injection attacks
The $conn variable here is a MySQLi object. See MySQLi connect example for more details.
For both examples, we assume that $sql is
$sql = "SELECT column_1
FROM table
WHERE column_2 = ?
AND column_3 > ?";
The ? represents the values we will provide later. Please note that we do not need quotes for the placeholders, regardless of the type. We can also only provide placeholders in the data portions of the query, meaning SET, VALUES and WHERE. You cannot use placeholders in the SELECT or FROM portions.
Object oriented style
if ($stmt = $conn->prepare($sql)) {
$stmt->bind_param("si", $column_2_value, $column_3_value);
$stmt->execute();
$stmt->bind_result($column_1);
$stmt->fetch();
//Now use variable $column_1 one as if it were any other PHP variable
$stmt->close();
}
Procedural style
if ($stmt = mysqli_prepare($conn, $sql)) {
mysqli_stmt_bind_param($stmt, "si", $column_2_value, $column_3_value);
mysqli_stmt_execute($stmt);
// Fetch data here
mysqli_stmt_close($stmt);
}
The first parameter of $stmt->bind_param or the second parameter of mysqli_stmt_bind_param is determined by the data type of the corresponding parameter in the SQL query:
| Parameter | Data type of the bound parameter |
|---|---|
i | integer |
d | double |
s | string |
b | blob |
Your list of parameters needs to be in the order provided in your query.
In this example si means the first parameter (column_2 = ?) is string and the second parameter (column_3 > ?) is integer.
For retrieving data, see How to get data from a prepared statement
