❮ Func Array Keys Func Date Timezone Offset Get ❯

PHP PDO Prepared Statements and Stored Procedures

PHP PDO Reference Manual

Many mature databases support the concept of prepared statements.

What is a prepared statement? You can think of it as a compiled template for the SQL you want to run, which can be customized using variable parameters. Prepared statements offer two main benefits:

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. Once the query is prepared, the database will analyze, compile, and optimize its plan to execute the query. For complex queries, this process can take a significant amount of time. If you need to repeat the same query with different parameters many times, this process can drastically slow down your application. By using prepared statements, you avoid repeating the analysis/compilation/optimization cycle. In short, prepared statements use fewer resources, so they run faster.
Parameters for prepared statements do not need to be enclosed in quotes; the driver handles this automatically. If your application exclusively uses prepared statements, you can ensure that no SQL injection will occur. (However, if other parts of the query are constructed from unescaped input, SQL injection remains a risk).

Prepared statements are so useful that they are the only feature that PDO will emulate if the driver does not support them. This ensures that your application can use the same data access pattern regardless of whether the database has this capability.

Repeated Insertion with Prepared Statements

The following example executes an insert query by replacing the named placeholders with name and value.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// Insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// Insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

Repeated Insertion with Prepared Statements

The following example executes an insert query by replacing the positional placeholders with name and value.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// Insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// Insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

Fetching Data with Prepared Statements

The following example fetches data based on the provided key value. User input is automatically quoted, so there is no risk of SQL injection.

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

If the database driver supports it, applications can also bind output and input parameters. Output parameters are typically used to retrieve values from stored procedures. Output parameters are slightly more complex to use than input parameters, as you must know the length of the given parameter. If the value bound to the parameter is larger than the suggested length, an error will occur.

Calling a Stored Procedure with an Output Parameter

<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

// Call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";
?>

You can also specify parameters that have both input and output values, similar to output parameters. In the next example, the string "hello" is passed to the stored procedure, and upon its return, "hello" is replaced with the value returned by the stored procedure.

Calling a Stored Procedure with an Input/Output Parameter

<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// Call the stored procedure
$stmt->execute();
?>

print "procedure returned $value\n";
?>

Invalid Use of Placeholders

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name']));

// Placeholders must be used in the entire value position
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
?>

PHP PDO Reference Manual

❮ Func Array Keys Func Date Timezone Offset Get ❯