Sentencias preparadas y procedimientos almacenados

Muchas de las bases de datos más maduras admiten el concepto de sentencias preparadas. Estas pueden definirse como un tipo de plantillas compiladas para SQL que las aplicaciones quieren ejecutar, pudiendo ser personalizadas utilizando parámetros variables. Las sentencias preparadas ofrecen dos grandes beneficios:

  • La consulta sólo necesita ser analizada (o preparada) una vez, pero puede ser ejecutada muchas veces con los mismos o diferentes parámetros. Cuando la consulta se prepara, la base de datos analizará, compilará y optimizará su plan para ejecutarla. Para consultas complejas, este proceso puede tomar suficiente tiempo como para ralentizar notablemente una aplicación si fuera necesario repetir la misma consulta muchas veces con los mismos parámetros. Mediante el empleo de una sentencia preparada, la aplicación evita repetir el ciclo de análisis/compilación/optimización. Esto significa que las sentencias preparadas utilizan menos recursos y se ejecutan más rápidamente.
  • Los parámetros para las sentencias preparadas no necesitan estar entrecomillados; el controlador automáticamente se encarga de esto. Si una aplicación usa exclusivamente sentencias preparadas, el desarrollador puede estar seguro de que no hay cabida para inyecciones de SQL (sin embargo, si otras partes de la consulta se construyen con datos de entrada sin escapar, aún es posible que ocurran ataques de inyecciones de SQL).

Las sentencias preparadas son tan útiles que son la única característica que PDO emulará para los controladores que no las soporten. Esto asegura que una aplicación sea capaz de emplear el mismo paradigma de acceso a datos independientemente de las capacidades de la base de datos.

Ejemplo #1 Inserciones repetidas utilizando sentencias preparadas

Este ejemplo realiza dos consultas INSERT sustituyendo name y value por los marcadores correspondientes.

<?php
$sentencia 
$mbd->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$sentencia->bindParam(':name'$nombre);
$sentencia->bindParam(':value'$valor);

// insertar una fila
$nombre 'uno';
$valor 1;
$sentencia->execute();

// insertar otra fila con diferentes valores
$nombre 'dos';
$valor 2;
$sentencia->execute();
?>

Ejemplo #2 Inserciones repetidas utilizando sentencias preparadas

Este ejemplo realiza dos consultas INSERT sustituyendo name y value por los marcadores posicionales '?'.

<?php
$sentencia 
$mbd->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$sentencia->bindParam(1$nombre);
$sentencia->bindParam(2$valor);

// insertar una fila
$nombre 'uno';
$valor 1;
$sentencia->execute();

// insertar otra fila con diferentes valores
$nombre 'dos';
$valor 2;
$sentencia->execute();
?>

Ejemplo #3 Obtener datos empleando sentencias preparadas

Este ejemplo obtiene datos basándose en un valor de clave proporcionado por un formulario. La entrada del usuario es entrecomillada automáticamente, con lo cual no hay riesgo de un ataque por inyección de SQL.

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

Si el controlador de la base de datos lo admite, una aplicación podría también vincular parámetros para salida y para entrada. Los parámetros de salida se emplean típicamente para recuperar valores de procedimientos almacenados. Los parámetros de salida son ligeramente más complejos de usar que los de entrada, de manera que el desarrollador debe conocer la magnitud de un parámetro dado cuando se vincula. Si el valor resulta ser más grande que el tamaño indicado, se emitirá un error.

Ejemplo #4 Llamar a un procedimiento almacenado con un parámetro de salida

<?php
$sentencia 
$mbd->prepare("CALL sp_returns_string(?)");
$sentencia->bindParam(1$valor_devuletoPDO::PARAM_STR4000); 

// llamar al procedimiento almacenado
$sentencia->execute();

print 
"El procedimiento devolvió $valor_devuleto\n";
?>

Un desarrollador podría también especificar parámetros que contienen valores tanto de entrada como de salida; la sintaxis es similar a la de los parámetros de salida. En el siguiente ejemplo, la cadena 'hola' es pasada al procedimiento almacenado, y cuando éste finaliza, 'hola' es reemplazada con el valor de retorno del procedimiento.

Ejemplo #5 Llamar a un procedimiento almacenado con un parámetro de entrada/salida

<?php
$sentencia 
$mbd->prepare("CALL sp_takes_string_returns_string(?)");
$valor 'hola';
$sentencia->bindParam(1$valorPDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT4000); 

// llamar al procedimiento almacenado
$sentencia->execute();

print 
"El procedimiento devolvió $valor\n";
?>

Ejemplo #6 Uso inválido de un marcador de posición

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

// los marcadores de posición deben emplearse en el lugar del valor completo
$sentencia $mbd->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$sentencia->execute(array("%$_GET[name]%"));
?>

add a note add a note

User Contributed Notes 7 notes

up
85
adam at pyramidpower dot com dot au
5 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
-40
oldmoscow dot mail dot ru
4 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
-43
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
-5
mdhaval_patel at yahoo dot com
1 month ago
$query="{CALL test1(@ID=:ID,@name=:name,@statement=:statement)}";
$stmt = $dbh->prepare($query);
$id="4";
$name="pp";
$statement="Select";
$stmt->bindParam(':ID', $id);
$stmt->bindParam(':statement', $statement);
$stmt->bindParam(':name', $name);
$stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
      var_dump($row);
    }

when i not pass name para not getting any output, becuse name is not needed here
up
-60
Anonymous
1 year 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
-82
Anonymous
4 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
-417
user at example dot 1com
7 months 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