Can't drop a table with bindParam using PDO and PHP

Question

For example this works fine:

$dropTable = $dbConnection->prepare('DROP TABLE IF EXISTS announcements');
$dropTable->execute();

$createTable = $dbConnection->prepare('CREATE TABLE announcements(
            id MEDIUMINT NOT NULL AUTO_INCREMENT,
            announcements TEXT NOT NULL,
            PRIMARY KEY (id))');
$createTable->execute();

But this fails:

$dropTable = $dbConnection->prepare('DROP TABLE IF EXISTS :tableToDrop');
$dropTable->bindParam(':tableToDrop', $_GET['table']);
$dropTable->execute();

$createTable = $dbConnection->prepare('CREATE TABLE announcements(
        id MEDIUMINT NOT NULL AUTO_INCREMENT,
            announcements TEXT NOT NULL,
            PRIMARY KEY (id))');
$createTable->execute();

With Error:

Fatal error: Uncaught 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 '?' at line 1' in xxxx/createTables.php:9  
Stack trace: #0 xxxxx/createTables.php(9):   
  PDO->prepare('DROP TABLE IF E...') #1 {main} thrown in xxxx/createTables.php on line 9

I'm sure it's something trivial, but I've been going at it for hours. Cheers.

Edit: Turns out you can't bindParam with a table name.Is there anyway to do a secure prepared statement with a dynamic table name?

There's only a handful of places where you can use a placeholder, the table name is not one of them. I don't have a link to reference, that's why it's just a comment. — Maerlyn
– Maerlyn, Commented Jan 27, 2013 at 22:41
@Maerlyn Oh, that may be right. I think I saw reference to that — Adam Ashwal
– Adam Ashwal, Commented Jan 27, 2013 at 22:44

Itay Moav -Malimovka · Accepted Answer · 2013-01-27 23:35:30Z

1

Safe way (and u should use that, event if what u wrote could work):

$t=array('t1'=>'t1','t2'=>'t2'....'tn'=>'tn');

$sql = "drop table {$t[$_GET['table']]} ..."

answered Jan 27, 2013 at 23:35

Itay Moav -Malimovka

53.8k68 gold badges198 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Ashwal Over a year ago

Cheers mate, never would have thought of that.

Collectives™ on Stack Overflow

Can't drop a table with bindParam using PDO and PHP

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related