You might consider making this a living document and posting it on github. People could then use issues or even pull requests to make suggestions.
2 Likes
@Lemon_Juice, thanks a ton!
It’s the very insight I were looking for. Going to rethink and rewrite related sections completely.
An interesting outcome: it looks like that the idea of type-hinted placeholders, of which I am a big fan, proved to be more useful than I thought lately.
Some time ago for my mysqli wrapper I adopted the idea of marking placeholders with type, like ?i, ?s etc. instead of just ?, thus making the driver aware of the data type without the need of external binding (yet at the cost of additional query parsing). Beside excellent usability of instant binding, it proved to be a very useful approach, because it allowed me to implement additional types like identifiers or arrays.
Yet for a time I abandoned the project, partly because I was unable to write a decent parser that would bypass quoted literals and extract placeholders anywhere else. Yet it seems that in my quest for the usable prepared statements I should try once more, as it turned out that instant binding that keeps precise typing is worth more than I thought before.
Thanks again, I really appreciate your time and effort!
1 Like
Actually, I had a look at your wrapper a few days ago and I think it is a great idea - in my opinion it should become part of native PDO as it elegantly solves the problem of passing type information. Some kind of syntax would need to be invented for named placeholders, for example i:placeholder.
1 Like
looks like an incredibly thorough tutorial. The git hub suggestion was pretty good. Hard to believe English is not your native language.
What is? Hope you don’t mind me asking.
1 Like
Thanks! It’s Russian. Some time ago I’ve got an idea that reading books can be used as an effective substitution of a language class, in either language. Say, my good native Russian I attributed to the very amount of books I were devouring at my youth. So I thought it might work for a foreign language as well and set out with reading books in English only. Judging by your reaction it seems that the theory proved to be true!
2 Likes
I doubt that it would be implemented in PDO. In fact, at the moment nobody is in charge for this library and its development is practically frozen. However, a userland implementation definitely should be made. I think I have to get back to the wrapper someday, though at the moment I am obsessed with writing tutorials. Say, right now I am working on the guide on PDO fetch modes, which counts as much as eighteen (including one broken), let alone mode combinations.
1 Like
pretty much, kudos all around on your language & coding skills! 
5 Likes
It’s a shame - I was thinking at least some minimal maintenance was done on an ongoing basis…
Actually, I wonder why parameters from execute() aren’t sent according to their PHP types, it would be most convenient in most cases:
$stmt->execute([(int) $var1, (string) $var2, (bool) $var3, (float) $var4]);
PDO should automatically send the values properly type-formatted instead of converting everything to a string. This would cover booleans and integers - only excluding bigints, which fall outside PHP’s int range. To me this looks like an oversight unless I’m missing some greater reason for the current situation. I think a PDO wrapper could take care of this easily. Your idea with type aware placeholders is great but parsing them in PHP might hamper performance much.
I would also like to add that sometimes prepared statement cannot send the correct type however hard we try. Consider this in mysql:
SELECT 300/200.5; # 1.4963
SELECT '300'/200.5; # 1.4962593516209477
SELECT 300.00/200.5; # 1.496259
The number 300 is treated as an integer, a float and a decimal respectively, and the result is different in each case. PDO can bind values as an integer or string (treated as float) but it can’t bind them as a decimal. Therefore, there are rare cases when prepared statements have their limits and we need to simply inject the value to SQL directly. But to be honest - I’ve never needed to bind a decimal in SQL!
1 Like
About 20 to 25 years ago almost all of the fields I was using with SQL were decimal. Back then I was mostly working on accounting and finance type applications and they use that data type almost exclusively. They don’t get used anywhere near so much in other types of application though - and since PHP doesn’t have a decimal data type you need to convert it when inserting the value into the database anyway.
1 Like
@Lemon_Juice, I toyed around with your values and ended up with filling a bug report for mysql, http://bugs.mysql.com/bug.php?id=80492
Looks like there is too dark a magic when bigints are involved.
Interesting, another example of mysql’s weirdness - I wonder if the devs will treat this bug report as something to fix since this behaviour is expected according to the documentation - quoted numbers are floats and they change the result of the whole calculation to a float. Which is weird that mysql defaults to a less precise arithmetic when numbers are quoted.
I’ve checked this in postgresql and your update with bigint from the bug report didn’t cause any problems. Also my selects from a post above with the number 300 formatted in different ways produced identical results. I think we need to accept the fact that each database will handle data types in its own way.
When digging deeper I find that mysql has some weird behaviour with data types in calculations. For example, there is no intuitive way to force a calculation to be done with float (double precision) accuracy. For example:
SELECT 300/200.5;
If number 300 is integer or decimal then the result will be a decimal value with a precision of 4 decimal places (=1.4963). Why 4 places? I don’t know - mysql has its rules for determining precision - they are certainly documented but not not really intuitive.
Anyway, let’s say we don’t want to change number 300 in the query because it can be a name of an integer column so it is a constant in our case, however we can change the expression of 200.5 because this is what we pass to the query. How can we force the calculation to use floating point precision? The only way I can think of is converting one of the values to a string because we cannot convert a number to a float in mysql - CAST(n AS FLOAT) does not exist. Therefore, this is what works:
SELECT 300/'200.5';
SELECT 300/CAST(200.5 AS CHAR);
SELECT CAST(300 AS CHAR)/200.5;
All of these return 1.4962593516209477. Isn’t it weird that in order to force floating point precision we need to cast values to a string? 
2 Likes
Had no time to answer it before but the problem is still bugging me.
On one hand, it’s no problem to make a userland implementation for this, just like many PDO wrappers do already.
On the other hand , there is a problem, which, I believe, makes it impossible.
When you compare a string in database with a number, the result is disastrous - the string being cast to a float, makes database not using an index and return weird result like 'foo' = 0.
So, with magical casting there is a chance to accidentally make a string compared to an int, with the outcome described above.
And you cannot tell wherever your numeric string will be cast to int. One of the possible cases is array keys. $arr['1'] will make an int key automatically. I am sure there are other cases. This is why, I believe, a magical binding has been never implemented.
If wee can find an overcome for this problem, it will be really great achievement.
Hm, I don’t fully understand where you see the problem - can you clarify it more? I wouldn’t call this idea magical casting (there’s no magic in it) - a better term would be automatic casting because the values are sent to the db in their php types.
Well, but the idea behind automatic casting is to send numbers as numbers - so you wouldn’t compare a string with a number - unless you purposefully wanted to. The problem exists in the current implementation where everything is sent as string so you are effectively comparing numbers with strings.
You would simply do:
$stmt = $pdo->prepare("SELECT * FROM t WHERE num=?");
$stmt->execute([5]);
and the automatic casting would send 5 as a number.
I don’t really understand the problem - can you provide examples of where this would be problematic?
Strings are not meant to be cast to ints - strings are meant to be sent as strings even if they contain numeric data.
But array keys don’t matter anyway, do they? PDO’s execute() takes an array as a parameter and uses values only, it doesn’t pass the keys to the db. Keys are only optionally used to identify named placeholders.
As someone who recently started to learn about prepared statements with mysqli (thanks to you), would you suggest that I shall move to PDO or continue learning deeper with mysqli? Is it “master in one or the other” or both?
I bookmarked your article, will go through it when I start to learn about PDO.
You only need to use one - they are very similar but PDO combines some functionality into a single statement that mySQLi needs to statements to do and so can result in slightly shorter code. PDO also allows you to use named placeholderrs in the prepare.
1 Like
The problem is in the direction.
SELECT * FROM t WHERE number = '1'
When you compare a number stored in database with a stringified number sent in a query, there is not [much] problem - a query value will be cast to a number and then compared using index, etc. - all the regular process.
SELECT * FROM t WHERE string = '1'
When you compare a string stored in database with a stringified number sent in a query, then nothing wrong happens: just a string comparison using index, etc. - all the regular process.
SELECT * FROM t WHERE string = 1
When you compare a string stored in database with a number sent in a query, then every value in this column will be cast to a number and compared. Which is an obvious and indisputable disaster.
The question is - how would a stringified number become an int? It is not that a regular case. So my question is, how to determine the probability of such a case.
But array keys don’t matter anyway, do they? PDO’s execute() takes an array as a parameter and uses values only,
I didn’t mean such a direct scenario but just a theoretical source for such a conversion. I had a problem recently with this PHP behavior exactly - when I used some data as array keys (namely car models) and got wrong comparison in a later process where both keys and values were involved - because keys that happened to be numbers were silently cast by PHP when they were used as array keys.
$s = '1';
$a[$s] = 2;
var_dump(key($a));
// int(1)
1 Like
@colshrapnel, I fully agree with you that comparing strings to numbers is not a good idea but I still don’t see why this would cause problems with automatic casting according to php types in the shorthand execute() with an array of values:
$stmt = $pdo->prepare("SELECT * FROM t WHERE int_col=? AND str_col=? AND bool_col=? AND float_col=?");
$stmt->execute([(int) $var1, (string) $var2, (bool) $var3, (float) $var4]);
Currently, all these values are sent as strings. My proposal is to send them according to their php types, so $var1 will be unquoted, etc. You said “there is a problem, which, I believe, makes it impossible” - I still don’t see where the problem is. I’m asking because I’m thinking of implementing and using such a solution and would gladly hear about any drawbacks this might have.
Why would a stringified number become an int? It wouldn’t - why would it? The programmer decides whether to send a number as a string or int - if it is disastrous to send it as int then he should send it as string. If it shouldn’t be converted to int then do not convert it. Where’s the problem?
Let’s consider the current alternatives:
$stmt = $pdo->prepare("SELECT * FROM t WHERE int_col=? AND str_col=? AND bool_col=? AND float_col=?");
$stmt->bindValue(1, (int) $var1, PDO::PARAM_INT);
$stmt->bindValue(2, (string) $var2, PDO::PARAM_STR); // or by default: $stmt->bindValue(2, $var2);
$stmt->bindValue(3, (bool) $var3, PDO::PARAM_BOOL);
$stmt->bindValue(4, $var4, PDO::PARAM_STR); // casting not necessary
$stmt->execute();
That is a bit verbose.
Then there is your idea with typed placeholders:
$stmt = $conn->prepare("SELECT * FROM t WHERE int_col=?i AND str_col=?s AND bool_col=?b AND float_col=?f");
$stmt->execute([$var1, $var2, $var3, $var4]);
Very nice but if it’s not implemented in a php extension then the performance will degrade due to necessary parsing.
Then there is the Doctrine DBAL way:
$stmt = $conn->prepare("SELECT * FROM t WHERE int_col=? AND str_col=? AND bool_col=? AND float_col=?");
$stmt->execute([$var1, $var2, $var3, $var4], ['integer', 'string', 'boolean', 'float']);
Types can be sent in an array in the additional (optional) parameter. This is some partial solution and it’s not bad - actually, this is quite a powerful mechanism because you can define your custom data types, which might come in handy for some special cases, for example in databases that support less common data types. However, in most cases custom data types are not used so why not infer the type from the php type by default?
I see the problem - but I think it doesn’t have anything to do with this topic particularly.
I decided to give it a go and try how the idea of automatic types will work in a userland implementation. I am currently using Doctrine DBAL but the same concept equally applies to PDO as DBAL’s API is very similar to PDO with some enhancements.
The problem:
$rows = $db->fetchAll("SELECT * FROM t WHERE col1=? AND col2=? AND col3=?", [$params]);
// these are DBAL utility methods not available in native PDO:
$db->insert('t', $data); // insert $data to table t
$db->update('t', $data, ['id' => $id]); // update table t with $data where id=$id
This usage is very simple and convenient - $params and $data are sent as strings to the database and most of the time all is fine. But one day I decide to add one more element to $params or to $data and this element is a boolean or an integer that really must be sent as a number for various reasons. And then the whole thing blows up and I now I have to change my workflow and send each value with bindValue() or (in DBAL) create a separate array with all data types - quite annoying, I’d say.
Therefore this is the solution for DBAL, a wrapper class for the DBAL Connection object:
namespace Lib;
/**
* This is a wrapper around DBAL Connection class. It adds automatic conversion
* of PHP types to database (PDO) types so that in most cases there is no need
* to specify $types argument for most common data types like strings, integers
* and booleans if the passed values are in correct PHP types.
*/
class Connection extends \Doctrine\DBAL\Connection {
public function executeCacheQuery($query, $params, $types, \Doctrine\DBAL\Cache\QueryCacheProfile $qcp) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::executeCacheQuery($query, $params, $types, $qcp);
}
public function executeQuery($query, array $params = array(), $types = array(), \Doctrine\DBAL\Cache\QueryCacheProfile $qcp = null) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::executeQuery($query, $params, $types, $qcp);
}
public function executeUpdate($query, array $params = array(), array $types = array()) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::executeUpdate($query, $params, $types);
}
public function fetchAll($sql, array $params = array(), $types = array()) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::fetchAll($sql, $params, $types);
}
public function fetchArray($statement, array $params = array(), array $types = array()) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::fetchArray($statement, $params, $types);
}
public function fetchAssoc($statement, array $params = array(), array $types = array()) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::fetchAssoc($statement, $params, $types);
}
public function fetchColumn($statement, array $params = array(), $column = 0, array $types = array()) {
if ($params && !$types) {
$types = $this->getTypesFromValues($params);
}
return parent::fetchColumn($statement, $params, $column, $types);
}
public function insert($tableExpression, array $data, array $types = array()) {
if (!$types) {
$types = $this->getTypesFromValues($data);
}
return parent::insert($tableExpression, $data, $types);
}
public function update($tableExpression, array $data, array $identifier, array $types = array()) {
if (!$types) {
$types = $this->getTypesFromValues(array_merge($data, $identifier));
}
return parent::update($tableExpression, $data, $identifier, $types);
}
/**
* Infer database types based on PHP types of given values.
*
* @param array $values
* @return array Types
*/
protected function getTypesFromValues(array $values) {
$types = [];
foreach ($values as $v) {
if (is_int($v)) {
$types[] = \PDO::PARAM_INT;
} elseif (is_bool($v)) {
$types[] = \PDO::PARAM_BOOL;
} else {
$types[] = \PDO::PARAM_STR;
}
}
return $types;
}
}
I have overridden all methods that accept an array of values to be passed to SQL and if the types are not explicitly sent then my method getTypesFromValues() decides on the SQL types based on the PHP types of the values. And when configuring DBAL connection it’s enough to pass the name of the wrapper class as an additional connection parameter and the wrapper will be used automaticaly:
'wrapperClass' => 'Lib\Connection'
Of course, a similar wrapper could be made for plain PDO. I’m just not sure how PARAM_BOOL will work for various databases (my example is for postgresql), however, it shouldn’t be a big problem because this can be adjusted in one place if necessary when switching to a different database.
So far so good, this solution seems to work well in a project I’m developing now and I think I’ll keep using it - I don’t have to worry about tedious type passing except some extremely rare cases where there is a need for an non-standard data type. I still don’t know why PDO doesn’t do this natively but well, this is not a perfect library, anyway…
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.