Can Prepared Statements Parameterize Table Names in SQL?

Can Parameterized Statements Handle Table Names?

You've encountered an issue when trying to parameterize the table name in a prepared statement. Separating variables intended for protection against SQL injection has resulted in errors.

The code you provided includes the function insertRow, which attempts to bind the new table name as a question mark statement.

function insertRow( $db, $mysqli, $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol )
{
    $statement = $mysqli->prepare("INSERT INTO (?) VALUES (?,?,?,?,?,?,?);");
    mysqli_stmt_bind_param( $statment, 'ssssisss', $new_table, $Partner, $Merchant, $ips, $score, $category, $overall, $protocol );
    $statement->execute();
}

Unfortunately, parameterizing the table name in a prepared statement is not possible. Prepared statements only allow for parameters to be bound to "value" elements of an SQL statement. Table names are not runtime values and alter the validity of the statement, potentially changing its meaning.

Even if a database interface like PDO allowed placeholder substitution anywhere, the value of the table placeholder would be a string enclosed within the SQL, resulting in invalid SQL.

To protect against SQL injection, it's essential to use a white-list of tables and check user input against this list. Your code should resemble the following:

if (in_array($mytable, $whitelist)) {
    $result = $db->query("SELECT * FROM {$mytable}");
}

The above is the detailed content of Can Prepared Statements Parameterize Table Names in SQL?. For more information, please follow other related articles on the PHP Chinese website!