Invalid parameter number given with pdo's prepared statement

Question

Hello I have made a query to select certain values from the database. Unfortunately I am getting this error message: Invalid parameter number

enter image description here

The code is as followes

$vehicles = DB::select( DB::raw("
        SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent 
        FROM vehicle as v 
        WHERE v.id 
        NOT IN((SELECT v.id FROM vehicle as v INNER JOIN reservation as r on r.`vehicle_id` = v.id WHERE r.status_id in(3,4,5) 
        AND (
                (
                    (:start >= r.startdate AND :eind <= r.enddate )
                    OR
                    (:start <= r.startdate AND :eind >= r.enddate ) 
                ) 
                OR
                (
                    (:start >= r.startdate AND :start <= r.enddate)
                    OR
                    (:eind >= r.startdate AND :eind <= r.enddate)
                )
            )
        GROUP BY v.id
        ))"), 
         array(
            'start' => $startdate, 
            'eind' => $enddate
         )
    );

Can one use the same parameter name multiple times? I guess that's your problem. — Marcel Korpel, Dec 18 '13 at 22:48
So how do I solve this? Because as u see I have to match them up against each other.. — Reshad, Dec 18 '13 at 22:48

score 1 · Accepted Answer · answered Dec 18 '13 at 22:53

You can't use the same parameter name multiple times. Give each parameter its own name and bind exactly that number of parameters:

$vehicles = DB::select( DB::raw("
        SELECT v.id, v.brand, v.type, v.description, v.airco, v.seats, v.hourly_rent 
        FROM vehicle as v 
        WHERE v.id 
        NOT IN((SELECT v.id FROM vehicle as v INNER JOIN reservation as r on r.`vehicle_id` = v.id WHERE r.status_id in(3,4,5) 
        AND (
                (
                    (:starta >= r.startdate AND :einda <= r.enddate )
                    OR
                    (:startb <= r.startdate AND :eindb >= r.enddate ) 
                ) 
                OR
                (
                    (:startc >= r.startdate AND :startd <= r.enddate)
                    OR
                    (:eindc >= r.startdate AND :eindd <= r.enddate)
                )
            )
        GROUP BY v.id
        ))"), 
         array(
            'starta' => $startdate, 
            'einda' => $enddate,
            'startb' => $startdate, 
            'eindb' => $enddate,
            'startc' => $startdate, 
            'eindc' => $enddate,
            'startd' => $startdate, 
            'eindd' => $enddate
         )
    );

Hi Marcel, do you know why this is so? Why can't binding names be used more than once? — James, Mar 04 '14 at 16:57

score 0 · Answer 2 · answered Dec 19 '13 at 06:42

0

You actually can. Just leave PDO in emulation mode.

answered Dec 19 '13 at 06:42

Your Common Sense

156,878
40
214
345

If you want maximum security, you don't want PDO to be in emulation mode, but using *real* prepared statements. – Marcel Korpel Dec 19 '13 at 14:51
There is no difference, security is equal. – Your Common Sense Dec 19 '13 at 20:33
Remember http://stackoverflow.com/a/12118602/258127? (I admit it's an edge case, but it *is* more dangerous to use PDO in emulation mode). – Marcel Korpel Dec 19 '13 at 21:15
there is not single problem with this case. – Your Common Sense Dec 19 '13 at 21:39

Invalid parameter number given with pdo's prepared statement

2 Answers2

Linked