6

Can you send more parameters than needed to a prepared statement using PDO with no undesired side effects?

That mights seem like a strange question but I ask because I have 4 queries in a row which all use similar and different parameters. The relevant parts of the queries:

1st (select, different table to others):
WHERE threadID = :tid

2nd (select):
WHERE user_ID = :u_ID AND thread_ID = :tid

3rd (update if 2nd was successful):
SET time = :current_time WHERE user_ID = :u_ID AND thread_ID = :tid

4th (insert if 2nd was unsuccessful):
VALUES (:u_ID, :tid, :current_time)

Can I declare one array with the three parameters at the beginning and use it for all 4 queries?

To sort out any confusion, the queries would be executed seperately. It is the parameters variable being reused and so that would mean some queries would receive parameters they don't need. So something like:

$parameters = array(':tid' => $tid, ':u_ID' => $u_ID, ':current_time' => $time);

$1st = $db->prepare($query1);
$1st->execute($parameters);

$2nd = $db->prepare($query2);
$2nd->execute($parameters);

$3rd = $db->prepare($query3);
$3rd->execute($parameters);

$4th = $db->prepare($query4);
$4th->execute($parameters);

If I can, should I? Will this slow down or cause security flaws to my database or scripts?

If I can make this question a bit clearer, please ask.

Thank you!

joe92
  • 635
  • 2
  • 11
  • 19
  • 1
    I don't see there being a problem with that. The parameters is a simply an array being used more than once.. The array is never change by the pdo class so all should be fine. –  Nov 21 '12 at 18:15
  • https://stackoverflow.com/questions/61617834/how-to-check-in-php-if-more-pdo-parameters-provided-than-needed – Awaaaaarghhh May 05 '20 at 21:05

4 Answers4

5

Perhaps the documentation has been updated since this question was first asked, but now it is quite clearly stated "No"

You cannot bind more values than specified; 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.

These answers should be useful in filtering out the extra parameters.

Community
  • 1
  • 1
Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
3

I know this is already answered and it's only asking about whether you can send extra params, but I thought people might arrive at this question, and want to know how to get around this limitation. Here's the solution I use:

$parameters = array('tid' => $tid, 'u_ID' => $u_ID, 'current_time' => $time);

$1st = $db->prepare($query1);
$1st->execute(array_intersect_key($parameters, array_flip(array('tid'))));

$2nd = $db->prepare($query2);
$2nd->execute(array_intersect_key($parameters, array_flip(array('u_ID', 'tid'))));

$3rd = $db->prepare($query3);
$3rd->execute(array_intersect_key($parameters, array_flip(array('u_ID', 'tid', 'current_time'))));

$4th = $db->prepare($query4);
$4th->execute(array_intersect_key($parameters, array_flip(array('u_ID', 'tid', 'current_time'))));

That array_interset_key and array_flip maneuver could be extracted to its own function, like:

function filter_fields($params,$field_names) {
    return array_intersect_key($params, array_flip($field_names))
}

I just haven't got around to it yet.

The function flips your array of key names, so you have an array with no values, but the right keys. Then intersect filters the first array so you only have the keys that are in both arrays (in this case, only the ones in your array_flipped array). But you get the values for the original array (not the empties). So you make one array of parameters, but specify which params are actually sent to PDO.

So, with the function, you'd do:

$parameters = array('tid' => $tid, 'u_ID' => $u_ID, 'current_time' => $time);

$1st = $db->prepare($query1);
$1st->execute(filter_fields($parameters, array('tid')));

$2nd = $db->prepare($query2);
$2nd->execute(filter_fields($parameters, array('u_ID', 'tid')));

$3rd = $db->prepare($query3);
$3rd->execute(filter_fields($parameters, array('u_ID', 'tid', 'current_time')));

$4th = $db->prepare($query4);
$4th->execute(filter_fields($parameters, array('u_ID', 'tid', 'current_time')));

If you have PHP 5.4, you can use the square bracket array syntax, to make it even cooler:

$parameters = array('tid' => $tid, 'u_ID' => $u_ID, 'current_time' => $time);

$1st = $db->prepare($query1);
$1st->execute(filter_fields($parameters, ['tid']));

$2nd = $db->prepare($query2);
$2nd->execute(filter_fields($parameters, ['u_ID', 'tid']));

$3rd = $db->prepare($query3);
$3rd->execute(filter_fields($parameters, ['u_ID', 'tid', 'current_time']));

$4th = $db->prepare($query4);
$4th->execute(filter_fields($parameters, ['u_ID', 'tid', 'current_time']));
eimajenthat
  • 1,338
  • 3
  • 15
  • 33
2

I got a chance to test my question, and the answer is you cannot send more parameters than the query uses. You get the following error:

PDOException Object
(
    [message:protected] => SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    [string:Exception:private] => 
    [code:protected] => HY093
    [file:protected] => C:\Destination\to\file.php
    [line:protected] => line number
    [trace:Exception:private] => Array
        (
            [0] => Array
                (
                    [file] => C:\Destination\to\file.php
                    [line] => line number
                    [function] => execute
                    [class] => PDOStatement
                    [type] => ->
                    [args] => Array
                        (
                            [0] => Array
                                (
                                    [:u_ID] => 1
                                    [:tid] => 1
                                    [:current_time] => 1353524522
                                )

                        )

                )

            [1] => Array
                (
                    [file] => C:\Destination\to\file.php
                    [line] => line number
                    [function] => function name
                    [class] => class name
                    [type] => ->
                    [args] => Array
                        (
                            [0] => SELECT
                                                column
                                            FROM
                                                table
                                            WHERE
                                                user_ID  = :u_ID AND
                                                thread_ID = :tid
                            [1] => Array
                                (
                                    [:u_ID] => 1
                                    [:tid] => 1
                                    [:current_time] => 1353524522
                                )

                        )

                )

        )

    [previous:Exception:private] => 
    [errorInfo] => Array
        (
            [0] => HY093
            [1] => 0
        )

)

I don't know a huge amount about PDO, hence my question, but I think that because :current_time is sent but not used and the error message is "Invalid parameter number: parameter was not defined" you cannot send extra parameters which are not used.

Additionally the error code HY093 is generated. Now I can't seem to find any documentation explaining PDO codes anywhere, however I came across the following two links specifically about HY093:
What is PDO Error HY093
SQLSTATE[HY093]

It seems HY093 is generated when you incorrectly bind parameters. This must be happening here because I am binding too many parameters.

Community
  • 1
  • 1
joe92
  • 635
  • 2
  • 11
  • 19
  • This is a really old post, but I'll comment my own findings about PDO, especially the parameters. You **cannot** reuse the same parameter name twice. In the above `:current_time` will be used twice (once in array[0] and again in array[1]). Each of them must have a unique name, eg. `:current_time` in array[0] and for example `:current_time2` in array[1]. The same with `:u_ID` and `:tid`. – Juha Untinen May 27 '16 at 08:36
  • Resurrecting an old thread here, just wanted to note that if EMULATE_PREPARES is off, execute() returns false without throwing an exception if the arguments don't match the parameter count (as of PHP 5.2+) – y o Aug 12 '16 at 10:15
0

executing different type of multiple queries with one execute leads to problems. you can run multiple selects or multiple updates with one execute. For this case to create different prepared statements objects and pass the the parameters accordingly. 

// for WHERE threadID = :tid
$st1 = $db->prepare($sql);
$st1->bindParam(':tid', $tid);
$st1->execute();
or
$st1->execute(array(':tid'=>$tid);

// for WHERE user_ID = :u_ID AND thread_ID = :tid
$st2 = $db->prepare($sql);
$st2->bindParam(':u_ID', $u_ID);
$st2->bindParam(':tid', $tid);
$st2->execute();
or
$st2->execute(array(':tid'=>$tid, ':u_ID' => $u_ID);

// for SET time = :current_time WHERE user_ID = :u_ID AND thread_ID = :tid
$st3 = $db->prepare($sql);
$st3->bindParam(':u_ID', $u_ID);
$st3->bindParam(':tid', $tid);
$st3->bindParam(':current_time', $current_time);
$st3->execute();
or
$st3->execute(array(':tid'=>$tid, ':u_ID' => $u_ID, ':current_time' => $current_time);

// for VALUES (:u_ID, :tid, :current_time)
$st4 = $db->prepare($sql);
$st4->bindParam(':u_ID', $u_ID);
$st4->bindParam(':tid', $tid);
$st4->bindParam(':current_time', $current_time);
$st4->execute();
or
$st4->execute(array(':tid'=>$tid, ':u_ID' => $u_ID, ':current_time' => $current_time);
dkkumargoyal
  • 556
  • 3
  • 10
  • I don't mean to use the same execute. I have revised my question to hopefully explain it a bit better. What I mean is to execute seperately but use the same parameters variable each time. Thus some queries would receive more parameters than they need. – joe92 Nov 21 '12 at 18:05