php - Can I parameterize the table name in a prepared statement?
Get the solution ↓↓↓
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();
}
Answer
Solution:
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, soSELECT * FROM ? withmytable as the param would actually end up sendingSELECT * 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.
Answer
Solution:
(Late answer, consult my side note).
The same rule applies when trying to create a "database".
You cannot use a prepared statement to bind a database.
I.e.:
CREATE DATABASE IF NOT EXISTS ?
will not work. Use a safelist instead.
Side note: I added this answer (as a community wiki) because it often used to close questions with, where some people posted questions similar to this in trying to bind a database and not a table and/or column.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: using $this when not in object context
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.