binding variable for drop table php

Question

Im writing PHP scripts for using with my mySQL database. The only problem i have is binding variables for drop table/ create table and so on.

    $stmt = $link->prepare("DROP TABLE ?");
    $stmt->bind_param('s','testing'); 
    $stmt->execute();

is not working. I tried also:

SELECT * FROM (SELECT MAX(name) from profiles where name='testing') <- is working
DROP TABLE (SELECT MAX(name) from profiles where name='testing') <- dont work

Answer 1

Binding a parameter is not the same as just replacing a portion of the string : you cannot just bind anything you want.

In this case : you cannot use a bound parameter for a table name -- you'll have to use string concatenations to build your query, instead of using a prepared statement.

As a reference, quoting PREPARE Syntax :

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

Answer 2

As far as I know, you can only bind to a parameter, not to any part of a query you want. You're essentially telling the database "hey, I'm going to pass you a value here, and I want you to do your magic to make sure it doesn't overstep its bounds". Things like table names or field names aren't values, they're part of the table structure itself.

In this case, you'll have to just use a use a simple $query = "DROP TABLE " . $table;. It should be easy enough to check against a list of known tables to ensure you're not injecting anything harmful. Anything that makes DDL changes shouldn't be taking input from the user anyway, as far as I'm concerned. These sorts of changes can be based on user input, but the actual construction of the query should be really well known and shouldn't need outside data to construct.

Also, I'm not really sure what you're trying to do with this query:

DROP TABLE (SELECT MAX(name) from profiles where name='testing');

It looks like you might be trying to delete a record, but that's entirely the wrong syntax for that. If you're trying to drop a table whose name comes from the result of another query, I really don't think you can do that either. I'm 99% sure that DROP TABLE expects only a literal table name value.

Answer 3

Are you sure you want to drop tables dynamically?
It is extremely unusual.

It seems you have wrong database design.
And now you faced a consequence.

It seems you should have one table users and delete rows from it, not tables.