How to check in PHP if more PDO parameters provided than needed

Question

I have following piece of code (PHP 7.3.16) which retrieves some data from database (MySQL):

// db credentials
$dbhost = "localhost";
$dbuser = "user";
$dbpass = "password";
$dbname = "database";

// pdo
$dsn = "mysql:host=".$dbhost.";dbname=".$dbname.";charset=utf8";
$options = [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES => false
];
$pdo = new PDO($dsn, $dbuser, $dbpass, $options);

// run sql query
$sql = "SELECT * FROM foo WHERE bar > ?";

$pdo_parameters = [ 1 ];
// this will produce no results without any error:
// $pdo_parameters = [ 1, 2, 3 ];

$stmt = $pdo->prepare($sql);
$stmt->execute( $pdo_parameters );

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    var_dump($row);
}

If array $pdo_parameters contains only 1 element then everything works as expected and I receive a result. However when $pdo_parameters contains more than one I don't receive any results at all without any exception message. PHP errors are active, but I don't see any PDO error message. Why?

I would like to raise an PHP exception if there are too many parameters. Is that possible?

Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number in /tmp/test:42 Stack trace: #0 /tmp/test.php(42): PDOStatement->execute(Array) #1 {main} thrown in /tmp/test.php on line 42

I have a huge code base and need in such cases a proper exception handling. However I don't understand why PHP doesn't raise an exception in that case. Is this just another PHP bug or if there is just no way for PHP (PHP C internals) to check if there are too many PDO parameters? I know how to check that by myself in plain PHP (could be pretty inaccurate though if we have "?" in some strings), but it would be better if PHP PDO extension would have a function to check such cases.

EDIT: It is a known PHP bug: https://bugs.php.net/bug.php?id=77490 (first report from 20 January 2019)

btw this solution: https://stackoverflow.com/a/1376838 is buggy — Awaaaaarghhh, May 05 '20 at 16:25
That's a good question. Sometimes PDO complains about extra parameters and sometimes don't. But I don't know exactly how to switch it on. Can you try a named parameter? — Your Common Sense, May 05 '20 at 16:34
@YourCommonSense yes: https://www.php.net/manual/en/pdostatement.bindparam.php is about using named parameters — Awaaaaarghhh, May 05 '20 at 16:54
This page is about using bindParam, that can be used with **either** named or positional placeholders. And exactly opposite to your approach of using an array with execute — Your Common Sense, May 05 '20 at 16:55
@YourCommonSense yeah, you're right, now i see it too. Yes that's indeed a different approach. Now edited my question. — Awaaaaarghhh, May 05 '20 at 16:59
https://stackoverflow.com/questions/13491802/pdo-passing-extra-parameters-to-a-prepared-statment-than-needed — Awaaaaarghhh, May 05 '20 at 21:05
php bug tracker: https://bugs.php.net/bug.php?id=79568 (05.05.2020) and https://bugs.php.net/bug.php?id=77490 (20.01.2019) — Awaaaaarghhh, May 06 '20 at 19:02

Álvaro González · Accepted Answer · 2020-05-07T06:18:35.343

4

You're right, that situation doesn't seem to throw exceptions nor trigger errors. PDOStatement::execute() at least returns false so you can roll your own:

$dsn = "mysql:host=$dbhost;dbname=$dbname;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $dbuser, $dbpass, $options);

$sql = 'SELECT ? AS foo';
$stmt = $pdo->prepare($sql);

if (!$stmt->execute([1, 2])) {
    throw new InvalidArgumentException('Failed to execute statement');
}
while ($row = $stmt->fetch()) {
    var_dump($row);
}

Not ideal but...

edited May 07 '20 at 06:18

answered May 05 '20 at 16:37

Álvaro González

142,137
41
261
360

I hope that `$stmt->execute([1, 2]` works as expected and there are no additional issues here too. – Awaaaaarghhh May 05 '20 at 16:43
PDO is in general quite pleasant to use but it's full of rough edges like this. – Álvaro González May 05 '20 at 16:47

score -1 · Answer 2 · answered May 05 '20 at 16:24

-1

To check what param were provided use this: https://www.php.net/manual/en/pdostatement.debugdumpparams.php,
Also use param bindings to be absolutely sure that you have bind the params the right way: https://www.php.net/manual/en/pdostatement.bindparam.php

answered May 05 '20 at 16:24

Marco Edoardo Duma

71
1
1
6

could you explain why `debugDumpParams` is useful in that case. PHP manual tries to describe something esoteric to me and I don't understand why is tht function even useful in that example. ( https://www.php.net/manual/en/pdostatement.debugdumpparams.php ) – Awaaaaarghhh May 05 '20 at 16:28
Yeah straight away, I'll post a link to pastebin to show you how everything should have been done :) – Marco Edoardo Duma May 05 '20 at 16:31
First thing first, I think is showing the empty array only because what you should have done was print_r($row, true); – Marco Edoardo Duma May 05 '20 at 16:33
it seems to work, I receive an error `Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined` if I bind too many parameters. However I would like to use "?" instead of named parameters. Otherwise I will have to rewrite tons of code lines ... – Awaaaaarghhh May 05 '20 at 16:35
Sorry but, why don't use "SELECT * FROM foo WHERE bar IN ('.$params.')" and $params = implode(",",$pdo_parameters); – Marco Edoardo Duma May 05 '20 at 16:43
If it was helpful, can you accept it as the right answer? – Marco Edoardo Duma May 05 '20 at 16:46
This is not helpful. You seems to be completely misunderstood the question. – Your Common Sense May 05 '20 at 16:47
:( stackoverflow is way meanier than I thought – Marco Edoardo Duma May 05 '20 at 16:48
Also nobody thought that SELECT * FROM foo WHERE bar > *an array* doesn't make sense? @YourCommonSense – Marco Edoardo Duma May 05 '20 at 16:50
you answer is okay, but your suggestion would imply that i'll have to rewrite many many lines of code and that's not practical and not ideal. Imho that's speicific edge case is a bug in PDO or just a missing hint on PHP PDO man page, I would open a bug request on PHP bug tracker later. – Awaaaaarghhh May 05 '20 at 16:53
No man, it's not a bug. I didn't see it before, but you have simply not thought that you are trying to make a wrong sql statement. SELECT * FROM SOMETHING IN (just put here the imploded array) and I guarantee it's gonna work – Marco Edoardo Duma May 05 '20 at 16:56
2

`WHERE bar > ?` is just an **example**. It can be any other query. The question is not about this query **at all** – Your Common Sense May 05 '20 at 16:59
The issue isn't about trying to select something where the value is **in** an array, it's about the fact the array contains more parameters than placeholders. – iainn May 05 '20 at 16:59
Thanks for explaining that @iainn, I really didn't got the real problem. Anyway : https://stackoverflow.com/questions/27461763/phps-pdo-prepared-statement-am-i-able-to-use-one-placeholder-multiple-times – Marco Edoardo Duma May 05 '20 at 17:08
:( my common sense is tingling – Marco Edoardo Duma May 05 '20 at 17:20
@YourCommonSense anyway I've start to read your answers of other questions, damn I'm starting to love you – Marco Edoardo Duma May 05 '20 at 17:28

How to check in PHP if more PDO parameters provided than needed

2 Answers2

Linked