PHP 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:

ParameterData type of the bound parameter
iinteger
ddouble
sstring
bblob

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