Can I parameterize the table name in a prepared statement?

Question

I've used the mysqli_stmt_bind_param function several times. However, if I separate variables that I'm trying to protect against SQL injection I run into errors.

Here's some code sample:

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

Is it possible to somehow replace the .$new_table. concatenation with another question mark statement, make another bind parameter statement, or add onto the existing one to protect against SQL injection?

Like this or some form of this:

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();
}

No, a parameterised query doesn't just drop the parameter values in to the query string, it supplies the RDBMS with the parameterised query and the parameters separately. But such a query can't have a table name or field name as a parameter. The only way to do that is to dynamically code the table name into the query string, just as you have already done. If this string is potentially open to attack you should validate it first; such as against a white list list of allowable tables. — MatBailie
The use of mysqli extension is safe, go ahead! But do not forget to sanitize and validate all your strings.. if table or field name or whatever standing against your data base! — devasia2112
@user1475765 using escape functions on your table name does not protect you from anything, use a white-list. — jeroen
possible duplicate of Can PHP PDO Statements accept the table name as parameter? — outis

Sam Graham Sam Graham · Accepted Answer · 2012-07-04T11:05:06

Short answer to your question is "no".

In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.

One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.

At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ? with mytable as the param would actually end up sending SELECT * FROM 'mytable' to the database, which is invalid SQL.

Your best bet is just to continue with

SELECT * FROM {$mytable}

but you absolutely should have a white-list of tables that you check against first if that $mytable is coming from user input.

Can I parameterize the table name in a prepared statement?

2 Answers