PDOStatement::bindParam
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.
* */
?>
PDOStatement::bindValue
(PHP 5 >= 5.1.0, PECL pdo >= 1.0.0)
PDOStatement::bindValue — 値をパラメータにバインドする
説明
bool PDOStatement::bindValue
( mixed
$parameter
, mixed $value
[, int $data_type = PDO::PARAM_STR
] )プリペアドステートメントで使用する SQL 文の中で、 対応する名前あるいは疑問符のプレースホルダに値をバインドします。
パラメータ
-
parameter -
パラメータ ID。名前つきプレースホルダを使用する プリペアドステートメントの場合は、 :name 形式のパラメータ名となります。 疑問符プレースホルダを使用するプリペアドステートメントの場合は、 1 から始まるパラメータの位置となります。
-
value -
パラメータにバインドする値。
-
data_type -
パラメータに対して PDO::PARAM_* 定数を使った明示的なデータ型を指定します。
返り値
成功した場合に TRUE を、失敗した場合に FALSE を返します。
例
例1 名前付けされたプレースホルダを用いてプリペアドステートメントを実行する
<?php
/* バインドされた PHP 変数によってプリペアドステートメントを実行する */
$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);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>
例2 疑問符プレースホルダを用いてプリペアドステートメントを実行する
<?php
/* バインドされた PHP 変数によってプリペアドステートメントを実行する */
$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();
?>
参考
- PDO::prepare() - 文を実行する準備を行い、文オブジェクトを返す
- PDOStatement::execute() - プリペアドステートメントを実行する
- PDOStatement::bindParam() - 指定された変数名にパラメータをバインドする
add a note
User Contributed Notes
PDOStatement::bindValue
contact[at]maximeelomari.com
17-Jul-2011 05:19
Anonymous
18-Jun-2011 08:40
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
?>
goofiq dot no dot spam at antispam dot wp dot pl
27-Dec-2009 10:43
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 ();
}
}
?>
cpd-dev
11-Dec-2009 04:46
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.
nicolas dot baptiste at gmail dot com
04-Sep-2009 08:06
This actually works to bind NULL on an integer field in MySQL :
$stm->bindValue(':param', null, PDO::PARAM_INT);
Lambdaman
30-Apr-2009 05:19
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.
nicemandan
11-Feb-2009 11:54
I've slightly altered the PDOBindArray function above so it can receive data types, which will help against injection attacks.
<?php
private function PDOBindArray(&$poStatement, &$paArray){
foreach ($paArray as $k=>$v) {
@$poStatement->bindValue($k, $v[0], $v[1]);
}
}
// the array structure should now look something like this
$inputArray = array(
':email' => array($email, PDO::PARAM_STR),
':pass' => array($pass, PDO::PARAM_INT)
);
?>
Anonymous
25-Aug-2008 04:31
PDO lacks methods to check if values can be bound to a parameter, e.g.,
if ($statement->hasParameter(':param'))
{
$statement->bindValue(':param', $value);
}
ATM you *have to know* which parameters exist in the SQL-statement. Otherwise you get an error. You cannot test for them.
streaky at mybrokenlogic dot com
08-Jan-2008 02:20
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.
joe at dsforge dot net
01-Oct-2007 06:46
note that bindParam() doesn't let you bind a table name into a prepared statement, whereas this can be done with bindValue()...
ts//tpdada//art//pl
15-Dec-2006 06:34
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();
?>
Chris L
26-May-2006 03:43
I'm not sure if this is intentional or not, but you can't use a placeholder more than once. I assumed (wrongly) that bindValue() would replace ALL instances of a given placeholder with a value. For example:
<?php
// $db is a PDO object
$stmt = $db->prepare
('
insert into
TableA
(
ID,
Name,
Foo
)
select
null,
:Name,
:Foo
from
TableA
where
Foo = :Foo
');
$stmt->bindValue(':Name', 'john doe');
$stmt->bindValue(':Foo', 'foo');
$stmt->execute();
?>
This apparently won't work - you must have separate :SelectFoo and :WhereFoo. I'm using PHP 5.0.4, MySQL 5.0.14, and PDO version 1.0.2.