PHP Unexplainable Error, Invalid parameter number: number of bound variables does not match number of tokens

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");
  }
}

Remove the quotes around `":commentFullAddress"`, otherwise it will denote the literal string `:commentFullAddress` instead of a parameter name. — Siguza, May 06 '16 at 10:51
PDO treat your values as its needed. `:commentFullAddress` will be wrapped with quotes automatically. If you wrap manually with quotes it will be treated as a literal string, as @Siguza said — Marcos Pérez Gude, May 06 '16 at 10:53
I have removed the quotes around the :commentFullAddress, but now, I got this error "Error on creating new box database: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''box3' ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, customerid INT' at line 1'" I put the quote around the :commentFullAddress because I thought the quote was needed to be there for the COMMENT in SQL. — DarkSpirit, May 06 '16 at 11:04

score 1 · Accepted Answer · edited May 23 '17 at 12:31

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:

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.

edited May 23 '17 at 12:31

Community

1
1

answered May 06 '16 at 11:17

Funk Forty Niner

74,450
15
68
141

I have made some changes according to your suggestion, but I still get the same error "number of bound variables does not match number of tokens". `code` $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";` `code` – DarkSpirit May 06 '16 at 11:34
@DarkSpirit My guess is that MySQL probably won't let you create a COMMENT from a prepared statement. TBH, I've never seen that before, so try removing it entirely. Plus, did you remove `$statement -> bindValue(':tableName', $_POST['tableName']);` ? – Funk Forty Niner May 06 '16 at 11:36
@DarkSpirit Plus, you are connecting with PDO right and not MySQLi_ or MySQL_? – Funk Forty Niner May 06 '16 at 11:40
I did forget to remove the bindValue(':tableName', $_POST['tableName']). My code is working fine now :) Yes, I am connecting with PDO. Thanks so much! – DarkSpirit May 06 '16 at 11:49
@DarkSpirit That's great, glad to hear it, *cheers and you're welcome!* – Funk Forty Niner May 06 '16 at 11:49

PHP Unexplainable Error, Invalid parameter number: number of bound variables does not match number of tokens

1 Answers1