PHP bound variables, Invalid parameter number error or PDO Update Statement

Question

I built a update statement in PHP using PDO, however I keep getting this error.

ERROR: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I have counted and re-counted (25) and spent a while making sure my syntax is correct and I can't understand what I am doing wrong.

Here is the code:

$sql = "UPDATE :tablename
        SET entryName = :entryname,
        entryAdd1 = : add1,
        entryAdd2 = :add2,
        entryCity = :city,
        entryCounty = :county,
        entryCountry = :country,
        entryPC = :postcode,
        entryPhone = :phone,
        entryEmail = :email,
        entryURL = :entryurl,
        entryDesc = :entryDesc,
        entryImg = :entryimg,
        entryStar  = :star,
        entryCuststar = :custstar,
        chains = :chains,
        cafe = :cafe,
        fishandchips = :fishandchips,
        indian = :indian,
        itallian = :italian,
        pizzeria = :pizzeria,
        tapas = :tapas,
        tearooms = :tearooms
        WHERE :colname = :entryid";

        $statement = $conn->prepare($sql);

        $statement->bindParam(":tablename", $tablename, PDO::PARAM_STR);

        $statement->bindParam(":entryname", $entryname, PDO::PARAM_STR);
        $statement->bindParam(":add1", $add1, PDO::PARAM_STR);
        $statement->bindParam(":add2", $add2, PDO::PARAM_STR);
        $statement->bindParam(":city", $entrytown, PDO::PARAM_STR);
        $statement->bindParam(":county", $entrycounty, PDO::PARAM_STR);
        $statement->bindParam(":country", $entrycountry, PDO::PARAM_STR);
        $statement->bindParam(":postcode", $entrypostcode, PDO::PARAM_STR);
        $statement->bindParam(":phone", $entryphone, PDO::PARAM_STR);
        $statement->bindParam(":email", $entryemail, PDO::PARAM_STR);
        $statement->bindParam(":entryurl", $entryurl, PDO::PARAM_STR);
        $statement->bindParam(":entryDesc", $entrydesc, PDO::PARAM_STR);
        $statement->bindParam(":entryimg", $entryicon, PDO::PARAM_STR);
        $statement->bindParam(":star", $star, PDO::PARAM_STR);
        $statement->bindParam(":custstar", $custstar, PDO::PARAM_STR);
        $statement->bindParam(":chains", $chains, PDO::PARAM_INT);
        $statement->bindParam(":cafe", $cafe, PDO::PARAM_INT);
        $statement->bindParam(":fishandchips", $fishchips, PDO::PARAM_INT);
        $statement->bindParam(":indian", $indian, PDO::PARAM_INT);
        $statement->bindParam(":italian", $italian, PDO::PARAM_INT);
        $statement->bindParam(":pizzeria", $pizzeria, PDO::PARAM_INT);
        $statement->bindParam(":tapas", $tapas, PDO::PARAM_INT);
        $statement->bindParam(":tearooms", $tearooms, PDO::PARAM_INT);

        $statement->bindParam(":colname", $colname, PDO::PARAM_STR);
        $statement->bindParam(":entryid", $entryid, PDO::PARAM_INT);

        $count = $statement->execute();

I state the $conn variable further up, it's just a practice directory I was making. If anyone could point me in the right direction I would appreciate it.

you can't parameterize table/column names the resultant query would be UPDATE 'table_name' ... quotes included! — andrew
– andrew, Commented Dec 8, 2014 at 16:24
and i do not know, is it matter, but you have an extra space here entryAdd1 = : add1, — vaso123
– vaso123, Commented Dec 8, 2014 at 16:25
@andrew if I hard-code the table name and column name I end up with the same error, I am not disputing you are right, just that it appears to make no difference to this error. But thank you for explaining that, I had just done something similar before. — AltTab
– AltTab, Commented Dec 8, 2014 at 16:30
I think @lolka_bolka has spotted why the number of params dont match — andrew
– andrew, Commented Dec 8, 2014 at 16:31
When you hardcoded the table name and column name, did you make sure to remove those two bindParam statements? — versalle88
– versalle88, Commented Dec 8, 2014 at 16:33

Jordy · Accepted Answer · 2014-12-08 16:29:31Z

1

Table and Column names cannot be replaced by parameters in PDO. You'd have to replace these with the actual names, offcourse you can filter out unwanted data if you wanted to, but not by using the prepared statements.

Won't work:

$sth = $dbh->prepare('SELECT name, colour, calories FROM ?  WHERE calories < ?');

THIS WORKS!

$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ?');

answered Dec 8, 2014 at 16:29

Jordy

1,0692 gold badges11 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VolkerK Over a year ago

Just for clarification: that relates to the UPDATE :tablename and the WHERE :colname part of the query.

versalle88 · Accepted Answer · 2014-12-08 16:42:49Z

You can't parameterize table/column names. Try this if you need them to be dynamic:

$sql = "UPDATE " . $tablename . "
    SET entryName   = :entryname,
    entryAdd1       = :add1,
    entryAdd2       = :add2,
    entryCity       = :city,
    entryCounty     = :county,
    entryCountry    = :country,
    entryPC         = :postcode,
    entryPhone      = :phone,
    entryEmail      = :email,
    entryURL        = :entryurl,
    entryDesc       = :entryDesc,
    entryImg        = :entryimg,
    entryStar       = :star,
    entryCuststar   = :custstar,
    chains          = :chains,
    cafe            = :cafe,
    fishandchips    = :fishandchips,
    indian          = :indian,
    itallian        = :italian,
    pizzeria        = :pizzeria,
    tapas           = :tapas,
    tearooms        = :tearooms
    WHERE " . $colname . " = :entryid";

    $statement = $conn->prepare($sql);

    $statement->bindParam(":entryname", $entryname, PDO::PARAM_STR);
    $statement->bindParam(":add1", $add1, PDO::PARAM_STR);
    $statement->bindParam(":add2", $add2, PDO::PARAM_STR);
    $statement->bindParam(":city", $entrytown, PDO::PARAM_STR);
    $statement->bindParam(":county", $entrycounty, PDO::PARAM_STR);
    $statement->bindParam(":country", $entrycountry, PDO::PARAM_STR);
    $statement->bindParam(":postcode", $entrypostcode, PDO::PARAM_STR);
    $statement->bindParam(":phone", $entryphone, PDO::PARAM_STR);
    $statement->bindParam(":email", $entryemail, PDO::PARAM_STR);
    $statement->bindParam(":entryurl", $entryurl, PDO::PARAM_STR);
    $statement->bindParam(":entryDesc", $entrydesc, PDO::PARAM_STR);
    $statement->bindParam(":entryimg", $entryicon, PDO::PARAM_STR);
    $statement->bindParam(":star", $star, PDO::PARAM_STR);
    $statement->bindParam(":custstar", $custstar, PDO::PARAM_STR);
    $statement->bindParam(":chains", $chains, PDO::PARAM_INT);
    $statement->bindParam(":cafe", $cafe, PDO::PARAM_INT);
    $statement->bindParam(":fishandchips", $fishchips, PDO::PARAM_INT);
    $statement->bindParam(":indian", $indian, PDO::PARAM_INT);
    $statement->bindParam(":italian", $italian, PDO::PARAM_INT);
    $statement->bindParam(":pizzeria", $pizzeria, PDO::PARAM_INT);
    $statement->bindParam(":tapas", $tapas, PDO::PARAM_INT);
    $statement->bindParam(":tearooms", $tearooms, PDO::PARAM_INT);

    $statement->bindParam(":entryid", $entryid, PDO::PARAM_INT);

    $count = $statement->execute();

Thanks,

Andrew

be careful of where $tablename is coming from, you don't want to introduce injection vulnerabilities

Collectives™ on Stack Overflow

PHP bound variables, Invalid parameter number error or PDO Update Statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related