use heredoc to input sql and when bind var ,get error

Question

i write this code for query

    $tableQuery_comment = <<<query
                            CREATE TABLE IF NOT EXISTS `?comment` (
                              `cmt_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                              `cmt_obj_id` bigint(20) NOT NULL DEFAULT '0',
                              `cmt_author_name` tinytext NOT NULL,
                              `cmt_author_email` varchar(100) NOT NULL DEFAULT '',
                              `cmt_author_url` varchar(200) NOT NULL DEFAULT '',
                              `cmt_author_ip` varchar(100) NOT NULL DEFAULT '',
                              `cmt_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                              `cmt_content` text NOT NULL,
                              `cmt_approve` varchar(20) NOT NULL DEFAULT 'yes',
                              `cmt_agent` varchar(255) NOT NULL DEFAULT '',
                              `cmt_parent` bigint(20) NOT NULL DEFAULT '0',
                              `user_id` bigint(20) NOT NULL DEFAULT '0',
                              PRIMARY KEY (`cmt_id`),
                              KEY `cmt_obj_id` (`cmt_obj_id`),
                              KEY `cmt_parent` (`cmt_parent`),
                              KEY `user_id` (`user_id`)
                            )
query;

and use mysqli to connect and execute my query with bind a value in first line of my query

$con = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
        $exe = $con->prepare($tableQuery_comment);
        $exe->bind_param(1, $tablePrefix);
        $exe->execute();

and get error in this line

$exe->bind_param(1, $tablePrefix);

Error :

mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

i try bind :name parameter like this code (when use this statement change ? sign to :tablePrefix)

$exe->bind_param(':tablePrefix', $tablePrefix);

and this code

$exe->bind_param('s', $tablePrefix);

but get and get again same error

what should i do to bind parameter in heredoc with mysqli?

`mysqli` doesn't support named parameters. You're confusing `mysqli` with `PDO`. — Barmar, Jan 27 '14 at 21:03
Also, you can't use parameter binding for table and column names. They can only be used in places where an expression is allowed. — Barmar, Jan 27 '14 at 21:05
in this [link](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) use mysqli and bind value — Death Programmer, Jan 27 '14 at 21:07
In the PDO example it uses `:name`, in the mysqli example it uses `?`, not `?name`. — Barmar, Jan 27 '14 at 21:08
sry for noob english. i writen install page of cms :), like wordpress and user can enter table prefix. so if user enter character like ' ` or any wrong character ,how correct this character? — Death Programmer, Jan 27 '14 at 21:19

score 2 · Accepted Answer · answered Jan 27 '14 at 21:13

You can't use parameters for table and column names, only where expressions are allowed. So you have to use string substitution for this:

    $tableQuery_comment = <<<query
                            CREATE TABLE IF NOT EXISTS `{$tablePrefix}comment` (
                              `cmt_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                              `cmt_obj_id` bigint(20) NOT NULL DEFAULT '0',
                              `cmt_author_name` tinytext NOT NULL,
                              `cmt_author_email` varchar(100) NOT NULL DEFAULT '',
                              `cmt_author_url` varchar(200) NOT NULL DEFAULT '',
                              `cmt_author_ip` varchar(100) NOT NULL DEFAULT '',
                              `cmt_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                              `cmt_content` text NOT NULL,
                              `cmt_approve` varchar(20) NOT NULL DEFAULT 'yes',
                              `cmt_agent` varchar(255) NOT NULL DEFAULT '',
                              `cmt_parent` bigint(20) NOT NULL DEFAULT '0',
                              `user_id` bigint(20) NOT NULL DEFAULT '0',
                              PRIMARY KEY (`cmt_id`),
                              KEY `cmt_obj_id` (`cmt_obj_id`),
                              KEY `cmt_parent` (`cmt_parent`),
                              KEY `user_id` (`user_id`)
                            )
query;

correct,but if user enter a letter like ` or ' or ... , i have inject query. right? — Death Programmer, Jan 27 '14 at 21:28
Yes. You can't use `bind_param` to prevent injection when you need to substitute into the table name. — Barmar, Jan 27 '14 at 21:31

use heredoc to input sql and when bind var ,get error

1 Answers1