PDOStatement::bindValue

(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 1.0.0)

PDOStatement::bindValue — Bindet einen Wert an einen Parameter

Beschreibung

public PDOStatement::bindValue(string|int $param, mixed $value, int $type = PDO::PARAM_STR): bool

Bindet einen Wert an den entsprechend benannten oder durch ein Fragezeichen gekennzeichneten Platzhalter in der SQL-Anweisung, die zur Vorbereitung der Anweisung verwendet wurde.

Parameter-Liste

param: Der Bezeichner des Parameters. Bei einer vorbereiteten Anweisung, die benannte Platzhalter verwendet, ist dies ein Parametername der Form :name. Bei einer vorbereiteten Anweisung mit Fragezeichen-Platzhaltern ist dies die Position des Parameters, beginnend mit 1 (1-indiziert).
value: Der Wert, der an den Parameter gebunden werden soll.
type: Ein expliziter Datentyp für den Parameter, der durch eine der PDO::PARAM_*-Konstanten angegeben wird.

Rückgabewerte

Gibt bei Erfolg true zurück. Bei einem Fehler wird false zurückgegeben.

Fehler/Exceptions

Gibt einen Fehler der Stufe E_WARNING aus, wenn das Attribut PDO::ATTR_ERRMODE auf PDO::ERRMODE_WARNING gesetzt ist.

Löst eine PDOException aus, wenn das Attribut PDO::ATTR_ERRMODE auf PDO::ERRMODE_EXCEPTION gesetzt ist.

Beispiele

Beispiel #1 Ausführen einer vorbereiteten Anweisung mit benannten Platzhaltern

<?php
/* Ausführen einer vorbereiteten Anweisung durch Binden von PHP-Variablen */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');

/* Setzt den Wert eines Parameters anhand seines Namens */
$sth->bindValue('calories', $calories, PDO::PARAM_INT);
/* Optional kann dem Namen auch ein Doppelpunkt ":" vorangestellt werden */
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

Beispiel #2 Ausführen einer vorbereiteten Anweisung mit Fragezeichen-Platzhaltern

<?php
/* Ausführen einer vorbereiteten Anweisung durch Binden von PHP-Variablen */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->bindValue(1, $calories, PDO::PARAM_INT);
$sth->bindValue(2, $colour, PDO::PARAM_STR);
$sth->execute();
?>

Siehe auch

PDO::prepare() - Bereitet eine Anweisung zur Ausführung vor und liefert ein Anweisungsobjekt
PDOStatement::execute() - Führt ein Prepared Statement aus
PDOStatement::bindParam() - Bindet einen Parameter an den angegebenen Variablennamen

add a note

User Contributed Notes 14 notes

down

154

streaky at mybrokenlogic dot com ¶

16 years ago


What the bindValue() docs fail to explain without reading them _very_ carefully is that bindParam() is passed to PDO byref - whereas bindValue() isn't.

Thus with bindValue() you can do something like $stmt->bindValue(":something", "bind this"); whereas with bindParam() it will fail because you can't pass a string by reference, for example.

down

D.Kellner ¶

8 years ago


When binding parameters, apparently you can't use a placeholder twice (e.g. "select * from mails where sender=:me or recipient=:me"), you'll have to give them different names otherwise your query will return empty handed (but not fail, unfortunately).  Just in case you're struggling with something like this.

down

e-ruiz at git hub ¶

8 years ago


Be careful when trying to validate using PDO::PARAM_INT. 

Take this sample into account:

<?php
/* php --version
 * PHP 5.6.25 (cli) (built: Aug 24 2016 09:50:46)
 * Copyright (c) 1997-2016 The PHP Group
 * Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
 */

$id = '1a'; 
$stm = $pdo->prepare('select * from author where id = :id');
$bind = $stm->bindValue(':id', $id, PDO::PARAM_INT);

$stm->execute();
$authors = $stm->fetchAll();

var_dump($id);         // string(2)
var_dump($bind);       // true
var_dump((int)$id);    // int(1)
var_dump(is_int($id)); // false
var_dump($authors);    // the author id=1  =(

// remember
var_dump(1 == '1');    // true
var_dump(1 === '1');   // false
var_dump(1 === '1a');  // false
var_dump(1 == '1a');   // true
?>

My opinion: bindValue() should test is_int() internaly first of anything, 
It is a bug? I'm not sure.

down

cpd-dev ¶

15 years ago


Although bindValue() escapes quotes it does not escape "%" and "_", so be careful when using LIKE. A malicious parameter full of %%% can dump your entire database if you don't escape the parameter yourself. PDO does not provide any other escape method to handle it.

down

Anonymous ¶

13 years ago


Note that the third parameter ($data_type) in the majority of cases will not type cast the value into anything else to be used in the query, nor will it throw any sort of error if the type does not match up with the value provided. This parameter essentially has no effect whatsoever except throwing an error if it is set and is not a float, so do not think that it is adding any extra level of security to the queries.

The two exceptions where type casting is performed:

- if you use PDO::PDO_PARAM_INT and provide a boolean, it will be converted to a long
- if you use PDO::PDO_PARAM_BOOL and provide a long, it will be converted to a boolean

<?php

$query = 'SELECT * FROM `users` WHERE username = :username AND `password` = ENCRYPT( :password, `crypt_password`)';

$sth= $dbh->prepare($query);

// First try passing a random numerical value as the third parameter
var_dump($sth->bindValue(':username','bob', 12345.67)); // bool(true)

// Next try passing a string using the boolean type
var_dump($sth->bindValue(':password','topsecret_pw', PDO::PARAM_BOOL)); // bool(true)

$sth->execute(); // Query is executed successfully
$result = $sth->fetchAll(); // Returns the result of the query

?>

down

contact[at]maximeelomari.com ¶

13 years ago


This function is useful for bind value on an array. You can specify the type of the value in advance with $typeArray.

<?php
/**
 * @param string $req : the query on which link the values
 * @param array $array : associative array containing the values to bind
 * @param array $typeArray : associative array with the desired value for its corresponding key in $array
 * */
function bindArrayValue($req, $array, $typeArray = false)
{
    if(is_object($req) && ($req instanceof PDOStatement))
    {
        foreach($array as $key => $value)
        {
            if($typeArray)
                $req->bindValue(":$key",$value,$typeArray[$key]);
            else
            {
                if(is_int($value))
                    $param = PDO::PARAM_INT;
                elseif(is_bool($value))
                    $param = PDO::PARAM_BOOL;
                elseif(is_null($value))
                    $param = PDO::PARAM_NULL;
                elseif(is_string($value))
                    $param = PDO::PARAM_STR;
                else
                    $param = FALSE;
                    
                if($param)
                    $req->bindValue(":$key",$value,$param);
            }
        }
    }
}

/**
 * ## EXEMPLE ##
 * $array = array('language' => 'php','lines' => 254, 'publish' => true);
 * $typeArray = array('language' => PDO::PARAM_STR,'lines' => PDO::PARAM_INT,'publish' => PDO::PARAM_BOOL);
 * $req = 'SELECT * FROM code WHERE language = :language AND lines = :lines AND publish = :publish';
 * You can bind $array like that :
 * bindArrayValue($array,$req,$typeArray);
 * The function is more useful when you use limit clause because they need an integer.
 * */
?>

down

Vladimir Kovpak ¶

9 years ago


<?php
/**
* Bind bit value.
*/

$sql = 'SELECT * FROM myTable WHERE level & ?';
$sth = \App::pdo()->prepare($sql);
$sth->bindValue(1, 0b0101, \PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);

down

nicolas dot baptiste at gmail dot com ¶

15 years ago


This actually works to bind NULL on an integer field in MySQL :

$stm->bindValue(':param', null, PDO::PARAM_INT);

down

me at iabdullah dot info ¶

10 years ago


The reason that we cannot define the value variable for bindValue() after calling it, is because that it binds the value to the prepared statement immediately and does not wait until the execute() to happen.

The following code will issue a notice and prevent the query from taking place:
<?php
    $st = $db->prepare ("SELECT * FROM posts WHERE id= :val ");
    $st->bindValue(':val',$val);

    $val = '2';
    $st->execute();
?>
The output:
Notice: Undefined variable: val.

Whereas in the case of bindParam, the evaluation of the value to the parameter will not be performed until the call of execute(). And that's to gain the benefit of reference passing.
<?php
    $st = $db->prepare ("SELECT * FROM posts WHERE id = :val ");
    $st->bindParam(':val',$val);

    $val = '2';
    // 
    // some code
    //
    $val = '3'; // re-assigning the value variable
    $st->execute();
?>
works fine.

down

-3

goofiq dot no dot spam at antispam dot wp dot pl ¶

14 years ago


bindValue with data_type depend parameter name

<?php

$db = new PDO (...);
$db -> setAttribute (PDO::ATTR_STATEMENT_CLASS, array ('MY_PDOStatement ', array ($db)));

class MY_PDOStatement extends PDOStatement {

  public function execute ($input = array ()) {
    foreach ($input as $param => $value) {
      if (preg_match ('/_id$/', $param))
        $this -> bindValue ($param, $value, PDO::PARAM_INT);
      else
        $this -> bindValue ($param, $value, PDO::PARAM_STR);
    }
    return parent::execute ();
  }

}

?>

down

-5

ts//tpdada//art//pl ¶

18 years ago


For bind whole array at once

<?php

function PDOBindArray(&$poStatement, &$paArray){
 
  foreach ($paArray as $k=>$v){

    @$poStatement->bindValue(':'.$k,$v);

  } // foreach
  
 } // function

// example

$stmt = $dbh->prepare("INSERT INTO tExample (id,value) VALUES (:id,:value)");

$taValues = array(
 'id' => '1',
 'value' => '2'
); // array

PDOBindArray($stmt,$taValues);

$stmt->execute();

?>

down

-5

sageptr at gmail dot com ¶

8 years ago


Be careful in edge cases!
With MySQL native prepares your integer can be wrapped around on some machines:

<?php
$x = 2147483648;
var_dump($x); // prints: int(2147483648)
$s = $db->prepare('SELECT :int AS I, :str AS S;');
$s->bindValue(':int', $x, PDO::PARAM_INT);
$s->bindValue(':str', $x, PDO::PARAM_STR);
$s->execute();
var_dump( $s->fetchAll(PDO::FETCH_ASSOC) );
/* prints: array(2) {
  ["I"]=>
  string(11) "-2147483648"
  ["S"]=>
  string(10) "2147483648"
} */
?>

Also, trying to bind PDO::PARAM_BOOL in MySQL with native prepares can make your query silently fail and return empty set.

Emulated prepares work more stable in this cases, because they convert everything to strings and just decide whenever to quote argument or not to quote.

down

-11

consatangmailcom ¶

9 years ago


The parameter must names like a php variable.
e.g.
<?php
$dbh = new PDO("mysql:dbname=test;host=127.0.0.1", "user", "password");

$sth = $dbh->prepare("SELECT * FROM `table` WHERE `last-name`=:last-name");

if($sth !== false && $sth->bindValue(":last-name", "Ngo")) {
    $sth->execute();
}

// output: PHP Warning:  PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
?>

down

-29

Lambdaman ¶

15 years ago


If you want to bind a null value to a database field you must use 'NULL' in quotes (for MySQL):



<?php



$stmt->bindValue(:fieldName, 'NULL');



// not

$stmt->bindValue(:fieldName, NULL);

// or

$stmt->bindValue(:fieldName, null);



?>



Using PHP's null/NULL as a value doesn't work.

add a note