SunshinePHP Developer Conference 2015

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 = ?");
if (
$stmt->execute(array($_GET['name']))) {
  while (
$row $stmt->fetch()) {
    
print_r($row);
  }
}
?>

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.

Example #4 Calling a stored procedure with an output parameter

<?php
$stmt 
$dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1$return_valuePDO::PARAM_STR4000); 

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

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

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.

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

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

// 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(array($_GET['name']));

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

add a note add a note

User Contributed Notes 6 notes

up
36
adam at pyramidpower dot com dot au
4 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
-9
oldmoscow dot mail dot ru
3 years ago
Note for MySQL: to use input/output parameters for stored procedures with PDO use PDO.Query() statement.
For example:

<?php
$dbh
->query("CAST SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)");
$dbh->query("SELECT @someOutParameter");
?>

Or, if you want very much to use PDO.Prepare(), insert "SELECT @someOutParameter" in your stored procedure and then use:

<?php
$stmt
= $dbh->prepare("CAST SomeStoredProcedure(?, ?)");
$stmt ->execute(array($someInParameter1, $someInParameter2));
?>
up
-12
Sebastien Gourmand
1 year ago
Replace the query() by a prepare()/execute() for having one line like a sprintf()

old query() renamed for remember to secure.

<?php
class MyPDO extends PDO{

    const
DB_HOST='localhost';
    const
DB_PORT='3306';
    const
DB_NAME='test';
    const
DB_USER='root';
    const
DB_PASS='';

    public function
__construct($options=null){
       
parent::__construct('mysql:host='.MyPDO::DB_HOST.';port='.MyPDO::DB_PORT.';dbname='.MyPDO::DB_NAME,
                           
MyPDO::DB_USER,
                           
MyPDO::DB_PASS,$options);
    }

    public function
query($query){ //secured query with prepare and execute
       
$args = func_get_args();
       
array_shift($args); //first element is not an argument but the query itself, should removed

       
$reponse = parent::prepare($query);
       
$reponse->execute($args);
        return
$reponse;

    }

    public function
insecureQuery($query){ //you can use the old query at your risk ;) and should use secure quote() function with it
       
return parent::query($query);
    }

}

$db = new MyPDO();
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

$t1 = isset($_GET["t1"])?$_GET["t1"]:1; // need to be securised for injonction
$t2 = isset($_GET["t2"])?$_GET["t2"]:2; // need to be securised for injonction
$t3 = isset($_GET["t3"])?$_GET["t3"]:3; // need to be securised for injonction

$ret = $db->query("SELECT * FROM table_test WHERE t1=? AND t2=? AND t3=?",$t1,$t2,$t3);
//$ret = $db->insecureQuery("SELECT * FROM table_test WHERE t1=".$db->quote($t1));

while ($o = $ret->fetch())
{
    echo
$o->nom.PHP_EOL;
}
?>
up
-20
Anonymous
7 months ago
/** Добавить модификации документа */
    public function insert_doc_modification($doc_id , array $notice ,array $modification_fields)
    {
     
        $fields = array_keys($modification_fields);
        $sql = "INSERT INTO doc_modification
                    (doc_id , " . implode( ",", $fields ) . ")
                VALUES
                    (:doc_id , :" . implode( ", :", $fields ) . " )
                ON DUPLICATE KEY UPDATE
                    `hash` = :hash ; ";
       
        $stmt = $this->pdo->prepare($sql);  
        $stmt->bindParam(':doc_id',$doc_id);
        //Создать бинды по полям
        foreach($modification_fields as $name => $val)
        {
            $stmt->bindParam(':'.$name,  $modification_fields[$name]);
        }
        //Обойти все модификации по документу
        foreach($notice as $value)
        {
            foreach($modification_fields as $name => $val)
            {
                if(empty($value[$name])) continue ;
                $modification_fields[$name] = $value[$name];
            }
            $stmt->execute();
        }

    }
up
-31
Anonymous
3 years ago
Note for MySQL: to use input/output parameters for stored procedures with PDO use PDO.Query() statement.
For example:

<?php
$dbh
->query("CAST SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)");
$dbh->query("SELECT @someOutParameter");
?>

Or, if you want very much to use PDO.Prepare(), insert "SELECT @someOutParameter" in your stored procedure and then use:

<?php
$stmt
= $dbh->prepare("CAST SomeStoredProcedure(?, ?)");
$stmt ->execute(array($someInParameter1, $someInParameter2));
?>
up
-10
user at example dot 1com
1 month ago
lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@  lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@

lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@ lol :@
To Top