Confusion Between Bindvalue() and Bindparam()

What is the difference between bindParam and bindValue?

The answer is in the documentation for bindParam:

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

And execute

call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

Example:

$value = 'foo';
$s = $dbh->prepare('SELECT name FROM bar WHERE baz = :baz');
$s->bindParam(':baz', $value); // use bindParam to bind the variable
$value = 'foobarbaz';
$s->execute(); // executed with WHERE baz = 'foobarbaz'

or

$value = 'foo';
$s = $dbh->prepare('SELECT name FROM bar WHERE baz = :baz');
$s->bindValue(':baz', $value); // use bindValue to bind the variable's value
$value = 'foobarbaz';
$s->execute(); // executed with WHERE baz = 'foo'

Confusion between bindValue() and bindParam()?

There should be no difference in how values are escaped or not escaped. bindParam differs from bindValue in that it references the variable, binding the value only when you execute the statement. bindValue takes the value immediately. To illustrate:

$stmt = $db->prepare('SELECT * FROM `table` WHERE foo = :foo');

$foo = 'foo';
$stmt->bindValue(':foo', $foo);
$foo = 'bar';

$stmt->execute();

The above executes like SELECT * FROM table WHERE foo = 'foo';

$stmt = $db->prepare('SELECT * FROM `table` WHERE foo = :foo');

$foo = 'foo';
$stmt->bindParam(':foo', $foo);
$foo = 'bar';

$stmt->execute()

The above executes like SELECT * FROM table WHERE foo = 'bar'.

It's true that neither cares about _ or % as special characters, because generally speaking they aren't special characters as far as the syntax is concerned, and the database driver is not able to analyze the context to figure out whether you mean % to be a wildcard or the actual character "%" in the context of a LIKE query.

Both protect against SQL injection.

Can i use both bindValue and bindParam on a single prepared statement?

For the SQL statement there is no difference between bindValue() and bindParam(). The only difference is how--or rather, when--PHP reads the input variables. So you can use both together, although that may cause hard-to-find issues (regarding the result of the SQL operation) later.

So it's best to avoid confusion and stick to bindValue() unless you need the special behaviour of bindParam().

What is the differences and advantages between bindParam(), bindValue() and execute(array())

bindParam() binds the parameter by reference, so it will be evaluated at $stmt->execute(), which is unlike bindValue() which evaluates at the call of the function itself.

So as an example:

bindParam:

<?php

try {

$dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbh->prepare("SELECT * FROM test WHERE number = ?");
$stmt->bindParam(1, $xy, PDO::PARAM_INT);
$xy = 123; //See here variable is defined after it has been bind
$stmt->execute();

print_r($stmt->fetchAll());

} catch(PDOException $e) {
echo $e->getMessage();
}

?>

works great!

bindValue:

<?php

try {

$dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbh->prepare("SELECT * FROM test WHERE number = ?");
$stmt->bindValue(1, $xy, PDO::PARAM_INT);
$xy = 123; //See here variable is defined after it has been bind
$stmt->execute();

print_r($stmt->fetchAll());

} catch(PDOException $e) {
echo $e->getMessage();
}

?>

output:

Notice: Undefined variable: xy

Also a few other differences:

  • bindParam() also has the argument length which can(must) be used if you call a IN&OUT procedure to store the output back in the variable (Which also requires to append PDO::PARAM_INPUT_OUTPUT with an OR statement to the type argument)
  • With bindParam() & bindValue() you can specify the type of the value, which you can't do in the execute(), there everything is just a string (PDO::PARAM_STR)

PDO bindparam and bindvalue. Which is the diference?

You don't have to set the lenght for outputting, you can do this if your want.

This is what the manual says about the difference between bindValue() and bindParam()

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL >statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the >variable is bound as a reference and will only be evaluated at the time that >PDOStatement::execute() is called.

Most parameters are input parameters, that is, parameters that are used in a read-only >fashion to build up the query. Some drivers support the invocation of stored procedures >that return data as output parameters, and some also as input/output parameters that both >send in data and are updated to receive it.

I use always bindParam when I want to add a variable. But I guess you have to make your own decission for that.

PHP PDO with bindParam vs bindValue?

PDOStatement::bindParam() is a way to tell PDO: «when you execute this statement later, please read the value from this variable». That means that when you call $STH -> execute() the $value must exist and must keep the intended value.

Since you are using it this way:

foreach($fields as $dbfield => $field) {
$value = isset($_POST[$field]) ? 1 : 0;
$STH -> bindParam( ':' . $dbfield, $value, PDO::PARAM_INT, 1 );
}

... $value gets overwritten on each loop iteration and your values get lost. (Only the last one remains.)

You need to use PDOStatement::bindValue() that means: «store this value and use it later when you execute the statement». This way, the variable is no longer necessary.

Can I pass a JavaScript variable to another browser window?

Provided the windows are from the same security domain, and you have a reference to the other window, yes.

Javascript's open() method returns a reference to the window created (or existing window if it reuses an existing one). Each window created in such a way gets a property applied to it "window.opener" pointing to the window which created it.

Either can then use the DOM (security depending) to access properties of the other one, or its documents,frames etc.

Does PDO::bindParam / bindValue throw an exception if the data type is different from the supplied?

At least for mysql - no, it will never throw an error.

Moreover, 99.99% of time mysql is happy with PDO::PARAM_STR for any type. Therefore, you can simplify your insert function dramatically, omitting all the unnecessary stuff from the $values array:

public function insert($sql, $values){
return $this->connect()->prepare($sql)->execute($values);
}
$array = array('jotName' => 'some jot string');
$db->insert($sql, $array);


Related Topics



Leave a reply



Submit