<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Prepared statements and stored procedures</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="pdo.transactions.html">Transactions and auto-commit</a></div>
<div class="next" style="text-align: right; float: right;"><a href="pdo.error-handling.html">Errors and error handling</a></div>
<div class="up"><a href="book.pdo.html">PDO</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="pdo.prepared-statements" class="chapter">
<h1>Prepared statements and stored procedures</h1>
<p class="para">
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:
</p>
<ul class="itemizedlist">
<li class="listitem">
<span class="simpara">
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.
</span>
</li>
<li class="listitem">
<span class="simpara">
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).
</span>
</li>
</ul>
<p class="para">
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.
</p>
<p class="para">
<div class="example" id="example-904">
<p><strong>Example #1 Repeated inserts using prepared statements</strong></p>
<div class="example-contents"><p>
This example performs an INSERT query by substituting a <em>name</em>
and a <em>value</em> for the named placeholders.
</p></div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #DD0000">':name'</span><span style="color: #007700">, </span><span style="color: #0000BB">$name</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #DD0000">':value'</span><span style="color: #007700">, </span><span style="color: #0000BB">$value</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// insert one row<br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #DD0000">'one'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$value </span><span style="color: #007700">= </span><span style="color: #0000BB">1</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /><br /></span><span style="color: #FF8000">// insert another row with different values<br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #DD0000">'two'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$value </span><span style="color: #007700">= </span><span style="color: #0000BB">2</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-905">
<p><strong>Example #2 Repeated inserts using prepared statements</strong></p>
<div class="example-contents"><p>
This example performs an INSERT query by substituting a <em>name</em>
and a <em>value</em> for the positional <em>?</em> placeholders.
</p></div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO REGISTRY (name, value) VALUES (?, ?)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">$name</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">2</span><span style="color: #007700">, </span><span style="color: #0000BB">$value</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">// insert one row<br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #DD0000">'one'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$value </span><span style="color: #007700">= </span><span style="color: #0000BB">1</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /><br /></span><span style="color: #FF8000">// insert another row with different values<br /></span><span style="color: #0000BB">$name </span><span style="color: #007700">= </span><span style="color: #DD0000">'two'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$value </span><span style="color: #007700">= </span><span style="color: #0000BB">2</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-906">
<p><strong>Example #3 Fetching data using prepared statements</strong></p>
<div class="example-contents"><p>
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.
</p></div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT * FROM REGISTRY where name = ?"</span><span style="color: #007700">);<br />if (</span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">(array(</span><span style="color: #0000BB">$_GET</span><span style="color: #007700">[</span><span style="color: #DD0000">'name'</span><span style="color: #007700">]))) {<br /> while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch</span><span style="color: #007700">()) {<br /> </span><span style="color: #0000BB">print_r</span><span style="color: #007700">(</span><span style="color: #0000BB">$row</span><span style="color: #007700">);<br /> }<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
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.
</p>
<p class="para">
<div class="example" id="example-907">
<p><strong>Example #4 Calling a stored procedure with an output parameter</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"CALL sp_returns_string(?)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">$return_value</span><span style="color: #007700">, </span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_STR</span><span style="color: #007700">, </span><span style="color: #0000BB">4000</span><span style="color: #007700">); <br /><br /></span><span style="color: #FF8000">// call the stored procedure<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /><br />print </span><span style="color: #DD0000">"procedure returned </span><span style="color: #0000BB">$return_value</span><span style="color: #DD0000">\n"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
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.
</p>
<p class="para">
<div class="example" id="example-908">
<p><strong>Example #5 Calling a stored procedure with an input/output parameter</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"CALL sp_takes_string_returns_string(?)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$value </span><span style="color: #007700">= </span><span style="color: #DD0000">'hello'</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">$value</span><span style="color: #007700">, </span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_STR</span><span style="color: #007700">|</span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_INPUT_OUTPUT</span><span style="color: #007700">, </span><span style="color: #0000BB">4000</span><span style="color: #007700">); <br /><br /></span><span style="color: #FF8000">// call the stored procedure<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /><br />print </span><span style="color: #DD0000">"procedure returned </span><span style="color: #0000BB">$value</span><span style="color: #DD0000">\n"</span><span style="color: #007700">;<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-909">
<p><strong>Example #6 Invalid use of placeholder</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT * FROM REGISTRY where name LIKE '%?%'"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">(array(</span><span style="color: #0000BB">$_GET</span><span style="color: #007700">[</span><span style="color: #DD0000">'name'</span><span style="color: #007700">]));<br /><br /></span><span style="color: #FF8000">// placeholder must be used in the place of the whole value<br /></span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$dbh</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT * FROM REGISTRY where name LIKE ?"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">(array(</span><span style="color: #DD0000">"%</span><span style="color: #0000BB">$_GET</span><span style="color: #007700">[</span><span style="color: #0000BB">name</span><span style="color: #007700">]</span><span style="color: #DD0000">%"</span><span style="color: #007700">));<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
</div>
<hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="pdo.transactions.html">Transactions and auto-commit</a></div>
<div class="next" style="text-align: right; float: right;"><a href="pdo.error-handling.html">Errors and error handling</a></div>
<div class="up"><a href="book.pdo.html">PDO</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
