[Previous] [TOC] [Next]

Prepared Statements with MySQL

$stmt = mysqli_prepare();
mysqli_stmt_execute($stmt);

The new MySQL extension offers a way to both avoid SQL injection and to speed up SQL statements: so-called prepared statements. Within them, you provide placeholders for any dynamic data you are using in the SQL code. You then assign values to those placeholders. The MySQL extension then takes care of all the rest, including escaping of special characters.

Using Prepared Statements with MySQL
<?php
  if ($db = @mysqli_connect('localhost', 'user',
    'password')) {
    require_once 'stripFormSlashes.inc.php';
    mysqli_select_db($db, 'hoshmand');
    $stmt = mysqli_prepare($db, 'INSERT INTO quotes
      (quote, author, year) VALUES (?, ?, ?)');
    $quote = mysqli_real_escape_string($db,
      $_POST['quote']);
    $author = mysqli_real_escape_string($db,
      $_POST['author']);
    $year = intval($_POST['year']);
    mysqli_stmt_bind_param($stmt, 'ssi', $quote,
      $author, $year);
    if (mysqli_stmt_execute($stmt)) {
      echo 'Quote saved.';
    } else {
      echo 'Error writing quote.';
    }
    mysqli_close($db);
  } else {
    echo 'Connection failed.';
  }
?>

So first, you prepare an SQL statement with mysqli_prepare(); as a placeholder character, you use a question mark:

$stmt = mysqli_prepare($db, 'INSERT INTO quotes
  (quote, author, year) VALUES (?, ?, ?)');

Then, you bind values to each parameter. First, you provide the statement returned by mysqli_prepare, then one-character codes for the values of all parameters (s for string, i for integer, d for double). Finally, you provide a list of values. Because these values are used by reference, you have to provide variables, not raw values.

mysqli_stmt_bind_param($stmt, 'ssi', $quote,
  $author, $year);

Finally, mysqli_stmt_execute() executes the prepared statement.

The ext/mysqli extension supports some of the new features or recent MySQL versions, including transactions. For this, the following functions are available:

  • mysqli_autocommit($db, false) deactivates autocommit

  • mysqli_rollback($db) rolls back all pending transactions

  • mysqli_commit($db) commits all pending transactions

When using auto_increment values, the functions mysql_insert_id() and mysqli_insert_id() return the value of the auto_increment field of the last INSERT SQL statement created.

[Previous] [TOC] [Next]