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

Question

While introducing myself to pgSQL prepared statements, I've successfully returned the results of a few queries. However, I have a few questions.

Given the following query:

$w_ft = "36"; 
$sth = $dbh->prepare("SELECT * FROM main_products_common_dimensions WHERE w_ft = :w_ft");
$sth->bindParam(':w_ft', $theId, PDO::PARAM_INT);
$sth->execute();
$result = $sth->fetchAll();

I notice that even though the column in the main_products_common_dimensions table is a character_varying, I get the same/correct result set returned if I use

$w_ft = 36; 
...
$sth->bindParam(':w_ft', $w_ft, PDO::PARAM_INT);

and

$w_ft = "36"; 
...
$sth->bindParam(':w_ft', $w_ft, PDO::PARAM_STR);

and

$w_ft = "36"; 
...
$sth->bindParam(':w_ft', $w_ft, PDO::PARAM_INT);

and

$w_ft = 36; 
...
$sth->bindParam(':w_ft', $w_ft, PDO::PARAM_STR);

That is, no matter how I bind the parameter _INT or _STR or set the variable (integer or string), the data is returned correctly. Is this normal behavior?

From http://php.net/manual/en/pdostatement.bindparam.php, I see that the parameter datatype is explained

Explicit data type for the parameter using the PDO::PARAM_* constants. To return an INOUT parameter from a stored procedure, use the bitwise OR operator to set the PDO::PARAM_INPUT_OUTPUT bits for the data_type parameter.

What is meant by "returning an INOUT parameter from a stored procedure"? Is this related? Does that imply that I am not using a stored procedure? Length seems to be optional, though that is not indicated in its explanation. Are there advantages to providing it?

As you can see, I'm quite new to this, and just trying to get my head around it. Thank you very much

Answer 1

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.

Answer 2

This is because postgres accepts the quoting of integers when comparing with an integer column.

So if your id column is an int, these queries both work the same:

SELECT * FROM mytable WHERE id = 1;
SELECT * FROM mytable WHERE id = '1';

What the PDO::PARAM_* constants do is modify the way the quoting/escaping is done to the values, and is independent of the value datatype. Php also will make a type conversion if needed. If you choose PDO::PARAM_INT you are telling the DBMS driver that the value of $id is an integer, and should escape it as an integer, so it will not add quotes around it when it puts the value into the query.

$id = 1;
$sth = $db->prepare("SELECT * FROM mytable WHERE id = :id");
$sth->bindParam(':id', $id, PDO::PARAM_INT);
// resulting query would be SELECT * FROM mytable WHERE id = 1;

$sometext = "hello";
$sth = $db->prepare("SELECT * FROM mytable WHERE id = :id");
$sth->bindParam(':id', $sometext, PDO::PARAM_INT);
// in this case, $sometext will be casted to an integer, that will result in (int)0
// resulting query would be SELECT * FROM mytable WHERE id = 0;

$sometext = "hello";
$sth = $db->prepare("SELECT * FROM mytable WHERE id = :id");
$sth->bindParam(':id', $sometext, PDO::PARAM_STR);
// in this case, $sometext is already a string, and strings should be quoted
// resulting query would be SELECT * FROM mytable WHERE id = 'hello';

Also, about the INOUT param of bindParam, if you aren't going to use INOUT or OUT parameters of a stored procedure (like, say, passing a reference to a function call than is setted inside the function), you probably are better using bindValue. Using bindValue you can put the result of a function or any constant value as the value to bind, you don't need to put a variable.

$sth->bindValue(':something', 5);
$sth->bindValue(':something_else', $foo->bar());

Answer 3

$w_ft = 36; 
...
$sth->bindParam(':w_ft', $theId, PDO::PARAM_INT);

should be

$w_ft = 36; 
...
$sth->bindParam(':w_ft', $w_ft, PDO::PARAM_INT);