MySQLi Prepared Statement Number of variables doesn't match number of parameters

Question

I'm writing what I think is a fairly basic prepared statment using MySQLi. Statement follows (in which $connection is the MySQLi instance):

    $venue = $_POST['venue'];
    $description = $_POST['description'];

    if (isset($_POST['add'])) {
       $query = $connection->prepare("INSERT INTO events (date, time, showTime, venue, description) VALUES ('$date', '$time', '$showtime', '?', '?')");
       $query->bind_param('ss', $venue, $description);
    }
    elseif (isset($_POST['edit'])) {
       $query = $connection->prepare("UPDATE events SET date = '$date', time = '$time', showTime = '$showtime', venue = '?', description = '?' WHERE id ='$id'");
       $query->bind_param('ss', $venue, $description);
    }

    $query->execute();

This fails with the following in the log: Number of variables doesn't match the number of parameters in a prepared statement.

I'm not a genius at math, but there are two placeholders in the statement and two variables in the bind_param :) I've also confirmed that the types are correct: if I var_dump $venue and $description, I get strings. Can someone help me see where my mistake is?

Thanks!

why are you preparing statements but also interpolating strings? That's likely to open you to sql injection vulnerabilities. In both `prepare()`s, you have 2 question marks but provide 3 bind parameters. Could be wrong, but don't think bound parameters' question mark placholders ever need to be quoted. Cheers! — erik258, Oct 08 '19 at 03:07

score 1 · Accepted Answer · answered Oct 08 '19 at 06:11

Without going into much details, please do not mix 'direct values' with value holders (?) in your query. Instead, bind them all at once. Try the following:

$query = $connection->prepare("INSERT INTO events (date, time, showTime, venue, description) VALUES (?, ?, ?, ?, ?)");
$query->bind_param('sssss', $date, $time, $showtime, $venue, $description);

MySQLi Prepared Statement Number of variables doesn't match number of parameters

1 Answers1