oci_bind_by_name

(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

oci_bind_by_name — Associe une variable PHP à un marqueur Oracle

Description

oci_bind_by_name(
    resource $statement,
    string $param,
    mixed &$var,
    int $max_length = -1,
    int $type = 0
): bool

Lie une variable PHP var au marqueur Oracle param. Le fait de lier une variable est important en termes de performance de la base de données Oracle, mais aussi en termes de sécurité relative aux injections SQL.

Le liage permet aux bases de données de réutiliser le contexte d'exécution de la requête ainsi que le cache associé, y compris lorsqu'un autre utilisateur ou processus l'exécute. Le liage réduit le risque d'injection SQL car les données associées à une variable liée ne sont jamais traitées comme faisant partie de la requête SQL. Inutile donc d'ajouter des quotes ou d'échapper ces données.

Les variables PHP liées peuvent changer et la requête ré-exécutée sans avoir besoin d'analyser de nouveau la requête ou de lier de nouveau les variables.

Avec Oracle, les variables liées sont habituellement divisées en lien IN pour les valeurs passées à la base de données, et en lien OUT pour les valeurs à retourner à PHP. Une variable liée peut être à la fois en lien IN et OUT. Dans ce cas, le fait de savoir si la variable liée doit être utilisée pour l'entrée ou la sortie sera déterminé au moment de l'exécution.

Il faut spécifier le paramètre max_length lors de l'utilisation du lien OUT afin que PHP alloue assez de mémoire pour contenir la valeur de retour.

Pour les liens IN, il est recommandé de définir le paramètre max_length si la requête est exécutée plusieurs fois avec des valeurs différentes pour les variables PHP. Sinon, Oracle peut tronquer les données à la longueur de la valeur initiale de la variable PHP. Si l'on ne connaît pas la valeur maximale dont on a besoin, alors appelez de nouveau la fonction oci_bind_by_name() avec les données courantes, avant chaque appel à la fonction oci_execute(). Le fait d'associer une taille supérieure à ce dont on a besoin a un impact sur la mémoire associée au processus pour la base de données.

Un appel à la fonctionnalité d'association de variables donne à Oracle l'adresse mémoire à utiliser pour lire les données. Pour les liens IN, cette adresse doit contenir des données valides lors de l'appel à la fonction oci_execute(). Ceci signifie que la variable liée doit rester dans le contexte jusqu'à l'exécution. Si ce n'est pas le cas, des résultats non attendus peuvent survenir, ainsi que des erreurs de type "ORA-01460: unimplemented or unreasonable conversion requested". Pour les liens OUT, un symptôme peut être qu'aucune valeur n'a été définie dans la variable PHP.

Pour une requête qui est exécutée à plusieurs reprises, les valeurs liées qui ne changent jamais peuvent réduire la capacité de l'optimisateur d'Oracle à choisir le meilleur plan d'exécution. Pour les requêtes prenant du temps, qui sont rarement appelées plusieurs fois, l'association de variables n'apporte aucun bénéfice. Cependant, dans les 2 cas, l'association est plus sécurisée que de placer directement les chaînes de caractères dans la requête SQL, sachant qu'il y a un risque de mauvais filtrage de l'entrée utilisateur.

Liste de paramètres

statement

Un identifiant de requête OCI8 valide.

param

Le marqueur, préfixé par deux-points, utilisé dans la requête. Les deux-points sont optionnels dans le paramètre param.

var

La variable PHP à associer avec le marqueur du paramètre param.

max_length

Spécifie la taille maximale pour les données. Si la valeur est -1, la fonction utilisera la longueur courante des données du paramètre var pour définir la longueur maximale. Dans ce cas, le paramètre var doit exister et contenir des données lors de l'appel à la fonction oci_bind_by_name().

type

Le type de données à utiliser par Oracle pour traiter les données. Par défaut, vaut SQLT_CHR. Oracle convertira les données entre ce type et la colonne de la base de données (ou des variables de type PL/SQL), lorsque c'est possible.

S'il faut lier des types abstraits de données (LOB/ROWID/BFILE), il faudra l'allouer dans un premier temps, avec oci_new_descriptor(). La longueur length ne sert pas pour ces types et devrait être fixée à -1.

Les valeurs possibles pour le paramètre type sont :

SQLT_BFILEE ou OCI_B_BFILE - pour les BFILEs ;
SQLT_CFILEE ou OCI_B_CFILEE - pour les CFILEs ;
SQLT_CLOB ou OCI_B_CLOB - pour les CLOBs ;
SQLT_BLOB ou OCI_B_BLOB - pour les BLOBs ;
SQLT_RDD ou OCI_B_ROWID - pour les ROWIDs ;
SQLT_NTY ou OCI_B_NTY - pour les types de données nommés ;
SQLT_INT ou OCI_B_INT - pour les entiers ;
SQLT_CHR - pour les VARCHAR ;
SQLT_BIN ou OCI_B_BIN - pour les colonnes RAW ;
SQLT_LNG - pour les colonnes LONG ;
SQLT_LBI - pour les colonnes LONG RAW ;
SQLT_RSET - pour les curseurs créés avec la fonction oci_new_cursor();
SQLT_BOL ou OCI_B_BOL - pour les booléens (requiert Oracle Database 12c)

Valeurs de retour

Cette fonction retourne true en cas de succès ou false si une erreur survient.

Exemples

Exemple #1 Insertion de données avec la fonction oci_bind_by_name()

<?php

// Création de la table avec :
//   CREATE TABLE mytab (id NUMBER, text VARCHAR2(40));

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$stid = oci_parse($conn,"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)");

$id = 1;
$text = "Données à insérer     ";
oci_bind_by_name($stid, ":id_bv", $id);
oci_bind_by_name($stid, ":text_bv", $text);
oci_execute($stid);

// La table contient maintenant : 1, 'Données à insérer  

?>

Exemple #2 Une association avec de multiples appels

<?php

// Création de la table avec :
//   CREATE TABLE mytab (id NUMBER);

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$a = array(1,3,5,7,11);  // Données à insérer

$stid = oci_parse($conn, 'INSERT INTO mytab (id) VALUES (:bv)');
oci_bind_by_name($stid, ':bv', $v, 20);
foreach ($a as $v) {
    $r = oci_execute($stid, OCI_DEFAULT);  // Ne pas valider automatiquement
}
oci_commit($conn); // Une seule validation

// La table contient maintenant 5 lignes : 1, 3, 5, 7, 11

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #3 Association avec une boucle foreach

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = 'SELECT * FROM departments WHERE department_name = :dname AND location_id = :loc';
$stid = oci_parse($conn, $sql);

$ba = array(':dname' => 'IT Support', ':loc' => 1700);

foreach ($ba as $key => $val) {

    // oci_bind_by_name($stid, $key, $val) ne fonctionne pas car
    // elle associe chaque marqueur au même endroit : $val
    // au lieu d'utiliser l'endroit actuel de la donnée $ba[$key]
    oci_bind_by_name($stid, $key, $ba[$key]);
}

oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
foreach ($row as $item) {
    print $item."<br>\n";
}

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #4 Association dans une clause WHERE

<?php

$conn = oci_connect("hr", "hrpwd", "localhost/XE");
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = 'SELECT last_name FROM employees WHERE department_id = :didbv ORDER BY last_name';
$stid = oci_parse($conn, $sql);
$didbv = 60;
oci_bind_by_name($stid, ':didbv', $didbv);
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
    echo $row['LAST_NAME'] ."<br>\n";
}

// Affiche :
//    Austin
//    Ernst
//    Hunold
//    Lorentz
//    Pataballa

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #5 Association avec une clause LIKE

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

// Trouve tous les critères qui commencent par 'South'
$stid = oci_parse($conn, "SELECT city FROM locations WHERE city LIKE :bv");
$city = 'South%';  // '%' is a wildcard in SQL
oci_bind_by_name($stid, ":bv", $city);
oci_execute($stid);
oci_fetch_all($stid, $res);

foreach ($res['CITY'] as $c) {
    print $c . "<br>\n";
}
// Affiche :
//   South Brunswick
//   South San Francisco
//   Southlake

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #6 Association avec REGEXP_LIKE

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

// Trouve tous les critères qui contiennent 'ing'
$stid = oci_parse($conn, "SELECT city FROM locations WHERE REGEXP_LIKE(city, :bv)");
$city = '.*ing.*';
oci_bind_by_name($stid, ":bv", $city);
oci_execute($stid);
oci_fetch_all($stid, $res);

foreach ($res['CITY'] as $c) {
    print $c . "<br>\n";
}
// Affiche :
//   Beijing
//   Singapore

oci_free_statement($stid);
oci_close($conn);

?>

Pour quelques conditions utilisées dans les clauses IN, utiliser des variables liées individuelles. Les valeurs inconnues au moment de l'exécution pourront être définies à NULL. Ceci permet d'utiliser une seule requête pour l'ensemble des utilisateurs de l'application, maximisant ainsi l'efficacité du cache Oracle DB.

Exemple #7 Association de plusieurs valeurs dans une clause IN

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = 'SELECT last_name FROM employees WHERE employee_id in (:e1, :e2, :e3)';
$stid = oci_parse($conn, $sql);
$mye1 = 103;
$mye2 = 104;
$mye3 = NULL; // pretend we were not given this value
oci_bind_by_name($stid, ':e1', $mye1);
oci_bind_by_name($stid, ':e2', $mye2);
oci_bind_by_name($stid, ':e3', $mye3);
oci_execute($stid);
oci_fetch_all($stid, $res);
foreach ($res['LAST_NAME'] as $name) {
    print $name ."<br>\n";
}

// Affiche :
//   Ernst
//   Hunold

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #8 Association d'un ROWID retourné par une requête

<?php

// Création de la table avec :
//   CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));
//   INSERT INTO mytab (id, salary, name) VALUES (1, 100, 'Chris');
//   COMMIT;

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$stid = oci_parse($conn, 'SELECT ROWID, name FROM mytab WHERE id = :id_bv FOR UPDATE');
$id = 1;
oci_bind_by_name($stid, ':id_bv', $id);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
$rid = $row['ROWID'];
$name = $row['NAME'];

// Modification du nom en lettre majuscule & sauvegarde de cette modification
$name = strtoupper($name);
$stid = oci_parse($conn, 'UPDATE mytab SET name = :n_bv WHERE ROWID = :r_bv');
oci_bind_by_name($stid, ':n_bv', $name);
oci_bind_by_name($stid, ':r_bv', $rid, -1, OCI_B_ROWID);
oci_execute($stid);

// La table contient maintenant 1, 100, CHRIS

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #9 Association d'un ROWID dans un INSERT

<?php

// Cet exemple insère un id & un nom, puis, met à jour le salaire
// Création de la table avec :
//   CREATE TABLE mytab (id NUMBER, salary NUMBER, name VARCHAR2(40));
//
// Basé sur l'exemple original de ROWID, fourni par thies at thieso dot net (980221)

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
}

$sql = "INSERT INTO mytab (id, name) VALUES(:id_bv, :name_bv)
        RETURNING ROWID INTO :rid";

$ins_stid = oci_parse($conn, $sql);

$rowid = oci_new_descriptor($conn, OCI_D_ROWID);
oci_bind_by_name($ins_stid, ":id_bv",   $id,    10);
oci_bind_by_name($ins_stid, ":name_bv", $name,  32);
oci_bind_by_name($ins_stid, ":rid",     $rowid, -1, OCI_B_ROWID);

$sql = "UPDATE mytab SET salary = :salary WHERE ROWID = :rid";
$upd_stid = oci_parse($conn, $sql);
oci_bind_by_name($upd_stid, ":rid", $rowid, -1, OCI_B_ROWID);
oci_bind_by_name($upd_stid, ":salary", $salary,   32);

// ids et noms à insérer
$data = array(1111 => "Larry",
              2222 => "Bill",
              3333 => "Jim");

// Salaire de chaque personne
$salary = 10000;

// Insertion et mise à jour immédiate de chaque ligne
foreach ($data as $id => $name) {
    oci_execute($ins_stid);
    oci_execute($upd_stid);
}

$rowid->free();
oci_free_statement($upd_stid);
oci_free_statement($ins_stid);

// Affichage des nouvelles lignes
$stid = oci_parse($conn, "SELECT * FROM mytab");
oci_execute($stid);
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    var_dump($row);
}

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #10 Association pour une fonction stockée PL/SQL

<?php

// Avant d'exécuter le programme PHP, une fonction stockée doit être créée
// au format SQL*Plus ou SQL Developer :
//
//  CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS
//  BEGIN
//      RETURN p * 3;
//  END;

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

$p = 8;

$stid = oci_parse($conn, 'begin :r := myfunc(:p); end;');
oci_bind_by_name($stid, ':p', $p);

// La valeur retournée est un lien OUT. Le type par défaut devra être
// de type string, aussi, l'association sur une longueur de 40 signifie
// que la chaîne retournée fera au maximum 40 caractères.
oci_bind_by_name($stid, ':r', $r, 40);

oci_execute($stid);

print "$r\n";   // Affiche 24

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #11 Association de paramètres à une procédure stockée PL/SQL

<?php

// Avant d'exécuter le programme PHP, une procédure stockée doit être créée
// au format SQL*Plus ou SQL Developer :
//
//  CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
//  BEGIN
//      p2 := p1 * 2;
//  END;

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

$p1 = 8;

$stid = oci_parse($conn, 'begin myproc(:p1, :p2); end;');
oci_bind_by_name($stid, ':p1', $p1);

// Le second paramètre de la procédure stockée est un lien OUT.
// Le type par défaut devra être de type string, aussi,
// l'association sur une longueur de 40 signifie
// que la chaîne retournée fera au maximum 40 caractères.

oci_bind_by_name($stid, ':p2', $p2, 40);

oci_execute($stid);

print "$p2\n";   // Affiche 16

oci_free_statement($stid);
oci_close($conn);

?>

Exemple #12 Association sur une colonne CLOB

<?php

// Avant l'exécution, il est nécessaire de créer la table :
//     CREATE TABLE mytab (mykey NUMBER, myclob CLOB);

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

$mykey = 12343;  // Clé arbitraire pour l'exemple ;

$sql = "INSERT INTO mytab (mykey, myclob)
        VALUES (:mykey, EMPTY_CLOB())
        RETURNING myclob INTO :myclob";

$stid = oci_parse($conn, $sql);
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
oci_execute($stid, OCI_DEFAULT);
$clob->save("A very long string");

oci_commit($conn);

// Récupération des données CLOB

$query = 'SELECT myclob FROM mytab WHERE mykey = :mykey';

$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_execute($stid);

print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) {
    print '<tr><td>'.$row['MYCLOB'].'</td></tr>';
    // Dans une boucle, le fait de libérer la variable avant la récupération de
    // la ligne suivante réduit l'utilisation mémoire de PHP
    unset($row);  
}
print '</table>';

?>

Exemple #13 Association sur un booléen dans un script PL/SQL

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message']), E_USER_ERROR);
}

$plsql = 
  "begin
    :output1 := true;
    :output2 := false;
   end;";

$s = oci_parse($c, $plsql);
oci_bind_by_name($s, ':output1', $output1, -1, OCI_B_BOL);
oci_bind_by_name($s, ':output2', $output2, -1, OCI_B_BOL);
oci_execute($s);
var_dump($output1);  // true
var_dump($output2);  // false

?>

Notes

Avertissement

N'utiliser pas la fonction addslashes() et oci_bind_by_name() simultanément, car aucun ajout de guillemets n'est nécessaire. Si c'est le cas, alors les guillemets ajoutés seront écrits dans la base de données ; en effet, la fonction oci_bind_by_name() insère les données brutes et ne supprime ni les guillemets ajoutés, ni les caractères d'échappement.

Note:
Si l'on associe une chaîne de caractères à une colonne de type CHAR dans une clause WHERE, il faut se souvenir qu'Oracle utilise une comparaison en ajoutant des caractères vides pour les colonnes de type CHAR. Aussi, la variable PHP doit être complétée par des caractères vides afin d'atteindre la même taille que la colonne pour que la clause WHERE fonctionne.

Note:
L'argument PHP var est une référence. Aussi, quelques formats de boucle peuvent ne pas fonctionner comme prévu :

<?php foreach ($myarray as $key => $value) { oci_bind_by_name($stid, $key, $value); } ?>

Ceci associe chaque clé à la valeur pointée par $value, aussi, toutes les variables associées pointent vers la valeur de la dernière itération de la boucle. A la place, utiliser ceci :

<?php foreach ($myarray as $key => $value) { oci_bind_by_name($stid, $key, $myarray[$key]); } ?>

Voir aussi

oci_bind_array_by_name() - Lie un tableau PHP à un paramètre de tableau Oracle PL/SQL
oci_parse() - Prépare une requête SQL avec Oracle