PHP Pdo::Bindparam() Data Types.. How Does It Work

PHP PDO::bindParam() data types.. how does it work?

In other DB abstraction frameworks in other languages it can be used for things like making sure you're doing the proper escaping for in-lining values (for drivers that don't support proper bound parameters) and improving network efficiency by making sure numbers are binary packed appropriately (given protocol support). It looks like in PDO, it doesn't do much.

   if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_STR && param->max_value_len <= 0 && ! ZVAL_IS_NULL(param->parameter)) {
                if (Z_TYPE_P(param->parameter) == IS_DOUBLE) {
                        char *p;
                        int len = spprintf(&p, 0, "%F", Z_DVAL_P(param->parameter));
                        ZVAL_STRINGL(param->parameter, p, len, 0);
                } else {
                        convert_to_string(param->parameter);
                }
        } else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_INT && Z_TYPE_P(param->parameter) == IS_BOOL) {
                convert_to_long(param->parameter);
        } else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_BOOL && Z_TYPE_P(param->parameter) == IS_LONG) {
                convert_to_boolean(param->parameter);
        }

So, if you say it is a STR (or if you say nothing at all as that is the default) and your data's internal type is a double then it will turn it into a string using one method, if it's not a double then it will convert it to a string using a different method.

If you say it's an int but it is really a bool then it will convert it to a long.

If you say it's a bool but it's really a number then it will convert it to a true boolean.

This is really all I saw (quickly) looking at the stmt source, I imagine once you pass the parameters into the driver they can do additional magic. So, I'd guess that all you get is a little bit of do the right and a whole lot of behavior ambiguity and variance between drivers.

bindParam works correctly no matter what data type is specified or given

PDO::PARAM_INT and PDO::PARAM_STR when passed to bindParam() are indications that the driver is free to ignore.

Looking at PDO pg driver's source code, it appears that, except for PDO_PARAM_LOB which is treated specially, all types are quoted as strings (that is, between quotes and passed to libpq's PQescapeStringConn function)

You should also be aware of the PDO::ATTR_EMULATE_PREPARES attribute that controls what method is used under the hood. When false, PQprepare() is used with real out-of-query parameters. If true, parameter values are injected into the SQL passed to the non-parametrized PQexec().
Technically, this is quite different, so you may see differing behaviors in corner cases or error cases depending on this attribute.

Why do we need to specify the parameter type in bindParam()?

Using bindParam() with types could be considered safer, because it allows for stricter verification, further preventing SQL injections. However, I wouldn't say there is a real security risk involved if you don't do it like that, as it is more the fact that you do a prepared statement that protects from SQL injections than type verification. A simpler way to achieve this is by simply passing an array to the execute() function instead of using bindParam(), like this:

$calories = 150; 
$colour = 'red';

$sth = $dbh->prepare('SELECT name, colour, calories
                      FROM fruit
                      WHERE calories < :calories AND colour = :colour');

$sth->execute(array(
    'calories' => $calories,
    'colour' => $colour
));

You're not obligated to use a dictionary, you can also do it just like you did with questionmarks and then put it in the same order in the array. However, even if this works perfectly, I'd recommend making a habit of using the first one, since this method is a mess once you reach a certain number of parameters. For the sake of being complete, here's what it looks like:

$calories = 150; 
$colour = 'red';

$sth = $dbh->prepare('SELECT name, colour, calories
                      FROM fruit
                      WHERE calories < ? AND colour = ?');

$sth->execute(array($calories, $colour));

PDO::PARAM_INT is important in bindParam?

Yes, use it.

I did a few tests (with PDO::ATTR_EMULATE_PREPARES false) and I found out that the quotes around the values will be different.

When you bind an integer value with PARAM_INT there will be no quotes in the query (A string value with PARAM_INT has quotes). If you bind an integer value with PDO::PARAM_STR there will be quotes and mysql has to cast to integer.

Examples:

$stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT);
$threadid = 123;
// SELECT TagId FROM tagthread WHERE ThreadId = 123
$threadid = '123test';
// SELECT TagId FROM tagthread WHERE ThreadId = '123test'
// mysql will cast 123test to 123

EDIT:

I further tested and read on that topic. Conclusion: Implicit casting is dangerous and can lead to unexpected results. Read more on that here. Another disadvantage to always use PDO::PARAM_STR is the performance. Read more on performance Disadvantages of quoting integers in a Mysql query?

So if your column is of type [TINY|SMALL|MEDIUM|BIG]INT than use PARAM_INT. And in case it is a LIMIT clause than cast to integer if the variable type in PHP is not integer.

PHP pdo bindParam type management

You've got several mistakes in your code.

However, let's go over what the types PDO::PARAM_INT, PDO::PARAM_STR and PDO::PARAM_NULL are telling MySQL to do.

Those values are telling PDO how to treat the input, not to disallow input. If you send text, but the column is int then MySQL will attempt to coerce the data into int. It won't tell you "you entered abcd but expected value was integer". You must do this check on your own before passing data to PDO.

Now onto other problems:

• Don't use bindParam. bindParam accepts the value by reference. This is intended for when you invoke stored procedures and variable is supposed to be modified based on procedure's output. Use bindValue. If you tried to do the following with bindParam, it wouldn't work and you'd get an error:

  $stmt->bindParam(':my_column', 1, PDO::PARAM_INT); // It fails and yields an error

• Don't lock tables. You're already using transactions, no need to lock the table, MySQL handles concurrency and access for you.

Bottom line - perform validation before using PDO for inserting. PDO helps you clean the input based on connection information (among other things). It won't perform validation.

PDO bindParam variable datatype from array

Just change your array definition to use the PDO constants like this:

$imp->datafields=array(
  "id"           => PDO::PARAM_INT,
  "name"         => PDO::PARAM_STR,
  "cookieLength" => PDO::PARAM_INT
);

And then in your foreach loop just use $value alone like this:

$stmt->bindParam(":$key", $program->$key, $value);
               //^     ^                  ^^^^^^

And also use double quotes that the variables gets parsed in it!

What you could also do if you really want it just to use constant() in every iteration like this:

$stmt->bindParam(":$key", $program->$key, constant("PDO::$value"));

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);

List of PDOStatement::bindParam data_type parameters

From the documentation here:

PDO::PARAM_BOOL (integer)
Represents a boolean data type.
PDO::PARAM_NULL (integer)
Represents the SQL NULL data type.
PDO::PARAM_INT (integer)
Represents the SQL INTEGER data type.
PDO::PARAM_STR (integer)
Represents the SQL CHAR, VARCHAR, or other string data type.
PDO::PARAM_LOB (integer)
Represents the SQL large object data type.
PDO::PARAM_STMT (integer)
Represents a recordset type. Not currently supported by any drivers.
PDO::PARAM_INPUT_OUTPUT (integer)
Specifies that the parameter is an INOUT parameter for a stored procedure. You must bitwise-OR this value with an explicit PDO::PARAM_* data type.

How define the variable type in PDOStatement::bindValue()?

I'm no PDO-expert, but I can think of a few scenarioes where the data_type parameter is both useful and even needed.

Output parameters

When you define output or input/output parameters, you must provide both type and length of the expected output parameter.

Ref: http://www.php.net/manual/en/pdo.prepared-statements.php

Example #4

$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

Example #5

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

DBMs without implicit casting

Explained in another answer to this question...

When parameter is not bound to castable data

Even databases with casting abilities will not always be able to cast you variable correctly.

Ref: Reasons to strongly type parameters in PDO?

$limit = 1;

$dbh->prepare("SELECT * FROM items LIMIT :limit");
$dbh->bindParam(":limit", $limit, PDO::PARAM_STR); 
// Will throw "You have an error in your SQL syntax..."

---

Related Topics