PDO
在线手册：中文 英文
PHP手册

PDO::prepare

(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)

PDO::prepare — Prepares a statement for execution and returns a statement object

说明

PDOStatement PDO::prepare ( string $statement [, array $driver_options = array() ] )

Prepares an SQL statement to be executed by the PDOStatement::execute() method. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style. Use these parameters to bind any user-input, do not include the user-input directly in the query.

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

参数

statement

This must be a valid SQL statement for the target database server.

driver_options

This array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns. You would most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time.

返回值

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).

Note:

Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

范例

Example #1 Prepare an SQL statement with named parameters

<?php
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();
?>

Example #2 Prepare an SQL statement with question mark parameters

<?php
/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red = $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow = $sth->fetchAll();
?>

参见

PDO
在线手册：中文 英文
PHP手册
PHP手册 - N: Prepares a statement for execution and returns a statement object

用户评论:

gjarrige at six-axe dot fr (23-Mar-2012 09:57)

I found a bug with the PDO Scroll Cursor technique, for which I posted a note the 08-Mar-2012 at 12:00.
I found a solution to circumvent the problem (2 patchs in the code below) :

    public static function getScrollCursor($db, $sql, $args, $offset, $nbl_by_page ) {  
        if (!is_array($args)) {
            $args = array() ;
        }
        $offset = intval($offset) ;
        if ($offset <= 0) {
            $offset = 1 ;
        }
        /* patch 1 :
         * L'affichage doit démarrer sur l'offset -1, sinon on "rate" la première ligne
         * the decrement is necessary not to miss the first line of the result set
         */
        $offset-- ;
       
        $nbl_by_page = intval($nbl_by_page);
        if ($nbl_by_page <= 0) {
            $nbl_by_page = 10 ;
        }  
        $sql = trim ($sql );
  
        $rows = array ();  
        try {
            $st = $db->prepare ( $sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
            $st->execute ( $args );
           
            if ($offset > 0) {
                /* patch 2 :
                 * Un bug d'origine inconnu oblige à effectuer un premier positionnement
                 * sur la ligne n° 0, quand on affiche les offsets > 0
                 * Dans le cas où on affiche l'offset 0, il ne faut surtout pas faire
                 * ce premier positionnement, car il interfère avec celui qui est
                 * effecuté par la boucle d'affichage (for).
                 * !! Very important patch here, for $offset > 0 only (not for $offset = 0)
                 */
                $lost = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_REL, 0);
            }
           
            for (
                $tofetch = $nbl_by_page,
                $row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_REL, $offset);
                $row !== false && $tofetch-- > 0;
                $row = $st->fetch(PDO::FETCH_ASSOC)
            ) {
                $rows [] = $row ;
            }
           
            unset ( $st );

            return $rows;
              
        } catch ( Exception $e ) {
            // place your error code here;
        }
    }

gjarrige at six-axe dot fr (08-Mar-2012 08:00)

Example of scroll cursor for DB2 for i5 (IBM i server), that I use in a abstract class :

    public static function getScrollCursor($db, $sql, $args, $offset, $nbl_by_page ) {   
        if (!is_array($args)) {
            $args = array() ;
        }
        $offset = intval($offset) ;
        if ($offset <= 0) {
            $offset = 1 ;
        }
        $nbl_by_page = intval($nbl_by_page);
        if ($nbl_by_page <= 0) {
            $nbl_by_page = 10 ;
        }   
        $sql = trim ($sql );
   
        $rows = array ();   
        try {
            $st = $db->prepare ( $sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
            $ok = $st->execute ( $args );
            if ($ok) {
                for (
                    $tofetch = $nbl_by_page,
                    $row = $st->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_REL, $offset);
                    $row !== false && $tofetch-- > 0;
                    $row = $st->fetch(PDO::FETCH_ASSOC)
                ) {
                    $rows [] = $row;
                }
            } else {
                $rows = null;
            }
            unset ( $st );
            return $rows;
               
        } catch ( Exception $e ) {
            // place your error code here;
        }
    }   

You'll find a similar example in the PHP.net site, for the function "prepare" of the "ibm_db2" library.

The scroll cursor technique is more efficient on very big tables than the "full SQL" technique presented below :

SELECT foo.* FROM ( 
    SELECT row_number() over (ORDER BY TABLE_NAME) as rn,  
    A.*
    FROM YOURLIB/YOURTABLE A
     WHERE YOU_COL1 = ? AND YOUR_COL2 = ? ...
) AS foo 
WHERE foo.rn BETWEEN ? AND ?

public at grik dot net (07-Mar-2012 08:23)

With PDO_MYSQL you need to remember about the PDO::ATTR_EMULATE_PREPARES option.

The default value is TRUE, like
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

This means that no prepared statement is created with $dbh->prepare() call. With exec() call PDO replaces the placeholders with values itself and sends MySQL a generic query string.

The first consequence is that the call  $dbh->prepare('garbage');
reports no error. You will get an SQL error during the $dbh->exec() call.
The second one is the SQL injection risk in special cases, like using a placeholder for the table name.

The reason for emulation is a poor performance of MySQL with prepared statements. Emulation works significantly faster.

Anonymous (26-Feb-2012 03:47)

For those of you subclassing PDO while using PHP_STRICT...

While the above suggests the signature for this method is:

public function prepare ($statement, array $driver_options = array());

In fact you need to use:

public function prepare ($statement, $driver_options = array());

PDO::prepare will however barf an error if parameter 2 is not an array (including if it is null)

webmaster at salientdigital dot com (10-Jun-2011 06:09)

Using PDO to do database introspection...

<?php
    // Get a list of tables
    $tables = array();
    $sql = "SHOW TABLES";
    $stm = $this->prepare($sql);
    $result = $stm->execute();
    $columnname = 'Tables_in_'. $config['database'];
    while( $row = $stm->fetchObject() ){
        $tables[] = $row->$columnname;
    }
    print_r($tables);
?>

I just found that it is not possible to prepare "SHOW CREATE TABLE ?" nor "SHOW CREATE TABLE :tablename" however, doing it with string concatenation worked fine:

<?php
    $schemas = array();
    foreach($tables as $table)
    {
        $sql = 'SHOW CREATE TABLE '. $table;
        $stm = $this->prepare($sql);
        $result = $stm->execute();
        $columnname = 'Create Table';
        while( $row = $stm->fetchObject() ){
            $schemas[$table] = $row->$columnname;
        }
    }
    print_r($schemas);
?>

pascal dot buguet at laposte dot net (31-Aug-2010 01:11)

PDO::CURSOR_SCROLL is ok with MSS.
You must install SQL Server Driver for PHP 2.0 CTP2 : SQLSRV20.EXE
and  the native client "Microsoft SQL Server 2008 R2 Native Client" : sqlncli.msi.

php at evan dot fribourgonline dot com (17-May-2010 06:53)

Note that PDO::CURSOR_SCROLL is **not** supported or emulated by the MySQL PDO driver, nor is a warning thrown.

See: http://bugs.php.net/bug.php?id=44475

Robin (04-Mar-2010 01:01)

Use prepared statements to ensure integrity of binary data during storage and retrieval. Escaping/quoting by f.e. sqlite_escape_string() or PDO::quote() is NOT suited for binary data - only for strings of text.

A simple test verifies perfect storage and retrieval with prepared statements:

<?php

$num_values = 10000;

$db = new pdo( 'sqlite::memory:' );

$db->exec( 'CREATE TABLE data (binary BLOB(512));' );

// generate plenty of troublesome, binary data
for( $i = 0; $i < $num_values; $i++ )
{
    for( $val = null, $c = 0; $c < 512/16; $c++ )
        $val .= md5( mt_rand(), true );
    @$binary[] = $val;
}

// insert each value by prepared statement
for( $i = 0; $i < $num_values; $i++ )
    $db->prepare( 'INSERT INTO data VALUES (?);' )->execute( array($binary[$i]) );

// fetch the entire row
$data = $db->query( 'SELECT binary FROM data;' )->fetchAll( PDO::FETCH_COLUMN );

// compare with original array, noting any mismatch
for( $i = 0; $i < $num_values; $i++ )
    if( $data[$i] != $binary[$i] ) echo "[$i] mismatch\n";

$db = null;

?>

sgirard at rossprint dot com (29-Oct-2009 11:15)

Maybe everyone else already knows this but...

If you have a routine that prepares/executes many insert or update statements for a sqlite db then you may want to make use of the pdo transactions.

On some old hardware my query set went from 12 seconds to 1/3-1/2 second.

-sean

richard at codevanilla.com (11-Sep-2009 12:44)

beware
PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

This includes mySQL it seems so

<?php
try{
        $sth1 = $this->db1->prepare($t1, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
       
        }
        catch(PDOException $e){
                return $this->pack('dbError', $e->getMessage());
        }
?>

does not and so will not throw the exception if your SQL is wrong.

You will need to check that $sth1 is not null.

daniel dot egeberg at gmail dot com (24-May-2009 09:06)

You can also pass an array of values to PDOStatement::execute(). This is also secured against SQL injection. You don't necessarily have to use bindParam() or bindValue().

admin at wdfa dot co dot uk (10-Apr-2009 05:52)

Note on the SQL injection properties of prepared statements.

Prepared statements only project you from SQL injection IF you use the bindParam or bindValue option.

For example if you have a table called users with two fields, username and email and someone updates their username you might run

UPDATE `users` SET `user`='$var'

where $var would be the user submitted text.

Now if you did
<?php
$a=new PDO("mysql:host=localhost;dbname=database;","root","");
$b=$a->prepare("UPDATE `users` SET user='$var'");
$b->execute();
?>

and the user had entered  User', email='test for a test the injection would occur and the email would be updated to test as well as the user being updated to User.

Using bindParam as follows
 <?php
$var="User', email='test";
$a=new PDO("mysql:host=localhost;dbname=database;","root","");
$b=$a->prepare("UPDATE `users` SET user=:var");
$b->bindParam(":var",$var);
$b->execute();
?>

The sql would be escaped and update the username to User', email='test'

ak_9jsz (19-May-2008 06:16)

Using cursors doesn't work with SQLite 3.5.9. I get an error message when it gets to the execute() method.

Some of you might be saying "duh!" but i was surprised to see TRIGGER support in SQLite, so i had to try. :)

I wanted to use Absolute referencing on a Scrollable cursor and i only wanted one column of data. So i used this instead of a cursor.

<?php

$dbo = new PDO('sqlite:tdb');
$sql = 'SELECT F1, F2 FROM tblA WHERE F1 <> "A";';
$res = $dbo->prepare($sql);
$res->execute();
$resColumn = $res->fetchAll(PDO::FETCH_COLUMN, 0);

for($r=0;$r<=3;$r++)
    echo 'Row '. $r . ' returned: ' . $resColumn[$r] . "\n";

$dbo = null;
$res = null;
?>

Stan (14-Nov-2007 03:35)

Using prepared SELECT statements on a MySQL database prior to MySQL 5.1.17 can lead to SERIOUS performance degradation.

Quote from http://dev.mysql.com/doc/refman/5.1/en/query-cache.html :

>> The query cache is not used for server-side prepared statements before MySQL 5.1.17 <<

The MySQL query cache buffers complete query results and is used to satisfy repeated identical queries if the underlying tables do not change in the meantime - just what happens all the time in a typical web application. It speeds up queries by a several hundred to a several thousand percent.

Obviously, it doesn't make much sense to give up query caching for the relatively small performance benefit of prepared statements (i.e. the DBMS not having to parse and optimize the same query multiple times) - so using PDO->query() for SELECT statements is probably the better choice i you're connecting to MySQL < 5.1.17.

www.onphp5.com (07-Apr-2007 02:41)

Please note that the statement regarding driver_options is misleading:

"This array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns. You would most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time"

From this you might think that scrollable cursors work for all databases, but they don't! Check out this bug report:
http://bugs.php.net/bug.php?id=34625

johniskew (22-Feb-2007 04:03)

If you need to create variable sql statements in a prepare statement...for example you may need to construct a sql query with zero, one, two, etc numbers of arguments...here is a way to do it without a lot of if/else statements needed to glue the sql together:

<?php

    public function matchCriteria($field1=null,$field2=null,$field3=null) {
        $db=DB::conn();
        $sql=array();
        $paramArray=array();
        if(!empty($field1)) {
            $sql[]='field1=?';
            $paramArray[]=$field1;
        }
        if(!empty($field2)) {
            $sql[]='field2=?';
            $paramArray[]=$field2;
        }
        if(!empty($field3)) {
            $sql[]='field3=?';
            $paramArray[]=$field3;
        }
        $rs=$db->prepare('SELECT * FROM mytable'.(count($paramArray)>0 ? ' WHERE '.join(' AND ',$sql) : ''));
        $result=$rs->execute($paramArray);
        if($result) {
            return $rs;
        }
        return false;
    }

?>

jacques at chester dot id dot au (13-Nov-2006 02:07)

Watch out: prepared statements on MySQL barf if you try to pass in substitution tables for sql keywords, table names, view names and field names.

For example, this will not work:

$stmt = $dbh->prepare("SELECT :sqlAggregate( :fieldName) from :viewName";

You will get a MySQL error 1064 and a very unhelpful error message.

In short, you need to do string substitutions into your queries if you want configurable table names, aggregate keywords etc etc.

Which doesn't help at all in the "protection from injection attacks" thing. A very annoying discovery.

william dot clarke at gmail dot com (31-Aug-2006 11:58)

Surely if you want to use prepared statements that way you should use the syntax in the second example:

eg.

instead of:
select id,name from demo_de where name LIKE :name OR name=:name

use:
select id,name from demo_de where name LIKE ? OR name=?

I believe you are supposed to either use distinct named parameters (name, name1) OR anonymous parameters (?s)

roth at egotec dot com (30-Aug-2006 09:58)

Attention using MySQL and prepared statements.
Using a placeholder multiple times inside a statement doesn't work. PDO just translates the first occurance und leaves the second one as is.

select id,name from demo_de where name LIKE :name OR name=:name

You have to use

select id,name from demo_de where name LIKE :name OR name=:name2

and bind name two times. I don't know if other databases (for example Oracle or MSSQL) support multiple occurances. If that's the fact, then the PDO behaviour for MySQL should be changed.