skip to content

In PHP's PDO, how does bindParam() differ from bindValue(), and when does the PDO::PARAM_* type argument change what the database receives?

level: middleimportance: must knowfreq 56%

answer

  1. copy now vs reference read at execute()
  2. the foreach bindParam trap
  3. a literal cannot be passed by reference
  4. execute(array) binds everything as PARAM_STR
  5. PARAM_INT, PARAM_BOOL, PARAM_NULL, PARAM_LOB

basics

~20 s

bindValue() copies a value when called; bindParam() binds a variable by reference and reads it at execute(). The PARAM_* type, PARAM_STR by default, decides how the value is sent, which matters for LIMIT, booleans and binary data.

solid answer

~40 s

`bindValue(':price', $price, PDO::PARAM_STR)` takes a **copy** of the value right away. `bindParam(':price', $price)` binds the **variable by reference**; PDO reads it when `execute()` runs, so you can bind once and change the variable between executions. That reference is also the classic bug: `foreach ($filters as $k => $v) $stmt->bindParam($k, $v);` binds every marker to the same `$v`, so all of them get the last value. `bindParam()` also needs a variable, so passing a literal is an `Error`. The third argument is a `PDO::PARAM_*` type, `PARAM_STR` by default, and `execute([...])` binds every value as `PARAM_STR`. The type matters when the SQL needs a number and prepares are emulated (`LIMIT` with `PARAM_INT`), for booleans (`PARAM_BOOL`), and for binary or stream data (`PARAM_LOB`).

code

php · 16 lines
php
<?php
declare(strict_types=1);

/** @var PDO $pdo */
$stmt = $pdo->prepare('UPDATE products SET in_stock = :in_stock WHERE sku = :sku');

$sku = '';
$inStock = false;
$stmt->bindParam(':sku', $sku);
$stmt->bindParam(':in_stock', $inStock, PDO::PARAM_BOOL);

foreach ($stockFeed as $row) {   // bound once, executed per row
    $sku = $row['sku'];
    $inStock = $row['qty'] > 0;
    $stmt->execute();
}

go deeper

for a junior

Know that bindValue() copies and bindParam() references a variable, and that the default type is PDO::PARAM_STR.

for a middle

Explain the foreach trap, the Error for literals in bindParam(), and why LIMIT needs PARAM_INT under emulated prepares.

for a senior

Spot mixed bind-then-execute(array) code that silently drops typed bindings, and choose binding styles that stay correct under both emulation settings.

for a principal

Settle on one binding convention for the codebase, typically execute arrays plus typed bindValue() for integers, and enforce it in review or static analysis.

## Two ways to attach a value `PDOStatement` offers two binding methods with nearly identical signatures: - `bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool` - `bindParam(string|int $param, mixed &$var, int $type = PDO::PARAM_STR, int $maxLength = 0, mixed $driverOptions = null): bool` `$param` is the marker name (`':max'`, colon optional) or, for `?` markers, its **1-based** position. | | `bindValue()` | `bindParam()` | |---|---|---| | what is stored | a copy of the value | a reference to the variable | | when the value is read | at the call | at each `execute()` | | accepts a literal or expression | yes: `bindValue(1, $a + 1)` | no: throws `Error`, a literal cannot be passed by reference | | typical use | almost everything | loops that re-execute with a changing variable; stored-procedure output parameters | ## The foreach trap ```php foreach (['min' => 10, 'max' => 50] as $name => $value) { $stmt->bindParam($name, $value); // both markers now point at $value } $stmt->execute(); // min and max are both 50 ``` Each iteration binds the marker to the **same variable** `$value`, which ends the loop holding the last element. The fix is `bindValue()`, or `bindParam($name, $filters[$name])` so each marker references its own array element. ## What the type argument does The `PDO::PARAM_*` constant tells the driver how to send the value: - **`PDO::PARAM_STR`** (the default): sent as a string. - **`PDO::PARAM_INT`**: sent as an integer. - **`PDO::PARAM_BOOL`**: sent as a boolean; emulated prepares write `1` or `0`. - **`PDO::PARAM_NULL`**: sent as SQL `NULL`. Under emulation a PHP `null` is written as `NULL` whatever type you give. - **`PDO::PARAM_LOB`**: large or binary data, which may be a stream resource, for example a PostgreSQL `bytea` column. - `PDO::PARAM_INPUT_OUTPUT`, combined with a type using `|`, marks a stored-procedure in/out parameter for `bindParam()`. When does the type change the SQL the database receives? Mostly under **emulated prepares**, where PDO itself writes the value into the query text: 1. `PARAM_STR` values are quoted by the driver: `10` becomes `'10'`. 2. `PARAM_INT` values are converted to an integer and written without quotes. 3. `PARAM_BOOL` becomes `1` or `0`, `PARAM_NULL` becomes `NULL`. So `LIMIT ?` bound as a string becomes `LIMIT '10'` under MySQL's default emulation, and the server rejects it; bound with `PARAM_INT` it becomes `LIMIT 10`. With native prepares the type travels with the parameter, which matters less for most comparisons but still matters for binary data. ## Mixing binds and execute(array) - `execute(['max' => 50])` binds every value in the array as `PARAM_STR`. - Passing an array to `execute()` **discards** any earlier `bindValue()`/`bindParam()` bindings on that statement. Use one approach per execution: either bind every marker, typed where needed, and call `execute()` with no argument, or pass everything in the array. ## Seeing what was bound `$stmt->debugDumpParams()` prints the statement's SQL template and each bound parameter with its name or position and its `param_type` as an integer. Since PHP 7.2 it also prints the SQL actually sent, with values substituted, when prepares are emulated. It writes straight to output, so wrap it in output buffering when you need the text in a log. It shows only what is bound at that moment, which makes it a quick way to catch the foreach trap or a binding lost to `execute(array)`. ## PARAM_LOB in practice `PDO::PARAM_LOB` tells PDO to treat the data as a large object. Bound with `bindParam()` or `bindValue()`, the value may be an open stream from `fopen()`, so a product image upload can be written without reading it all into a PHP string first; bound to a result column with `bindColumn()`, the column comes back as a stream you read with `stream_get_contents()` or `fpassthru()`. ## Choosing in practice - Default to **`execute([...])`** for simple queries whose values are strings or plain comparisons. - Use **`bindValue()` with a type** when a position needs an integer (`LIMIT`, `OFFSET`), a boolean, `NULL` on purpose, or binary data. - Use **`bindParam()`** only when a variable is meant to change between executions, or for output parameters, and never inside a `foreach` over a temporary variable.

  • Why does $stmt->bindParam(':limit', 20, PDO::PARAM_INT) fail?
    The second parameter of `bindParam()` is taken by reference, so it must be a variable. A literal or an expression cannot be passed by reference, and PHP throws `Error`. Use `bindValue(':limit', 20, PDO::PARAM_INT)`, which takes the value by copy.
  • You call bindValue(':limit', 20, PDO::PARAM_INT) and then $stmt->execute(['term' => $t]). What happens to :limit?
    Passing an array to `execute()` clears the statement's earlier bindings and binds only the array's values, all as strings. `:limit` is then unbound and the call fails, typically with an Invalid parameter number (HY093) error. Bind `:term` with `bindValue()` too and call `execute()` with no argument.

saying these in an interview costs you the question

  • bindParam() and bindValue() both copy the value when called
  • Binding inside foreach with bindParam($k, $v) gives each marker its own value
  • execute(array) keeps the types set earlier with bindValue()
  • PARAM_INT validates that the value is a number and rejects anything else
  • The PARAM_* type never affects what SQL the database receives