PHP无法解释的错误，无效的参数编号：绑定变量的数量与令牌的数量不匹配

Question

I can't find a mistake in my code, and I always get the following error:

exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' "

when trying to submit some inputs from a form.

if (isset($_GET['createNewBox'])) {

  if (!empty($_POST['tableName']) and !empty($_POST['commentFullAddress'])) {

    try{

        $sql = 'CREATE TABLE :tableName (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            customerid INT,
            item TEXT,
            pin INT(11) NOT NULL,
            position VARCHAR(5),
            storedate DATE NOT NULL,
            storetime TIME NOT NULL
            ) DEFAULT CHARACTER SET utf8 ENGINE=INNODB COMMENT=":commentFullAddress"';
        $statement = $pdo -> prepare($sql);
        $statement -> bindValue(':tableName', $_POST['tableName']);
        $statement -> bindValue(':commentFullAddress', $_POST['commentFullAddress']);

        if ($statement -> execute()) {

            session_start();
            $_SESSION['messageSucceed'] = "A new database has been created for the box.";
            header('Location: /?managebox');
            exit();
        }   

    } catch (PDOException $e) {

        $error_output = "Error on creating new box database: " . $e;
        include '../error.html.php';
        exit();
    }

  } else {

    session_start();
    $_SESSION['message'] = "Please do not submit empty data.";
    header("Location: /?managebox");
  }
}

Answer 1

There are 2 things wrong with your code.

Firstly, this:

CREATE TABLE :tableName

You can't bind a table in PDO, so you need to either use a variable or from a safelist.

Then you're using quotes around the values for the binds COMMENT=":commentFullAddress"'; and those need to be removed.

Sidenote: TBH, I don't know why you're using a prepared statement for the COMMENT, I've never seen that before.

References:

• When to use single quotes, double quotes, and backticks in MySQL

• Can PHP PDO Statements accept the table or column name as parameter?

• http://php.net/pdo.prepared-statements

Plus, make sure those POST arrays contain values.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

You also may have to change bindValue to bindParam, I said "may".

---

Footnotes:

I don't understand why you're using this code to create a table, yet alone coming from user input. That's your decision but I don't see the reason for it, unless you're trying to create some form of database hosting service.