Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

NOTE: This article is 3 years or older so its information may no longer be relevant. Read on at your own discretion! Comments for this article have automatically been locked, refer to the FAQ for more details.
As I expand the data binding layer for my travelblog.ws project, I keep coming across different ways of breaking the PHP PDO statement class. The recent peculiarity that I've come across was to do with binding more variables than there were bind parameters in the SQL statement. In this situation, PDO will not return any data. In fact this is documented in the API...
Binding more values than specified is not possible; if more keys exist in input_parameters than in the SQL specified in the PDO::prepare(), then the statement will fail and an error is emitted.

Lets see the code that would cause the above behaviour. First there was some boiler plate code to write to execute a simple SQL statement (getting an ID from a 'posts' table for data that I know exists). There was a single bind parameter, ':id', in the SQL statement.
$binds = ...; /* see specific examples below */
$sql = 'SELECT id FROM posts WHERE id = :id';
$statement = $dbConn->prepare($sql);
$results = $statement->execute($binds);
$data = $statement->fetchAll(\PDO::FETCH_OBJ);

when the $binds variable was set as follows:
$binds = array('id' => '0f841cb12dc75');

I was getting data back as expected...

However if I were to add some additional (non-sensical) bind parameter/value to the $binds array like this...
$binds = array('id' => '0f841cb12dc75', 'a' => 'b');

Instead of ignoring the bind parameter that does not appear in the SQL statement, PDO instead returned no data.

Additionally the following warning was emitted to the PHP error log:
PHP Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in dbtest.php on line 33

The above all made sense, though I was half-expecting that PDO would simply ignore any extra bind parameters.


Skip down to comments...
Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...