DarkSpirit DarkSpirit - 7 months ago 17
PHP Question

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

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

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.