PDOStatement::bindValue
Binds a value to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.
Parameters
Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name . For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
The value to bind to the parameter.
Explicit data type for the parameter using the PDO::PARAM_* constants.
Return Values
Returns true on success or false on failure.
Errors/Exceptions
Emits an error with level E_WARNING if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_WARNING .
Throws a PDOException if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION .
Examples
Example #1 Execute a prepared statement with named placeholders
/* Execute a prepared statement by binding PHP variables */
$calories = 150 ;
$colour = ‘red’ ;
$sth = $dbh -> prepare ( ‘SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour' );
$sth -> bindValue ( ‘calories’ , $calories , PDO :: PARAM_INT );
/* Names can be prefixed with colons «:» too (optional) */
$sth -> bindValue ( ‘:colour’ , $colour , PDO :: PARAM_STR );
$sth -> execute ();
?>
Example #2 Execute a prepared statement with question mark placeholders
/* Execute a prepared statement by binding PHP variables */
$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 ();
?>
See Also
- PDO::prepare() — Prepares a statement for execution and returns a statement object
- PDOStatement::execute() — Executes a prepared statement
- PDOStatement::bindParam() — Binds a parameter to the specified variable name
User Contributed Notes 14 notes
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.
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.
Be careful when trying to validate using PDO::PARAM_INT.
Take this sample into account:
/* 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 > );
$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 =(
// 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.
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.
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
$query = ‘SELECT * FROM `users` WHERE username = :username AND `password` = ENCRYPT( :password, `crypt_password`)’ ;
// 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
This function is useful for bind value on an array. You can specify the type of the value in advance with $typeArray.
/**
* @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.
* */
?>
$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 );
This actually works to bind NULL on an integer field in MySQL :
$stm->bindValue(‘:param’, null, PDO::PARAM_INT);
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:
$st = $db -> prepare ( «SELECT * FROM posts WHERE » );
$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.
$st = $db -> prepare ( «SELECT * FROM posts WHERE » );
$st -> bindParam ( ‘:val’ , $val );
$val = ‘2’ ;
//
// some code
//
$val = ‘3’ ; // re-assigning the value variable
$st -> execute ();
?>
works fine.
bindValue with data_type depend parameter name
$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 ();
>
For bind whole array at once
function PDOBindArray (& $poStatement , & $paArray )
@ $poStatement -> bindValue ( ‘:’ . $k , $v );
$stmt = $dbh -> prepare ( «INSERT INTO tExample (id,value) VALUES (:id,:value)» );
$taValues = array(
‘id’ => ‘1’ ,
‘value’ => ‘2’
); // array
PDOBindArray ( $stmt , $taValues );
Be careful in edge cases!
With MySQL native prepares your integer can be wrapped around on some machines:
$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.
The parameter must names like a php variable.
e.g.
$dbh = new PDO ( «mysql:dbname=test;host=127.0.0.1» , «user» , «password» );
$sth = $dbh -> prepare ( «SELECT * FROM `table` WHERE `last-name`=:last-name» );
// output: PHP Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
?>
If you want to bind a null value to a database field you must use ‘NULL’ in quotes (for MySQL):
$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.
PHP PDOStatement: bindParam vs bindValue
It’s commonplace to repeatedly execute a query, with each iteration using different parameters.
However, doing so using the conventional query() method and a looping mechanism comes at a cost of both overhead, because of the repeated parsing of the almost identical query for validity, and coding convenience, and the need to repeatedly reconfigure the query using the new values for each iteration.
Using prepared statement eliminate this problem. Thankfully, it is easy to accomplish in PHP using the PDOStatement class.
The bindValue and bindParam PDOStatement methods are both use in binding a variable to a parameter (name or question mark placeholder use in SQL statement).
Although they’re quite similar, they differ in how they work.
The bindParam binds a parameter exclusively to a specified variable name which is bound as a reference while the bindValue binds a value which could be a variable, an integer or string to a parameter.
- Both bind a variable to a placeholder’s parameter.
In Both bindParam and bindValue , a variable can be binded to a parameter.
< ?php $name = 'collins'; $sql = 'INSERT INTO feeds (name) VALUES (:name)'; $update = $conn ->prepare($sql); $update -> bindParam(':name', $name); $update -> execute(); < ?php $name = 'collins'; $sql = 'INSERT INTO feeds (name) VALUES (:name)'; $update = $conn ->prepare($sql); $update -> bindValue(':name', $name); $update -> execute(); $a = 'good boy'; $b = &$a; // $a and $b both equal "good boy" $b = "bad boy"; // $a and $b both equal "bad boy" echo "$a"; // echos "bad boy" Taking a look at the description of “bindParam”, you could see that the method’s second argument accepts a variable that is pass as a reference and will only be evaluated at the time that the execute() is called.
Thus, we can still change the value of a variable bonded to a parameter even after the bindParam() method had been called like so:
$name = 'collins'; $sql = 'INSERT INTO feeds (name) VALUES (:name)'; $update = $conn -> prepare($sql); $update -> bindParam(':name', $name); $name = 'john'; $update -> execute(); // execute with john inserted into the column "name" * Binding an integer to a parameter via “bindValue”.
< ?php $sql = 'INSERT INTO feeds (numbers) VALUES (:number)'; $update = $conn ->prepare($sql); $update -> bindValue(':number', 100); $update -> execute(); * Binding a string to a parameter via “bindValue”.
< ?php $sql = 'INSERT INTO feeds (names) VALUES (:name)'; $update = $conn ->prepare($sql); $update -> bindValue(':name', 'collins'); $update -> execute(); When you try to bind say, a string or number (float / integer) to a parameter using bindParam , you’ll get this error:
Fatal error: Cannot pass parameter 2 by reference
Conclusion
In this article, I illustrated to us the difference between bindParam and bindValue.
Have questions or contributions, let me know via comment. I’ll do my best to answer them.