I'm writing a function which will drop a table if it already exists. It will ask the user what they'd like to call the table, take that response and put it into a php variable. I want to make a customized drop statement then for sql so that there are no errors with sql. Here's what I have.
$table = $_POST["tablename"]; //gets table name from html page
drop_table($db, $table); //call function
function drop_table($db,$table){
$drop = "DROP TABLE IF EXISTS .$table. "; //this is the part I can't figure out. How do I add in the table name to the statement,
$q = mysqli_query($db, $drop); //since the sql statement has to be in quotes?
}
Thanks!
P.Ss This is an internal system for analyses only. No worries with dropping tables if just my colleagues and I are using it
Your problem here is a syntax error by attempting to concatenate in $table with dots. Remove those.
$drop = "DROP TABLE IF EXISTS $table ";
But the much much larger problem is that you are permitting end users to drop any table in your database, since you have not filtered the input in any way.
You need to be sure that your users are only dropping tables in the currently selected database, which means at the very least, not permitting . inside $table to prevent things like $table = 'information_schema.user'
if (strpos($table, '.') !== FALSE) {
// don't allow the action!
}
Another step to take would be to verify that the value of $table exists in information_schema.TABLES and belongs to the correct current database before executing the DROP statement.
// If this returns 1, the table exists in the correct database and can be dropped.
// note that $table is escaped here. I didn't fill in the mysqli_query() but obviously
// this is to be executed. It would be even better with a MySQLi prepared statement
"SELECT 1
FROM information_schema.TABLES
WHERE
TABLE_SCHEMA='the_allowed_database'
AND TABLE_NAME='" . mysqli_real_escape_string($db, $table) . "'"`
After passing this check, you would do well to specify a prefix to tables which are flexible in the environment and are therefore permissible to delete, so that a user could not delete every table in the active database. For example, only permit deletion of tables with the prefix usertable_.
if (strpos($table, 'usertable_') !== 0) {
// don't permit deletion
}
This is a very difficult design to secure, and I would recommend you step back and rethink the strategy here. You need to be extremely careful when allowing users to drop tables based on form input.
Do you mean:
$drop = "DROP TABLE IF EXISTS " . $table;
I really, really hope you've thought through the consequences of someone being able to drop tables from your database by entering the right name in the URL.