PHP 8.3.4 Released!

Prepared statements and stored procedures

Many of the more mature databases support the concept of prepared statements. What are they? They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

  • The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.
  • The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.

Example #1 Repeated inserts using prepared statements

This example performs an INSERT query by substituting a name and a value for the named placeholders.

<?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();
?>

Example #2 Repeated inserts using prepared statements

This example performs an INSERT query by substituting a name and a value for the positional ? placeholders.

<?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();
?>

Example #3 Fetching data using prepared statements

This example fetches data based on a key value supplied by a form. The user input is automatically quoted, so there is no risk of a SQL injection attack.

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

Example #4 Calling a stored procedure with an output parameter

If the database driver supports it, an application may also bind parameters for output as well as input. Output parameters are typically used to retrieve values from stored procedures. Output parameters are slightly more complex to use than input parameters, in that a developer must know how large a given parameter might be when they bind it. If the value turns out to be larger than the size they suggested, an error is raised.

<?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";
?>

Example #5 Calling a stored procedure with an input/output parameter

Developers may also specify parameters that hold values both input and output; the syntax is similar to output parameters. In this next example, the string 'hello' is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.

<?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";
?>

Example #6 Invalid use of placeholder

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

// placeholder must be used in the place of the whole value
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(["%$_GET[name]%"]);
?>

add a note

User Contributed Notes 4 notes

up
219
adam at pyramidpower dot com dot au
13 years ago
Note that when using name parameters with bindParam, the name itself, cannot contain a dash '-'.

example:
<?php
$stmt
= $dbh->prepare ("INSERT INTO user (firstname, surname) VALUES (:f-name, :s-name)");
$stmt -> bindParam(':f-name', 'John');
$stmt -> bindParam(':s-name', 'Smith');
$stmt -> execute();
?>

The dashes in 'f-name' and 's-name' should be replaced with an underscore or no dash at all.

See http://bugs.php.net/43130

Adam
up
13
w37090 at yandex dot ru
4 years ago
Insert a multidimensional array into the database through a prepared query:
We have an array to write the form:

$dataArr:
Array
(
[0] => Array
(
[0] => 2020
[1] => 23
[2] => 111111
)

[1] => Array
(
[0] => 2020
[1] => 24
[2] => 222222222
)
....

Task: prepare a request and pass through binds
$array = [];
foreach ($dataArr as $k=>$v) {
// $x = 2020, the variable is predetermined in advance, does not change the essence
$array[] = [$x, $k, $v];
}
$sql = ("INSERT INTO `table` (`field`,`field`,`field`) VALUES (?,?,?)");

$db->queryBindInsert($sql,$array);

public function queryBindInsert($sql,$bind) {
$stmt = $this->pdo->prepare($sql);

if(count($bind)) {
foreach($bind as $param => $value) {
$c = 1;
for ($i=0; $i<count($value); $i++) {
$stmt->bindValue($c++, $value[$i]);
}
$stmt->execute();
}
}
}
up
1
theking2(at)king.ma
20 days ago
Example #5 gives an 1414 wenn tried on MariaDB. Use this function to call a stored procedure with the last parameter as INOUT returning a value like a (uu)id or a count;

<?php
/**
* call_sp Call the specified stored procedure with the given parameters.
* The first parameter is the name of the stored procedure.
* The remaining parameters are the (in) parameters to the stored procedure.
* the last (out) parameter should be an int like state or number of affected rows.
*
* @param mixed $sp_name The name of the stored procedure to call.
* @param mixed $params The parameters to pass to the stored procedure.
* @return int The number of affected rows.
*/
function call_sp( \PDO $db, string $sp_name, ...$params ): mixed
{
$placeholders = array_fill( 0, count( $params ), "?" );
$placeholders[] = "@new_id";

$sql = "CALL $sp_name( " . implode( ", ", $placeholders ) . " ); SELECT @new_id AS `new_id`";

try {
LOG->debug( "calling Stored Procedure", [ "sql" => $sql ] );

$stmt = $db->prepare( $sql );
$i = 0;
foreach(
$params as $param ) {
$stmt->bindValue( ++$i, $param );
}
$stmt->execute();
$new_id = $stmt->fetch( PDO::FETCH_ASSOC )['new_id'];

return
$new_id;

} catch (
\Exception $e ) {
LOG->error( "Error calling Stored Procedure", [ "sql" => $sql, "params" => $params, "error" => $e->getMessage() ] );
throw
$e;
}
up
-58
bkilinc at deyta dot net
3 years ago
it is a good practice not using double quotes in sql strings. This way you can ensure that no variable is injected in query.
a simple query with parameters should be;
'INSERT INTO REGISTRY (name, value) VALUES (?, ?)'
not
"INSERT INTO REGISTRY (name, value) VALUES (?, ?)"
To Top