PDO Prepared Statement doesn't create tables in MySQL as expected

Question

I have been spending 1hour+ looking at the code and I can't figure out why it doesn't work and am seeking advice rather than ripping my hair off.

I am just trying to write a php script to create some tables in MySQL.

The below code doesn't create any table at all. Why is it?

//prepared statement to execute for table creation
$query = "CREATE TABLE IF NOT EXISTS :table (:columns)";
$stmt = $dbh->prepare($query);
$stmt->bindParam(":table", $table, PDO::PARAM_STR);
$stmt->bindParam(":columns", $columns, PDO::PARAM_STR);

//creating the members table
$table = "members";
$columns = "user VARCHAR(20), password VARCHAR(20), INDEX(user(5))";

try {
  $stmt->execute();
} catch (Exception $ex) {
    echo "Exception: ".$ex->getMessage(); }
/*
$sql = "CREATE TABLE IF NOT EXISTS $table ($columns)";
$dbh->query($sql);*/
echo "Table $table now in database ". DB_NAME. "<br>";

When I comment out the prepared stmt and use a straight query, it works and table created, so there is nothing wrong with $dbh.

//prepared statement to execute for table creation
/* 
$query = "CREATE TABLE IF NOT EXISTS :table (:columns)";
$stmt = $dbh->prepare($query);
$stmt->bindParam(":table", $table, PDO::PARAM_STR);
$stmt->bindParam(":columns", $columns, PDO::PARAM_STR);
*/
//creating the members table
$table = "members";
$columns = "user VARCHAR(20), password VARCHAR(20), INDEX(user(5))";
/*
try {
  $stmt->execute();
} catch (Exception $ex) {
    echo "Exception: ".$ex->getMessage(); }
*/
$sql = "CREATE TABLE IF NOT EXISTS $table ($columns)";
$dbh->query($sql);
echo "Table $table now in database ". DB_NAME. "<br>";

Interesting to note that recently there are many people creating tables in their codes — Ken Lee, Jul 25 '21 at 08:25
Table and Column names CANNOT be replaced by parameters in PDO. https://stackoverflow.com/a/182353/1529324 — Andrea Olivato, Jul 25 '21 at 08:29
Don't understand the use of prepared statements when the values passed in are hardcoded literals that won't change and not user-generated input. ‍♂️ — Kim Hallberg, Jul 25 '21 at 08:29
Does this answer your question? [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) — Andrea Olivato, Jul 25 '21 at 08:29

score 1 · Answer 1 · answered Jul 25 '21 at 08:29

From: The excellent https://phpdelusions.net/pdo#bindable

Query parts you can bind

It is very important to understand which query parts you can bind using prepared statements and which you cannot. In fact, the list is overwhelmingly short: only string and numeric literals can be bound. So you can tell that as long as your data can be represented in the query as a numeric or a quoted string literal - it can be bound. For all other cases you cannot use PDO prepared statements at all: neither an identifier, or a comma-separated list, or a part of a quoted string literal or whatever else arbitrary query part cannot be bound using a prepared statement.

Workarounds for the most frequent use cases can be found in the corresponding part of the article

PDO Prepared Statement doesn't create tables in MySQL as expected

1 Answers1