CODEWITHSUNDEEP
phpmysqlmysqli
binoculars
binoculars

Reputation: 2274

Dynamic table names and prepared statements using MySQLi

I have the need to make the table names dynamic in my queries. I've read that you can't bind them like you would with parameters, so I figured I'd do something like this:

$table_unsafe = $_GET['table'];
$table_safe = get_safe_table($mysqli, $table_unsafe);
$query = "SELECT * FROM " . $table_safe . " WHERE user=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('i', $user_id);

function get_safe_table($mysqli, $table)
{
    $query = "SELECT table_name FROM tables WHERE table_name=?";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $result = $stmt->get_result();
    $result = $result->fetch_row();
    if ($result) {
        return $result[0];
    } else {
        exit;
    }
}

The table tables is just a table that holds all the available table names:

+-----+------------------+
| id  |    table_name    |
+-----+------------------+
|   1 | user_statistics  |
|   2 | user_information |
| ... | ...              |
+-----+------------------+

Is this practice safe? If not, what can I do to make it more safe? The reason why I put the available tables in a table, is because of the amount of tables I have (+300). Each time a new table gets created, I simply add a row to tables instead of having to update my code all the time. There is no public code that can insert to or update tables.

Upvotes: 0

Views: 562

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 158005

Yes, it is possible but you don't have to create a table to hold the table names and maintain it manually - of course mysql already has such a table. So it could be just 

$sql = "SELECT 1 FROM information_schema.TABLES 
        WHERE TABLE_NAME = ? AND TABLE_SCHEMA IN (SELECT DATABASE())";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $table);
$stmt->execute();
return (bool)$stmt->get_result()->fetch_row();

However, the usage should be a bit different. It makes no sense to run a query like SELECT * FROM WHERE with just empty table name. You should only verify the function's result and throw an error in case it is empty.

Upvotes: 2

Related Questions