Skip to content
Tutorials Center
My learning

Topics

PHP Reference ยท Databases (PDO)

PHP PDO::prepare() method

Reference for PHP PDO::prepare(): create a prepared statement with ? or :name placeholders, run it with execute(), reuse it in a loop and avoid SQL injection.

PDO::prepare() sends an SQL statement to the database with placeholders where the values will go, and returns a PDOStatement. You then call execute() with the actual values. The SQL text and the data travel separately, so a value can never change the structure of the query. That makes prepared statements the standard defense against SQL injection.

A prepared statement can also be executed many times with different values, which is handy for inserting a batch of rows.

PHP
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

Try it

PHP Positional and named placeholders

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

PHP LIKE, IN lists and errors

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Syntax

PHP
PDO::prepare(string $query, array $options = []): PDOStatement|false
Name Description
$query The SQL statement with ? (positional) or :name (named) placeholders. Use one style per statement, not both.
$options Optional driver options, for example [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]. Rarely needed.

Return value: a PDOStatement object, or false on failure when the error mode is not exception mode.

Errors / Exceptions: with PDO::ERRMODE_EXCEPTION (the default since PHP 8.0), a statement the database cannot compile throws PDOException. SQLite checks the syntax while preparing, so a typo fails at prepare(). MySQL with emulated prepares only reports many errors at execute(), so wrap both calls in the same try block.

Usage notes

  • Values go through execute(), bindValue() or bindParam(). Never concatenate input into the SQL string, not even "after escaping".
  • Positional numbering starts at 1 when you use bindValue(); in the array passed to execute() the keys are simply 0, 1, 2...
  • Do not mix ? and :name in one statement.
  • Use each named placeholder once to be portable. Some drivers with native prepares refuse the same name twice; give them distinct names such as :q1 and :q2.
  • Placeholders stand for values only. They cannot replace table names, column names, ORDER BY directions or SQL keywords. For those, check the input against a fixed whitelist and then insert the allowed word into the SQL yourself.
  • execute($array) binds every value as a string (PDO::PARAM_STR). That is fine for most queries. When the type matters, for example LIMIT on MySQL with emulated prepares, use bindValue(..., PDO::PARAM_INT).
  • Wildcards for LIKE are part of the value ('%' . $term . '%'). Escape % and _ in the user's text if they must match literally.
  • Reuse the statement when you run the same SQL repeatedly; the database parses it only once.
  • MySQL emulation. By default the MySQL driver emulates prepares inside PHP. Set PDO::ATTR_EMULATE_PREPARES => false for real server-side prepared statements. See PDO.